Negative SQL Query

[abighill]

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"

[noone]

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.

[abighill]

Hi noone,

I tried your suggested SQL query and phpMyAdmin returned:

MySQL said:
#1054 - Unknown column 'a.data_id' in 'where clause'

This is because the 'data_id' does not exist in 'fett_url a' so I
changed the SQL query to read:

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 b.data_id = 2
and b.url_id is null
ORDER BY a.url_title

This time the query ran successfully but nothing was returned.

[abighill]

I posted the same probelm on the PHP Freaks Forum and Barand kindly
solved the problem for me:

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

Thanks everyone!

See post here: http://www.phpfreaks.com/forums/inde...howtopic=87476