> On May 17, 6:57 am, bang123 gmail.com> wrote:
>
>
>

>> On May 17, 9:33 am, Lennart gmail.com> wrote:

>

>>> On May 17, 5:03 am, bang123 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