Posted by noone on 10/02/29 11:41

abighill wrote:
> Problem
> -------------
>
> I want to return all URL records from 'fett_url' that are not currently
> indexed in the lookup table 'fett_url_to_data' where 'data_id=2'.
>
> i.e.
>
> fields=> url_id, url_title, url_link, url_description
>
> "1","Flickr","http://flickr.com","Photo tool"
>
> Previously I have managed to make a Positive SQL Query (see below)
> when I try to reverse this including "!=" the results are not what I
> require (See Negative SQL Query).
>
> Any ideas would be greatly appreciated.
>
>
> Project Fett Data Stucture
> -----------------------------------------
>
> table=> fett_url
> fields=> url_id, url_title, url_link, url_description
>
> "1","Flickr","http://flickr.com","Photo tool"
> "2","Google","http://google.co.uk","Search Engine"
> "3","Yahoo","http://yahoo.co.uk","Web Portal"
>
> lookup table=> fett_url_to_data
> fields=> url_id, data_id
>
> "2","1"
> "2","2"
> "2","6"
> "3","2"
> "3","5"
> "3","6"
>
>
> Positive SQL Query
> ------------------------------
>
> Query:
>
> SELECT *
> FROM fett_url_to_data
> LEFT JOIN fett_url
> ON fett_url_to_data.url_id = fett_url.url_id
> WHERE fett_url_to_data.data_id = 2
> ORDER BY url_title
>
> Returns:
>
> fields=> url_id, data_id, url_id, url_title, url_link, url_description
>
> "2","2","2","Google","http://google.co.uk","Search Engine"
> "3","2","3","Yahoo","http://yahoo.co.uk","Web Portal"
>
>
> Negative SQL Query
> ------------------------------
>
> Query:
>
> SELECT *
> FROM fett_url_to_data
> LEFT JOIN fett_url
> ON fett_url_to_data.url_id != fett_url.url_id
> WHERE fett_url_to_data.data_id = 2
> ORDER BY url_title
>
> Returns:
>
> fields=> url_id, data_id, url_id, url_title, url_link, url_description
>
> "2","2","1","Flickr","http://flickr.com","Photo tool"
> "3","2","1","Flickr","http://flickr.com","Photo tool"
> "3","2","2","Google","http://google.co.uk","Search Engine"
> "2","2","3","Yahoo","http://yahoo.co.uk","Web Portal"
>


SELECT a.url_id,
a.url_title,
a.url_link,
a.url_description,
b.url_id,
b.data_id
FROM fett_url a
LEFT OUTER JOIN fett_url_to_data b
ON a.url_id = b.url_id
WHERE a.data_id = 2
and b.url_id is null
ORDER BY a.url_title

Because you are doing a left-outer join, if it does not exist in B
(fet_url_to_data) it should return NULL.

for consistency in all SQL-compliant databases, you will want to list
each field, unless you are doing a single table select. - for
simplicity, I always use an abbreviated table alias.

[Back to original message]