Table:
RELATEDTAGS

Columns:
KEYWORD RELATEDKEYWORD PRIORITY

Sample rows:
JAIPUR IPL 1
JAIPUR RAJASTHAN 2
JAIPUR CRICKET 3
JAIPUR PINK 4

IPL WARNE 1
IPL JAIPUR 2
IPL CRICKET 3
IPL SACHIN 4

RAJASTHAN IPL 1
RAJASTHAN CRICKET 2
RAJASTHAN JAIPUR 3
RAJASTHAN WARNE 4

CRICKET SACHIN 1
CRICKET WARNE 2
CRICKET RAJASTHAN 3
CRICKET SCORE 4

PINK JAIPUR 1
PINK CITY 2
PINK RAJASTHAN 3


Question:

Please see above table structure and sample data. We are trying to
determine all strongly related keywords for 'JAIPUR'. Strong related
keyword means:

1) Its one of the RELATEDKEYWORDs for 'JAIPUR' (Result: IPL,
RAJASTHAN, CRICKET, PINK)

AND

2) The relatedkeyword has 'JAIPUR' as one of its RELATEDKEYWORDs
(Result: IPL, RAJASTHAN, PINK. Note that CRICKET isn't related back to
'JAIPUR'.)

AND

3) In the backward relationship, the priority of 'JAIPUR' is greater
than the priority of non-related keywords for 'JAIPUR'. i.e., priority
of 'JAIPUR' is greater than those which are NOT listed in #1. (Result:
RAJASTHAN, PINK. IPL gives higher priority for WARNE, which isn't
related to JAIPUR in the first place).

I am an amateur in SQL and unable to write the SQL query for this. Can
someone please help me with writing a query for MYSQL DB?

Thanks

On May 17, 5:03 am, bang123 <vasuad...@gmail.com> wrote:
> Table:
> RELATEDTAGS
>
> Columns:
> KEYWORD RELATEDKEYWORD PRIORITY
>
> Sample rows:
> JAIPUR IPL 1
> JAIPUR RAJASTHAN 2
> JAIPUR CRICKET 3
> JAIPUR PINK 4
>
> IPL WARNE 1
> IPL JAIPUR 2
> IPL CRICKET 3
> IPL SACHIN 4
>
> RAJASTHAN IPL 1
> RAJASTHAN CRICKET 2
> RAJASTHAN JAIPUR 3
> RAJASTHAN WARNE 4
>
> CRICKET SACHIN 1
> CRICKET WARNE 2
> CRICKET RAJASTHAN 3
> CRICKET SCORE 4
>
> PINK JAIPUR 1
> PINK CITY 2
> PINK RAJASTHAN 3
>
> Question:
>
> Please see above table structure and sample data. We are trying to
> determine all strongly related keywords for 'JAIPUR'. Strong related
> keyword means:
>
> 1) Its one of the RELATEDKEYWORDs for 'JAIPUR' (Result: IPL,
> RAJASTHAN, CRICKET, PINK)
>

select KEYWORD, RELATEDKEYWORD, PRIORITY from RELATEDTAGS x where
KEYWORD = 'JAIPUR'


> AND
>
> 2) The relatedkeyword has 'JAIPUR' as one of its RELATEDKEYWORDs
> (Result: IPL, RAJASTHAN, PINK. Note that CRICKET isn't related back to
> 'JAIPUR'.)
>

and exists (select 1 from RELATEDTAGS y where x.RELATEDKEYWORD =
y.KEYWORD and x.KEYWORD = y.RELATEDKEYWORD)

> AND
>
> 3) In the backward relationship, the priority of 'JAIPUR' is greater
> than the priority of non-related keywords for 'JAIPUR'. i.e., priority
> of 'JAIPUR' is greater than those which are NOT listed in #1. (Result:
> RAJASTHAN, PINK. IPL gives higher priority for WARNE, which isn't
> related to JAIPUR in the first place).
>

I'm afraid I don't understand this step. Can you elaborate and perhaps
provide sample data that will hold for 1 and 2, but will be discarded
by 3?


/Lennart



> I am an amateur in SQL and unable to write the SQL query for this. Can
> someone please help me with writing a query for MYSQL DB?
>
> Thanks

On May 17, 9:33 am, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:
> On May 17, 5:03 am, bang123 <vasuad...@gmail.com> wrote:
>
>
>
> > Table:
> > RELATEDTAGS
>
> > Columns:
> > KEYWORD RELATEDKEYWORD PRIORITY
>
> > Sample rows:
> > JAIPUR IPL 1
> > JAIPUR RAJASTHAN 2
> > JAIPUR CRICKET 3
> > JAIPUR PINK 4
>
> > IPL WARNE 1
> > IPL JAIPUR 2
> > IPL CRICKET 3
> > IPL SACHIN 4
>
> > RAJASTHAN IPL 1
> > RAJASTHAN CRICKET 2
> > RAJASTHAN JAIPUR 3
> > RAJASTHAN WARNE 4
>
> > CRICKET SACHIN 1
> > CRICKET WARNE 2
> > CRICKET RAJASTHAN 3
> > CRICKET SCORE 4
>
> > PINK JAIPUR 1
> > PINK CITY 2
> > PINK RAJASTHAN 3
>
> > Question:
>
> > Please see above table structure and sample data. We are trying to
> > determine all strongly related keywords for 'JAIPUR'. Strong related
> > keyword means:
>
> > 1) Its one of the RELATEDKEYWORDs for 'JAIPUR' (Result: IPL,
> > RAJASTHAN, CRICKET, PINK)
>
> select KEYWORD, RELATEDKEYWORD, PRIORITY from RELATEDTAGS x where
> KEYWORD = 'JAIPUR'
>
> > AND
>
> > 2) The relatedkeyword has 'JAIPUR' as one of its RELATEDKEYWORDs
> > (Result: IPL, RAJASTHAN, PINK. Note that CRICKET isn't related back to
> > 'JAIPUR'.)
>
> and exists (select 1 from RELATEDTAGS y where x.RELATEDKEYWORD =
> y.KEYWORD and x.KEYWORD = y.RELATEDKEYWORD)
>
> > AND
>
> > 3) In the backward relationship, the priority of 'JAIPUR' is greater
> > than the priority of non-related keywords for 'JAIPUR'. i.e., priority
> > of 'JAIPUR' is greater than those which are NOT listed in #1. (Result:
> > RAJASTHAN, PINK. IPL gives higher priority for WARNE, which isn't
> > related to JAIPUR in the first place).
>
> I'm afraid I don't understand this step. Can you elaborate and perhaps
> provide sample data that will hold for 1 and 2, but will be discarded
> by 3?
>
> /Lennart
>
> > I am an amateur in SQL and unable to write the SQL query for this. Can
> > someone please help me with writing a query for MYSQL DB?
>
> > Thanks

