Jump to content

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"

Link to comment
https://forums.phpfreaks.com/topic/4105-negative-sql-query/
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]
Link to comment
https://forums.phpfreaks.com/topic/4105-negative-sql-query/#findComment-14254
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?
Link to comment
https://forums.phpfreaks.com/topic/4105-negative-sql-query/#findComment-14359
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
Link to comment
https://forums.phpfreaks.com/topic/4105-negative-sql-query/#findComment-14364
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.