Jump to content

Archived

This topic is now archived and is closed to further replies.

abighill@gmail.com

Negative SQL Query

Recommended Posts

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"

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.