In the sample data above, JAIPUR's related keywords are IPL,
RAJASTHAN, CRICKET & PINK.

IPL's related keywords are WARNE, JAIPUR, CRICKET & SACHIN. You can
see that priority 1 is WARNE, which has no relation to JAIPUR. That
makes JAIPUR weakly related to IPL. Discard IPL.

RAJASTHAN's related keywords are IPL, CRICKET, JAIPUR & WARNE. You can
see that IPL & CRICKET have higher priority than JAIPUR, but these are
directly related to JAIPUR as well. This makes JAIPUR strongly related
to RAJASTHAN even though its not highest priority in the backward
relationship. select RAJASTHAN.

Hope that clarifies.

Thanks

On May 17, 5:57 am, bang123 <vasuad...@gmail.com> wrote:
> On May 17, 9:33 am, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:
>
>
>
> > On May 17, 5:03 am, bang123 <vasuad...@gmail.com> wrote:
>
> > > Table:
> > > RELATEDTAGS
>
> > > Columns:
> > > KEYWORD RELATEDKEYWORD PRIORITY
>
> > > Sample rows:
> > > JAIPUR IPL 1
> > > JAIPUR RAJASTHAN 2
> > > JAIPUR CRICKET 3
> > > JAIPUR PINK 4
>
> > > IPL WARNE 1
> > > IPL JAIPUR 2
> > > IPL CRICKET 3
> > > IPL SACHIN 4
>
> > > RAJASTHAN IPL 1
> > > RAJASTHAN CRICKET 2
> > > RAJASTHAN JAIPUR 3
> > > RAJASTHAN WARNE 4
>
> > > CRICKET SACHIN 1
> > > CRICKET WARNE 2
> > > CRICKET RAJASTHAN 3
> > > CRICKET SCORE 4
>
> > > PINK JAIPUR 1
> > > PINK CITY 2
> > > PINK RAJASTHAN 3
>
> > > Question:
>
> > > Please see above table structure and sample data. We are trying to
> > > determine all strongly related keywords for 'JAIPUR'. Strong related
> > > keyword means:
>
> > > 1) Its one of the RELATEDKEYWORDs for 'JAIPUR' (Result: IPL,
> > > RAJASTHAN, CRICKET, PINK)
>
> > select KEYWORD, RELATEDKEYWORD, PRIORITY from RELATEDTAGS x where
> > KEYWORD = 'JAIPUR'
>
> > > AND
>
> > > 2) The relatedkeyword has 'JAIPUR' as one of its RELATEDKEYWORDs
> > > (Result: IPL, RAJASTHAN, PINK. Note that CRICKET isn't related back to
> > > 'JAIPUR'.)
>
> > and exists (select 1 from RELATEDTAGS y where x.RELATEDKEYWORD =
> > y.KEYWORD and x.KEYWORD = y.RELATEDKEYWORD)
>
> > > AND
>
> > > 3) In the backward relationship, the priority of 'JAIPUR' is greater
> > > than the priority of non-related keywords for 'JAIPUR'. i.e., priority
> > > of 'JAIPUR' is greater than those which are NOT listed in #1. (Result:
> > > RAJASTHAN, PINK. IPL gives higher priority for WARNE, which isn't
> > > related to JAIPUR in the first place).
>
> > I'm afraid I don't understand this step. Can you elaborate and perhaps
> > provide sample data that will hold for 1 and 2, but will be discarded
> > by 3?
>
> > /Lennart
>
> > > I am an amateur in SQL and unable to write the SQL query for this. Can
> > > someone please help me with writing a query for MYSQL DB?
>
> > > Thanks
>
> In the sample data above, JAIPUR's related keywords are IPL,
> RAJASTHAN, CRICKET & PINK.
>
> IPL's related keywords are WARNE, JAIPUR, CRICKET & SACHIN. You can
> see that priority 1 is WARNE, which has no relation to JAIPUR. That
> makes JAIPUR weakly related to IPL. Discard IPL.
>
> RAJASTHAN's related keywords are IPL, CRICKET, JAIPUR & WARNE. You can
> see that IPL & CRICKET have higher priority than JAIPUR, but these are
> directly related to JAIPUR as well. This makes JAIPUR strongly related
> to RAJASTHAN even though its not highest priority in the backward
> relationship. select RAJASTHAN.
>
> Hope that clarifies.
>
> Thanks

It doesn't - but I think I get your drift.

From the set of keywords related to Jaipur, you'd initially expect the
following result:

IPL
RAJASTHAN
CRICKET
PINK

However, because IPL and CRICKET have words related to them (WARNE and
SACHIN, respectively) which ARE NOT directly related to JAIPUR but
have a stronger relationship (to WARNE and SACHIN respectively) than
JAIPUR , you'd therefore expect them to be omitted from the results.
Hmm, now to express all that in SQL syntax...

