Negative SQL Query

[abighill@gmail.com]

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"

[Barand]

When using LEFT JOIN to find unmatched records you are looking for NULL values (no matched data) in the right table. Try

[code]SELECT u.*
FROM fett_url u
LEFT JOIN fett_url_to_data d
ON d.url_id = u.url_id AND d.data_id = 2
WHERE d.url_id IS NULL
ORDER BY u.url_title[/code]

[abighill@gmail.com]

Thanks Barand, that is perfect.

Do you think you could recommend somewhere that I may be able learn more about constructing SQL queries? Books or tutorials? I would like to know more about this kind of thing but don't really know where to start.

Thanks again!

An additional question:

What does 'SELECT u.*' mean?

[Barand]

try [a href=\"http://www.w3schools.com/sql/default.asp\" target=\"_blank\"]http://www.w3schools.com/sql/default.asp[/a]

In my query I assigned table aliases to the two table names and then referenced the column names using the aliases "u" and "d". It saves repeating table names and makes the queries clearer to read.

SELECT [!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]u[!--colorc--][/span][!--/colorc--].*
FROM fett_url [!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]u[!--colorc--][/span][!--/colorc--]
LEFT JOIN fett_url_to_data [!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]d[!--colorc--][/span][!--/colorc--]
ON [!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]d[!--colorc--][/span][!--/colorc--].url_id = [!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]u[!--colorc--][/span][!--/colorc--].url_id AND [!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]d[!--colorc--][/span][!--/colorc--].data_id = 2
WHERE [!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]d[!--colorc--][/span][!--/colorc--].url_id IS NULL
ORDER BY [!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]u[!--colorc--][/span][!--/colorc--].url_title

So u.* is all columns from table u, which is fett_url

[abighill@gmail.com]

Ahh! [realisation] - I get it!

'SELECT u.*' is only returning columns from 'fett_url' and not 'fett_url_to_data'

Thanks again Barand! Muchly appreciated!