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... (AT) gmail (DOT) com> wrote:
select KEYWORD, RELATEDKEYWORD, PRIORITY from RELATEDTAGS x where
KEYWORD = 'JAIPUR'


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

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

On May 17, 5:03 am, bang123 <vasuad... (AT) gmail (DOT) com> wrote:
select KEYWORD, RELATEDKEYWORD, PRIORITY from RELATEDTAGS x where
KEYWORD = 'JAIPUR'


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

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

On May 17, 5:03 am, bang123 <vasuad... (AT) gmail (DOT) com> wrote:
select KEYWORD, RELATEDKEYWORD, PRIORITY from RELATEDTAGS x where
KEYWORD = 'JAIPUR'


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

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

On May 17, 9:33 am, Lennart <Erik.Lennart.Jons... (AT) gmail (DOT) com> wrote:
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, 9:33 am, Lennart <Erik.Lennart.Jons... (AT) gmail (DOT) com> wrote:
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, 9:33 am, Lennart <Erik.Lennart.Jons... (AT) gmail (DOT) com> wrote:
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... (AT) gmail (DOT) com> wrote:
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:57 am, bang123 <vasuad... (AT) gmail (DOT) com> wrote:
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:57 am, bang123 <vasuad... (AT) gmail (DOT) com> wrote:
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...