On May 17, 5:36 pm, strawberry <zac.ca...@gmail.com> wrote:
> On May 17, 5:57 am, bang123 <vasuad...@gmail.com> wrote:
>
>
>
> > On May 17, 9:33 am, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:
>
> > > On May 17, 5:03 am, bang123 <vasuad...@gmail.com> wrote:
>
> > > > Table:
> > > > RELATEDTAGS
>
> > > > Columns:
> > > > KEYWORD RELATEDKEYWORD PRIORITY
>
> > > > Sample rows:
> > > > JAIPUR IPL 1
> > > > JAIPUR RAJASTHAN 2
> > > > JAIPUR CRICKET 3
> > > > JAIPUR PINK 4
>
> > > > IPL WARNE 1
> > > > IPL JAIPUR 2
> > > > IPL CRICKET 3
> > > > IPL SACHIN 4
>
> > > > RAJASTHAN IPL 1
> > > > RAJASTHAN CRICKET 2
> > > > RAJASTHAN JAIPUR 3
> > > > RAJASTHAN WARNE 4
>
> > > > CRICKET SACHIN 1
> > > > CRICKET WARNE 2
> > > > CRICKET RAJASTHAN 3
> > > > CRICKET SCORE 4
>
> > > > PINK JAIPUR 1
> > > > PINK CITY 2
> > > > PINK RAJASTHAN 3
>
> > > > Question:
>
> > > > Please see above table structure and sample data. We are trying to
> > > > determine all strongly related keywords for 'JAIPUR'. Strong related
> > > > keyword means:
>
> > > > 1) Its one of the RELATEDKEYWORDs for 'JAIPUR' (Result: IPL,
> > > > RAJASTHAN, CRICKET, PINK)
>
> > > select KEYWORD, RELATEDKEYWORD, PRIORITY from RELATEDTAGS x where
> > > KEYWORD = 'JAIPUR'
>
> > > > AND
>
> > > > 2) The relatedkeyword has 'JAIPUR' as one of its RELATEDKEYWORDs
> > > > (Result: IPL, RAJASTHAN, PINK. Note that CRICKET isn't related back to
> > > > 'JAIPUR'.)
>
> > > and exists (select 1 from RELATEDTAGS y where x.RELATEDKEYWORD =
> > > y.KEYWORD and x.KEYWORD = y.RELATEDKEYWORD)
>
> > > > AND
>
> > > > 3) In the backward relationship, the priority of 'JAIPUR' is greater
> > > > than the priority of non-related keywords for 'JAIPUR'. i.e., priority
> > > > of 'JAIPUR' is greater than those which are NOT listed in #1. (Result:
> > > > RAJASTHAN, PINK. IPL gives higher priority for WARNE, which isn't
> > > > related to JAIPUR in the first place).
>
> > > I'm afraid I don't understand this step. Can you elaborate and perhaps
> > > provide sample data that will hold for 1 and 2, but will be discarded
> > > by 3?
>
> > > /Lennart
>
> > > > I am an amateur in SQL and unable to write the SQL query for this. Can
> > > > someone please help me with writing a query for MYSQL DB?
>
> > > > Thanks
>
> > In the sample data above, JAIPUR's related keywords are IPL,
> > RAJASTHAN, CRICKET & PINK.
>
> > IPL's related keywords are WARNE, JAIPUR, CRICKET & SACHIN. You can
> > see that priority 1 is WARNE, which has no relation to JAIPUR. That
> > makes JAIPUR weakly related to IPL. Discard IPL.
>
> > RAJASTHAN's related keywords are IPL, CRICKET, JAIPUR & WARNE. You can
> > see that IPL & CRICKET have higher priority than JAIPUR, but these are
> > directly related to JAIPUR as well. This makes JAIPUR strongly related
> > to RAJASTHAN even though its not highest priority in the backward
> > relationship. select RAJASTHAN.
>
> > Hope that clarifies.
>
> > Thanks
>
> It doesn't - but I think I get your drift.
>
> From the set of keywords related to Jaipur, you'd initially expect the
> following result:
>
> IPL
> RAJASTHAN
> CRICKET
> PINK
>
> However, because IPL and CRICKET have words related to them (WARNE and
> SACHIN, respectively) which ARE NOT directly related to JAIPUR but
> have a stronger relationship (to WARNE and SACHIN respectively) than
> JAIPUR , you'd therefore expect them to be omitted from the results.
> Hmm, now to express all that in SQL syntax...

You almost got it :-) CRICKET should be omitted because it doesn't
relate back to JAIPUR. IPL should be omitted because it has a non-
related keyword (WARNE is not related to JAIPUR) with higher priority
(1 being highest, 4 being lowest) than JAIPUR.

On May 17, 6:57 am, bang123 <vasuad...@gmail.com> wrote:
> On May 17, 9:33 am, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:
>
>
>
> > On May 17, 5:03 am, bang123 <vasuad...@gmail.com> wrote:
>
> > > Table:
> > > RELATEDTAGS
>
> > > Columns:
> > > KEYWORD RELATEDKEYWORD PRIORITY
>
> > > Sample rows:
> > > JAIPUR IPL 1
> > > JAIPUR RAJASTHAN 2
> > > JAIPUR CRICKET 3
> > > JAIPUR PINK 4
>
> > > IPL WARNE 1
> > > IPL JAIPUR 2
> > > IPL CRICKET 3
> > > IPL SACHIN 4
>
> > > RAJASTHAN IPL 1
> > > RAJASTHAN CRICKET 2
> > > RAJASTHAN JAIPUR 3
> > > RAJASTHAN WARNE 4
>
> > > CRICKET SACHIN 1
> > > CRICKET WARNE 2
> > > CRICKET RAJASTHAN 3
> > > CRICKET SCORE 4
>
> > > PINK JAIPUR 1
> > > PINK CITY 2
> > > PINK RAJASTHAN 3
>
> > > Question:
>
> > > Please see above table structure and sample data. We are trying to
> > > determine all strongly related keywords for 'JAIPUR'. Strong related
> > > keyword means:
>
> > > 1) Its one of the RELATEDKEYWORDs for 'JAIPUR' (Result: IPL,
> > > RAJASTHAN, CRICKET, PINK)
>
> > select KEYWORD, RELATEDKEYWORD, PRIORITY from RELATEDTAGS x where
> > KEYWORD = 'JAIPUR'
>
> > > AND
>
> > > 2) The relatedkeyword has 'JAIPUR' as one of its RELATEDKEYWORDs
> > > (Result: IPL, RAJASTHAN, PINK. Note that CRICKET isn't related back to
> > > 'JAIPUR'.)
>
> > and exists (select 1 from RELATEDTAGS y where x.RELATEDKEYWORD =
> > y.KEYWORD and x.KEYWORD = y.RELATEDKEYWORD)
>
> > > AND
>
> > > 3) In the backward relationship, the priority of 'JAIPUR' is greater
> > > than the priority of non-related keywords for 'JAIPUR'. i.e., priority
> > > of 'JAIPUR' is greater than those which are NOT listed in #1. (Result:
> > > RAJASTHAN, PINK. IPL gives higher priority for WARNE, which isn't
> > > related to JAIPUR in the first place).
>
> > I'm afraid I don't understand this step. Can you elaborate and perhaps
> > provide sample data that will hold for 1 and 2, but will be discarded
> > by 3?
>
> > /Lennart
>
> > > I am an amateur in SQL and unable to write the SQL query for this. Can
> > > someone please help me with writing a query for MYSQL DB?
>
> > > Thanks
>
> In the sample data above, JAIPUR's related keywords are IPL,
> RAJASTHAN, CRICKET & PINK.
>
> IPL's related keywords are WARNE, JAIPUR, CRICKET & SACHIN. You can
> see that priority 1 is WARNE, which has no relation to JAIPUR. That
> makes JAIPUR weakly related to IPL. Discard IPL.
>
> RAJASTHAN's related keywords are IPL, CRICKET, JAIPUR & WARNE. You can
> see that IPL & CRICKET have higher priority than JAIPUR, but these are
> directly related to JAIPUR as well. This makes JAIPUR strongly related
> to RAJASTHAN even though its not highest priority in the backward
> relationship. select RAJASTHAN.
>
> Hope that clarifies.
>
> Thanks

