On May 18, 12:10 am, Lennart <Erik.Lennart.Jons... (AT) gmail (DOT) com> wrote:
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, 12:10 am, Lennart <Erik.Lennart.Jons... (AT) gmail (DOT) com> wrote:
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... (AT) gmail (DOT) com> wrote:
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

On May 18, 2:53 am, bang123 <vasuad... (AT) gmail (DOT) com> wrote:
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

On May 18, 2:53 am, bang123 <vasuad... (AT) gmail (DOT) com> wrote:
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.

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.

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.