Negative SQL Query

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"

re: Negative SQL Query

abighill wrote:[color=blue]
> 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"
>[/color]


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.

re: Negative SQL Query

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.

re: Negative SQL Query

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