Ok, let's see if I get this. Condition 1 and 2 gives us the suspects:

select y.relatedKEYWORD, y.KEYWORD, y.priority from RELATEDTAGS x
inner join RELATEDTAGS y on x.relatedkeyword = y.keyword and x.keyword
= y.relatedkeyword where x.KEYWORD = 'JAIPUR'"

RELATEDKEYWORD KEYWORD PRIORITY
-------------- ---------- --------
JAIPUR IPL 2
JAIPUR RAJASTHAN 3
JAIPUR PINK 1

3 record(s) selected.

correct so far?

From this you want to exclude:

JAIPUR IPL 2

because there *exists* a row in:

select keyword, relatedkeyword, priority from RELATEDTAGS where
KEYWORD = 'IPL'

IPL CRICKET 3
IPL JAIPUR 2
IPL SACHIN 4
IPL WARNE 1

that has priority < min(priority) from the first set (sort of):


I think this will leave us with:

select x.relatedKEYWORD
from RELATEDTAGS x
inner join RELATEDTAGS y
on x.relatedKEYWORD = y.KEYWORD
and y.relatedKEYWORD = x.KEYWORD
where x.KEYWORD = 'JAIPUR'
and not exists (
select 1 from RELATEDTAGS z
where z.KEYWORD = x.relatedKEYWORD
and priority < (
select min(u.priority)
from RELATEDTAGS u
inner join RELATEDTAGS v
on u.relatedKEYWORD = v.KEYWORD
and u.relatedKEYWORD = v.KEYWORD
where u.KEYWORD = z.KEYWORD
)
);

I'm still not sure wether this is what you are aiming at, but
hopefully it will give you an idea

/Lennart

