abighill@gmail.com Posted March 4, 2006 Share Posted March 4, 2006 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_datafields=> 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_dataLEFT JOIN fett_urlON fett_url_to_data.url_id = fett_url.url_idWHERE fett_url_to_data.data_id = 2ORDER BY url_titleReturns: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_dataLEFT JOIN fett_urlON fett_url_to_data.url_id != fett_url.url_idWHERE fett_url_to_data.data_id = 2ORDER BY url_titleReturns: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" Quote Link to comment Share on other sites More sharing options...
Barand Posted March 4, 2006 Share Posted March 4, 2006 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 uLEFT JOIN fett_url_to_data dON d.url_id = u.url_id AND d.data_id = 2WHERE d.url_id IS NULLORDER BY u.url_title[/code] Quote Link to comment Share on other sites More sharing options...
abighill@gmail.com Posted March 5, 2006 Author Share Posted March 5, 2006 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted March 5, 2006 Share Posted March 5, 2006 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 = 2WHERE [!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]d[!--colorc--][/span][!--/colorc--].url_id IS NULLORDER BY [!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]u[!--colorc--][/span][!--/colorc--].url_titleSo u.* is all columns from table u, which is fett_url Quote Link to comment Share on other sites More sharing options...
abighill@gmail.com Posted March 5, 2006 Author Share Posted March 5, 2006 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! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.