Jump to content


Photo

Negative SQL Query


  • Please log in to reply
4 replies to this topic

#1 abighill@gmail.com

abighill@gmail.com
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 04 March 2006 - 08:47 PM

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"



#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 04 March 2006 - 09:14 PM

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

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 abighill@gmail.com

abighill@gmail.com
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 05 March 2006 - 11:12 AM

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?

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 05 March 2006 - 11:19 AM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 abighill@gmail.com

abighill@gmail.com
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 05 March 2006 - 01:30 PM

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!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users