On May 17, 8:10 pm, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:
> On May 17, 6:57 am, bang123 <vasuad...@gmail.com> wrote:
>
>
>
> > On May 17, 9:33 am, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:
>
> > > On May 17, 5:03 am, bang123 <vasuad...@gmail.com> wrote:
>
> > > > Table:
> > > > RELATEDTAGS
>
> > > > Columns:
> > > > KEYWORD RELATEDKEYWORD PRIORITY
>
> > > > Sample rows:
> > > > JAIPUR IPL 1
> > > > JAIPUR RAJASTHAN 2
> > > > JAIPUR CRICKET 3
> > > > JAIPUR PINK 4
>
> > > > IPL WARNE 1
> > > > IPL JAIPUR 2
> > > > IPL CRICKET 3
> > > > IPL SACHIN 4
>
> > > > RAJASTHAN IPL 1
> > > > RAJASTHAN CRICKET 2
> > > > RAJASTHAN JAIPUR 3
> > > > RAJASTHAN WARNE 4
>
> > > > CRICKET SACHIN 1
> > > > CRICKET WARNE 2
> > > > CRICKET RAJASTHAN 3
> > > > CRICKET SCORE 4
>
> > > > PINK JAIPUR 1
> > > > PINK CITY 2
> > > > PINK RAJASTHAN 3
>
> > > > Question:
>
> > > > Please see above table structure and sample data. We are trying to
> > > > determine all strongly related keywords for 'JAIPUR'. Strong related
> > > > keyword means:
>
> > > > 1) Its one of the RELATEDKEYWORDs for 'JAIPUR' (Result: IPL,
> > > > RAJASTHAN, CRICKET, PINK)
>
> > > select KEYWORD, RELATEDKEYWORD, PRIORITY from RELATEDTAGS x where
> > > KEYWORD = 'JAIPUR'
>
> > > > AND
>
> > > > 2) The relatedkeyword has 'JAIPUR' as one of its RELATEDKEYWORDs
> > > > (Result: IPL, RAJASTHAN, PINK. Note that CRICKET isn't related back to
> > > > 'JAIPUR'.)
>
> > > and exists (select 1 from RELATEDTAGS y where x.RELATEDKEYWORD =
> > > y.KEYWORD and x.KEYWORD = y.RELATEDKEYWORD)
>
> > > > AND
>
> > > > 3) In the backward relationship, the priority of 'JAIPUR' is greater
> > > > than the priority of non-related keywords for 'JAIPUR'. i.e., priority
> > > > of 'JAIPUR' is greater than those which are NOT listed in #1. (Result:
> > > > RAJASTHAN, PINK. IPL gives higher priority for WARNE, which isn't
> > > > related to JAIPUR in the first place).
>
> > > I'm afraid I don't understand this step. Can you elaborate and perhaps
> > > provide sample data that will hold for 1 and 2, but will be discarded
> > > by 3?
>
> > > /Lennart
>
> > > > I am an amateur in SQL and unable to write the SQL query for this. Can
> > > > someone please help me with writing a query for MYSQL DB?
>
> > > > Thanks
>
> > In the sample data above, JAIPUR's related keywords are IPL,
> > RAJASTHAN, CRICKET & PINK.
>
> > IPL's related keywords are WARNE, JAIPUR, CRICKET & SACHIN. You can
> > see that priority 1 is WARNE, which has no relation to JAIPUR. That
> > makes JAIPUR weakly related to IPL. Discard IPL.
>
> > RAJASTHAN's related keywords are IPL, CRICKET, JAIPUR & WARNE. You can
> > see that IPL & CRICKET have higher priority than JAIPUR, but these are
> > directly related to JAIPUR as well. This makes JAIPUR strongly related
> > to RAJASTHAN even though its not highest priority in the backward
> > relationship. select RAJASTHAN.
>
> > Hope that clarifies.
>
> > Thanks
>
> Ok, let's see if I get this. Condition 1 and 2 gives us the suspects:
>
> select y.relatedKEYWORD, y.KEYWORD, y.priority from RELATEDTAGS x
> inner join RELATEDTAGS y on x.relatedkeyword = y.keyword and x.keyword
> = y.relatedkeyword where x.KEYWORD = 'JAIPUR'"
>
> RELATEDKEYWORD KEYWORD PRIORITY
> -------------- ---------- --------
> JAIPUR IPL 2
> JAIPUR RAJASTHAN 3
> JAIPUR PINK 1
>
> 3 record(s) selected.
>
> correct so far?
>
> From this you want to exclude:
>
> JAIPUR IPL 2
>
> because there *exists* a row in:
>
> select keyword, relatedkeyword, priority from RELATEDTAGS where
> KEYWORD = 'IPL'
>
> IPL CRICKET 3
> IPL JAIPUR 2
> IPL SACHIN 4
> IPL WARNE 1
>
> that has priority < min(priority) from the first set (sort of):
>
> I think this will leave us with:
>
> select x.relatedKEYWORD
> from RELATEDTAGS x
> inner join RELATEDTAGS y
> on x.relatedKEYWORD = y.KEYWORD
> and y.relatedKEYWORD = x.KEYWORD
> where x.KEYWORD = 'JAIPUR'
> and not exists (
> select 1 from RELATEDTAGS z
> where z.KEYWORD = x.relatedKEYWORD
> and priority < (
> select min(u.priority)
> from RELATEDTAGS u
> inner join RELATEDTAGS v
> on u.relatedKEYWORD = v.KEYWORD
> and u.relatedKEYWORD = v.KEYWORD
> where u.KEYWORD = z.KEYWORD
> )
> );
>
> I'm still not sure wether this is what you are aiming at, but
> hopefully it will give you an idea
>
> /Lennart

That sure is ugly - but it might just be right!

On May 18, 12:10 am, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:
> On May 17, 6:57 am, bang123 <vasuad...@gmail.com> wrote:
>
>
>
> > On May 17, 9:33 am, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:
>
> > > On May 17, 5:03 am, bang123 <vasuad...@gmail.com> wrote:
>
> > > > Table:
> > > > RELATEDTAGS
>
> > > > Columns:
> > > > KEYWORD RELATEDKEYWORD PRIORITY
>
> > > > Sample rows:
> > > > JAIPUR IPL 1
> > > > JAIPUR RAJASTHAN 2
> > > > JAIPUR CRICKET 3
> > > > JAIPUR PINK 4
>
> > > > IPL WARNE 1
> > > > IPL JAIPUR 2
> > > > IPL CRICKET 3
> > > > IPL SACHIN 4
>
> > > > RAJASTHAN IPL 1
> > > > RAJASTHAN CRICKET 2
> > > > RAJASTHAN JAIPUR 3
> > > > RAJASTHAN WARNE 4
>
> > > > CRICKET SACHIN 1
> > > > CRICKET WARNE 2
> > > > CRICKET RAJASTHAN 3
> > > > CRICKET SCORE 4
>
> > > > PINK JAIPUR 1
> > > > PINK CITY 2
> > > > PINK RAJASTHAN 3
>
> > > > Question:
>
> > > > Please see above table structure and sample data. We are trying to
> > > > determine all strongly related keywords for 'JAIPUR'. Strong related
> > > > keyword means:
>
> > > > 1) Its one of the RELATEDKEYWORDs for 'JAIPUR' (Result: IPL,
> > > > RAJASTHAN, CRICKET, PINK)
>
> > > select KEYWORD, RELATEDKEYWORD, PRIORITY from RELATEDTAGS x where
> > > KEYWORD = 'JAIPUR'
>
> > > > AND
>
> > > > 2) The relatedkeyword has 'JAIPUR' as one of its RELATEDKEYWORDs
> > > > (Result: IPL, RAJASTHAN, PINK. Note that CRICKET isn't related back to
> > > > 'JAIPUR'.)
>
> > > and exists (select 1 from RELATEDTAGS y where x.RELATEDKEYWORD =
> > > y.KEYWORD and x.KEYWORD = y.RELATEDKEYWORD)
>
> > > > AND
>
> > > > 3) In the backward relationship, the priority of 'JAIPUR' is greater
> > > > than the priority of non-related keywords for 'JAIPUR'. i.e., priority
> > > > of 'JAIPUR' is greater than those which are NOT listed in #1. (Result:
> > > > RAJASTHAN, PINK. IPL gives higher priority for WARNE, which isn't
> > > > related to JAIPUR in the first place).
>
> > > I'm afraid I don't understand this step. Can you elaborate and perhaps
> > > provide sample data that will hold for 1 and 2, but will be discarded
> > > by 3?
>
> > > /Lennart
>
> > > > I am an amateur in SQL and unable to write the SQL query for this. Can
> > > > someone please help me with writing a query for MYSQL DB?
>
> > > > Thanks
>
> > In the sample data above, JAIPUR's related keywords are IPL,
> > RAJASTHAN, CRICKET & PINK.
>
> > IPL's related keywords are WARNE, JAIPUR, CRICKET & SACHIN. You can
> > see that priority 1 is WARNE, which has no relation to JAIPUR. That
> > makes JAIPUR weakly related to IPL. Discard IPL.
>
> > RAJASTHAN's related keywords are IPL, CRICKET, JAIPUR & WARNE. You can
> > see that IPL & CRICKET have higher priority than JAIPUR, but these are
> > directly related to JAIPUR as well. This makes JAIPUR strongly related
> > to RAJASTHAN even though its not highest priority in the backward
> > relationship. select RAJASTHAN.
>
> > Hope that clarifies.
>
> > Thanks
>
> Ok, let's see if I get this. Condition 1 and 2 gives us the suspects:
>
> select y.relatedKEYWORD, y.KEYWORD, y.priority from RELATEDTAGS x
> inner join RELATEDTAGS y on x.relatedkeyword = y.keyword and x.keyword
> = y.relatedkeyword where x.KEYWORD = 'JAIPUR'"
>
> RELATEDKEYWORD KEYWORD PRIORITY
> -------------- ---------- --------
> JAIPUR IPL 2
> JAIPUR RAJASTHAN 3
> JAIPUR PINK 1
>
> 3 record(s) selected.
>
> correct so far?
>
> From this you want to exclude:
>
> JAIPUR IPL 2
>
> because there *exists* a row in:
>
> select keyword, relatedkeyword, priority from RELATEDTAGS where
> KEYWORD = 'IPL'
>
> IPL CRICKET 3
> IPL JAIPUR 2
> IPL SACHIN 4
> IPL WARNE 1
>
> that has priority < min(priority) from the first set (sort of):
>
> I think this will leave us with:
>
> select x.relatedKEYWORD
> from RELATEDTAGS x
> inner join RELATEDTAGS y
> on x.relatedKEYWORD = y.KEYWORD
> and y.relatedKEYWORD = x.KEYWORD
> where x.KEYWORD = 'JAIPUR'
> and not exists (
> select 1 from RELATEDTAGS z
> where z.KEYWORD = x.relatedKEYWORD
> and priority < (
> select min(u.priority)
> from RELATEDTAGS u
> inner join RELATEDTAGS v
> on u.relatedKEYWORD = v.KEYWORD
> and u.relatedKEYWORD = v.KEYWORD
> where u.KEYWORD = z.KEYWORD
> )
> );
>
> I'm still not sure wether this is what you are aiming at, but
> hopefully it will give you an idea
>
> /Lennart

Thanks for all your effort...you have understood the condition 1 & 2,
but not 3. Let me explain condition #3 with another example:

Table FRIENDS

COMPANY FRIENDS PRIORITY
-----------------------------------------------------
MICROSOFT YAHOO 1
MICROSOFT TWITTER 2
MICROSOFT FLICKR 3
MICROSOFT MYSPACE 4

YAHOO GOOGLE 1
YAHOO MICROSOFT 2
YAHOO FLICKR 3
YAHOO YOUTUBE 4

TWITTER YAHOO 1
TWITTER FLICKR 2
TWITTER MICROSOFT 3
TWITTER GOOGLE 4

As you can see above, MICROSOFT has YAHOO, TWITTER, FLICKR & MYSPACE
as friends. We will try to find best friends of MICROSOFT.

YAHOO gives higher priority to GOOGLE over MICROSOFT among its
friends. Since GOOGLE isn't one of the friends of MICROSOFT, YAHOO
isn't the best friend of MICROSOFT.

TWITTER gives YAHOO & FLICKR higher priority than MICROSOFT, but
that's okay because they are friends of MICROSOFT too. Since TWITTER
gives MICROSOFT higher priority than all non-friends (ex: GOOGLE),
TWITTER is one of the best friends of MICROSOFT.

You may have to use 'group by' to address Condition #3. I have lost
out on writing SQL for this condition...

On May 18, 2:53 am, bang123 <vasuad...@gmail.com> wrote:
> On May 18, 12:10 am, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:
>
>
>
> > On May 17, 6:57 am, bang123 <vasuad...@gmail.com> wrote:
>
> > > On May 17, 9:33 am, Lennart <Erik.Lennart.Jons...@gmail.com> wrote:
>
> > > > On May 17, 5:03 am, bang123 <vasuad...@gmail.com> wrote:
>
> > > > > Table:
> > > > > RELATEDTAGS
>
> > > > > Columns:
> > > > > KEYWORD RELATEDKEYWORD PRIORITY
>
> > > > > Sample rows:
> > > > > JAIPUR IPL 1
> > > > > JAIPUR RAJASTHAN 2
> > > > > JAIPUR CRICKET 3
> > > > > JAIPUR PINK 4
>
> > > > > IPL WARNE 1
> > > > > IPL JAIPUR 2
> > > > > IPL CRICKET 3
> > > > > IPL SACHIN 4
>
> > > > > RAJASTHAN IPL 1
> > > > > RAJASTHAN CRICKET 2
> > > > > RAJASTHAN JAIPUR 3
> > > > > RAJASTHAN WARNE 4
>
> > > > > CRICKET SACHIN 1
> > > > > CRICKET WARNE 2
> > > > > CRICKET RAJASTHAN 3
> > > > > CRICKET SCORE 4
>
> > > > > PINK JAIPUR 1
> > > > > PINK CITY 2
> > > > > PINK RAJASTHAN 3
>
> > > > > Question:
>
> > > > > Please see above table structure and sample data. We are trying to
> > > > > determine all strongly related keywords for 'JAIPUR'. Strong related
> > > > > keyword means:
>
> > > > > 1) Its one of the RELATEDKEYWORDs for 'JAIPUR' (Result: IPL,
> > > > > RAJASTHAN, CRICKET, PINK)
>
> > > > select KEYWORD, RELATEDKEYWORD, PRIORITY from RELATEDTAGS x where
> > > > KEYWORD = 'JAIPUR'
>
> > > > > AND
>
> > > > > 2) The relatedkeyword has 'JAIPUR' as one of its RELATEDKEYWORDs
> > > > > (Result: IPL, RAJASTHAN, PINK. Note that CRICKET isn't related back to
> > > > > 'JAIPUR'.)
>
> > > > and exists (select 1 from RELATEDTAGS y where x.RELATEDKEYWORD =
> > > > y.KEYWORD and x.KEYWORD = y.RELATEDKEYWORD)
>
> > > > > AND
>
> > > > > 3) In the backward relationship, the priority of 'JAIPUR' is greater
> > > > > than the priority of non-related keywords for 'JAIPUR'. i.e., priority
> > > > > of 'JAIPUR' is greater than those which are NOT listed in #1. (Result:
> > > > > RAJASTHAN, PINK. IPL gives higher priority for WARNE, which isn't
> > > > > related to JAIPUR in the first place).
>
> > > > I'm afraid I don't understand this step. Can you elaborate and perhaps
> > > > provide sample data that will hold for 1 and 2, but will be discarded
> > > > by 3?
>
> > > > /Lennart
>
> > > > > I am an amateur in SQL and unable to write the SQL query for this. Can
> > > > > someone please help me with writing a query for MYSQL DB?
>
> > > > > Thanks
>
> > > In the sample data above, JAIPUR's related keywords are IPL,
> > > RAJASTHAN, CRICKET & PINK.
>
> > > IPL's related keywords are WARNE, JAIPUR, CRICKET & SACHIN. You can
> > > see that priority 1 is WARNE, which has no relation to JAIPUR. That
> > > makes JAIPUR weakly related to IPL. Discard IPL.
>
> > > RAJASTHAN's related keywords are IPL, CRICKET, JAIPUR & WARNE. You can
> > > see that IPL & CRICKET have higher priority than JAIPUR, but these are
> > > directly related to JAIPUR as well. This makes JAIPUR strongly related
> > > to RAJASTHAN even though its not highest priority in the backward
> > > relationship. select RAJASTHAN.
>
> > > Hope that clarifies.
>
> > > Thanks
>
> > Ok, let's see if I get this. Condition 1 and 2 gives us the suspects:
>
> > select y.relatedKEYWORD, y.KEYWORD, y.priority from RELATEDTAGS x
> > inner join RELATEDTAGS y on x.relatedkeyword = y.keyword and x.keyword
> > = y.relatedkeyword where x.KEYWORD = 'JAIPUR'"
>
> > RELATEDKEYWORD KEYWORD PRIORITY
> > -------------- ---------- --------
> > JAIPUR IPL 2
> > JAIPUR RAJASTHAN 3
> > JAIPUR PINK 1
>
> > 3 record(s) selected.
>
> > correct so far?
>
> > From this you want to exclude:
>
> > JAIPUR IPL 2
>
> > because there *exists* a row in:
>
> > select keyword, relatedkeyword, priority from RELATEDTAGS where
> > KEYWORD = 'IPL'
>
> > IPL CRICKET 3
> > IPL JAIPUR 2
> > IPL SACHIN 4
> > IPL WARNE 1
>
> > that has priority < min(priority) from the first set (sort of):
>
> > I think this will leave us with:
>
> > select x.relatedKEYWORD
> > from RELATEDTAGS x
> > inner join RELATEDTAGS y
> > on x.relatedKEYWORD = y.KEYWORD
> > and y.relatedKEYWORD = x.KEYWORD
> > where x.KEYWORD = 'JAIPUR'
> > and not exists (
> > select 1 from RELATEDTAGS z
> > where z.KEYWORD = x.relatedKEYWORD
> > and priority < (
> > select min(u.priority)
> > from RELATEDTAGS u
> > inner join RELATEDTAGS v
> > on u.relatedKEYWORD = v.KEYWORD
> > and u.relatedKEYWORD = v.KEYWORD
> > where u.KEYWORD = z.KEYWORD
> > )
> > );
>
> > I'm still not sure wether this is what you are aiming at, but
> > hopefully it will give you an idea
>
> > /Lennart
>
> Thanks for all your effort...you have understood the condition 1 & 2,
> but not 3. Let me explain condition #3 with another example:
>
> Table FRIENDS
>
> COMPANY FRIENDS PRIORITY
> -----------------------------------------------------
> MICROSOFT YAHOO 1
> MICROSOFT TWITTER 2
> MICROSOFT FLICKR 3
> MICROSOFT MYSPACE 4
>
> YAHOO GOOGLE 1
> YAHOO MICROSOFT 2
> YAHOO FLICKR 3
> YAHOO YOUTUBE 4
>
> TWITTER YAHOO 1
> TWITTER FLICKR 2
> TWITTER MICROSOFT 3
> TWITTER GOOGLE 4
>
> As you can see above, MICROSOFT has YAHOO, TWITTER, FLICKR & MYSPACE
> as friends. We will try to find best friends of MICROSOFT.
>
> YAHOO gives higher priority to GOOGLE over MICROSOFT among its
> friends. Since GOOGLE isn't one of the friends of MICROSOFT, YAHOO
> isn't the best friend of MICROSOFT.
>
> TWITTER gives YAHOO & FLICKR higher priority than MICROSOFT, but
> that's okay because they are friends of MICROSOFT too. Since TWITTER
> gives MICROSOFT higher priority than all non-friends (ex: GOOGLE),
> TWITTER is one of the best friends of MICROSOFT.
>
> You may have to use 'group by' to address Condition #3. I have lost
> out on writing SQL for this condition...

My query return:

KEYWORD RELATEDKEYWORD
---------- --------------
MICROSOFT TWITTER

and reading your last example, I still get the impression that my
query implements your thoughts. Can you provide an counterexample
where my query fails, and the reason for it's failure?

/Lennart

Add second sample data to the first sample.
CREATE Table RelatedTags
(Keyword VARCHAR(15) NOT NULL
,RelatedKeyword VARCHAR(15) NOT NULL
,Priority INTEGER NOT NULL
)

INSERT INTO RelatedTags
VALUES
('JAIPUR', 'IPL', 1 )
,('JAIPUR', 'RAJASTHAN', 2 )
,('JAIPUR', 'CRICKET', 3 )
,('JAIPUR', 'PINK', 4 )
,('IPL', 'WARNE', 1 )
,('IPL', 'JAIPUR', 2 )
,('IPL', 'CRICKET', 3 )
,('IPL', 'SACHIN', 4 )
,('RAJASTHAN', 'IPL', 1 )
,('RAJASTHAN', 'CRICKET', 2 )
,('RAJASTHAN', 'JAIPUR', 3 )
,('RAJASTHAN', 'WARNE', 4 )
,('CRICKET', 'SACHIN', 1 )
,('CRICKET', 'WARNE', 2 )
,('CRICKET', 'RAJASTHAN', 3 )
,('CRICKET', 'SCORE', 4 )
,('PINK', 'JAIPUR', 1 )
,('PINK', 'CITY', 2 )
,('PINK', 'RAJASTHAN', 3 )

-- Second sample data --
,('MICROSOFT', 'YAHOO', 1 )
,('MICROSOFT', 'TWITTER', 2 )
,('MICROSOFT', 'FLICKR', 3 )
,('MICROSOFT', 'MYSPACE', 4 )
,('YAHOO', 'GOOGLE', 1 )
,('YAHOO', 'MICROSOFT', 2 )
,('YAHOO', 'FLICKR', 3 )
,('YAHOO', 'YOUTUBE', 4 )
,('TWITTER', 'YAHOO', 1 )
,('TWITTER', 'FLICKR', 2 )
,('TWITTER', 'MICROSOFT', 3 )
,('TWITTER', 'GOOGLE', 4 )
;

Result is OK.
(Changed a little to show more information. And reformatted to my
preference.)
------------------------------ Commands Entered
------------------------------
select x.* -- changed to * from KEYWORD
from RELATEDTAGS x
inner join
RELATEDTAGS y
on x.relatedKEYWORD = y.KEYWORD
and y.relatedKEYWORD = x.KEYWORD
where x.KEYWORD IN ('JAIPUR', 'MICROSOFT') -- Changed to IN from =
and not exists (
select 1 from RELATEDTAGS z
where z.KEYWORD = x.relatedKEYWORD
and priority < (
select min(u.priority)
from RELATEDTAGS u
inner join
RELATEDTAGS v
on u.relatedKEYWORD = v.KEYWORD
and u.relatedKEYWORD = v.KEYWORD -- Redundant
predicate
where u.KEYWORD = z.KEYWORD
)
)
;
------------------------------------------------------------------------------

KEYWORD RELATEDKEYWORD PRIORITY
--------------- --------------- -----------
JAIPUR RAJASTHAN 2
JAIPUR PINK 4
MICROSOFT TWITTER 2

3 record(s) selected.

Here is another query. Also OK.
------------------------------ Commands Entered
------------------------------
SELECT K.*
FROM RelatedTags K
INNER JOIN
RelatedTags R
ON R.Keyword = K.RelatedKeyword
AND K.Keyword = R.RelatedKeyword
WHERE K.Keyword IN ('JAIPUR', 'MICROSOFT')
AND NOT EXISTS
(SELECT *
FROM RelatedTags S
WHERE S.Keyword = R.Keyword
-- or S.Keyword = K.RelatedKeyword
AND S.Priority < R.Priority
AND NOT EXISTS
(SELECT *
FROM RelatedTags T
WHERE T.Keyword = K.Keyword
AND T.RelatedKeyword = S.RelatedKeyword
)
)
;
------------------------------------------------------------------------------

KEYWORD RELATEDKEYWORD PRIORITY
--------------- --------------- -----------
JAIPUR RAJASTHAN 2
JAIPUR PINK 4
MICROSOFT TWITTER 2

3 record(s) selected.

Add an row.
INSERT INTO RelatedTags
VALUES
('WARNE', 'GOOGLE', 1 )
;

The query returned an extra row.
------------------------------ Commands Entered
------------------------------
select x.*
from RELATEDTAGS x
inner join
RELATEDTAGS y
on x.relatedKEYWORD = y.KEYWORD
and y.relatedKEYWORD = x.KEYWORD
where x.KEYWORD IN ('JAIPUR', 'MICROSOFT')
and not exists (
select 1 from RELATEDTAGS z
where z.KEYWORD = x.relatedKEYWORD
and priority < (
select min(u.priority)
from RELATEDTAGS u
inner join
RELATEDTAGS v
on u.relatedKEYWORD = v.KEYWORD
and u.relatedKEYWORD = v.KEYWORD -- Redundant
predicate
where u.KEYWORD = z.KEYWORD
)
)
;
------------------------------------------------------------------------------

KEYWORD RELATEDKEYWORD PRIORITY
--------------- --------------- -----------
JAIPUR IPL 1
JAIPUR RAJASTHAN 2
JAIPUR PINK 4
MICROSOFT TWITTER 2

4 record(s) selected.

This query must be OK.
------------------------------ Commands Entered
------------------------------
SELECT K.*
FROM RelatedTags K
INNER JOIN
RelatedTags R
ON R.Keyword = K.RelatedKeyword
AND K.Keyword = R.RelatedKeyword
WHERE K.Keyword IN ('JAIPUR', 'MICROSOFT')
AND NOT EXISTS
(SELECT *
FROM RelatedTags S
WHERE S.Keyword = R.Keyword
-- or S.Keyword = K.RelatedKeyword
AND S.Priority < R.Priority
AND NOT EXISTS
(SELECT *
FROM RelatedTags T
WHERE T.Keyword = K.Keyword
AND T.RelatedKeyword = S.RelatedKeyword
)
)
;
------------------------------------------------------------------------------

KEYWORD RELATEDKEYWORD PRIORITY
--------------- --------------- -----------
JAIPUR RAJASTHAN 2
JAIPUR PINK 4
MICROSOFT TWITTER 2

3 record(s) selected.