MySQL lookup joining 3 tables

I have 3 tables, location, lookup and exclude with the following info...

location table

+----+--------------+
| id | location     |
+----+--------------+
| 1  | Location a   |
| 2  | Location b   |
| 3  | Location c   |
| 5  | Location d   |
| 6  | Location e   |
| 7  | Location f   |
| 8  | Location g   |
| 9  | Location h   |
+----+--------------+

lookup table

+----+-------------+------+
| id | location_id | code |
+----+-------------+------+
| 1  | 2           | PR6  |
| 2  | 2           | PR7  |
| 3  | 2           | PR9  |
| 4  | 5           | WA2  |
| 6  | 8           | WA3  |
+----+-------------+------+

exclude table

+----+-------------+------+
| id | location_id | code |
+----+-------------+------+
| 1  | 2           | PR5  |
| 2  | 2           | PR8  |
+----+-------------+------+

These tables exist for a basic postcode lookup. I need to create some SQL so a search can be done using the postcode but I need to take into account any excluded postcodes so for example... If I was to search for "PR7" I would get "Location B" as the result but if I was to search for PR5 or PR8, I would not get "Location B" as these are in the exclude table. Here is what I have so far....

SELECT
lookup.*, exclude.`code` as exclude, location.location
FROM lookup
LEFT JOIN
exclude
ON lookup.location_id = exclude.location_id
LEFT JOIN
location
ON location.location = lookup.location_id
WHERE lookup.`code` LIKE 'PR%' AND (exclude.`code` NOT LIKE 'PR8%' OR ISNULL(exclude.`code`))
GROUP BY location.location
ORDER BY location.id

In my SQL above, the user typed "PR8" which is being sent to the where clause using PHP variables, I look for the 1st 2 letters of the postcode in the lookup table and then I am also trying to look for what the user entered in the exclude table to omit this from the results. Unfortunately I cant tell where I am going wrong, I am getting the following results where as based on the above tables I should be getting no results due to the exclusion...

+----+-------------+------+---------+
| id | location    | code | exclude |
+----+-------------+------+---------+
| 1  | Location b  | PR8  | PR5     |
+----+-------------+------+---------+

I hope I have explained this well enough but please tell me if I need to add more detail.

I would really appreciate some guidance on this, thanks so much in advance :)

Answers


I think you use "NOT IN".

So your "AND" would be more like

AND lookup.`code` NOT IN (SELECT 'code' FROM exclude)

EDIT I'm pretty sure this would work, not sure about best performance, I'll leave that to smarter people than I.

SELECT
lookup.*, location.location
FROM lookup
LEFT JOIN
location
ON location.id = lookup.location_id
WHERE lookup.`code` LIKE 'PR%' 
AND lookup.`code` NOT IN (SELECT `code` FROM exclude) 
GROUP BY location.location
ORDER BY location.id

see this may help you

Three table JOIN syntax in SQL Here is a general SQL query syntax to join three or more table. This SQL query should work in all major relation database e.g. MySQL, Oracle, Microsoft SQLServer, Sybase and PostgreSQL :

SELECT t1.col, t3.col FROM table1 join table2 ON table1.primarykey = table2.foreignkey
                                  join table3 ON table2.primarykey = table3.foreignkey

see more details here


Need Your Help

Removing files in a sub directory based on modification date

linux bash shell ls

Hi so I'm trying to remove old backup files from a sub directory if the number of files exceeds the maximum and I found this command to do that

InfoWindow close() function is not working

google-maps google-maps-api-3 maps infowindow

I am trying to manage an arrray of InfoWindow objects from the Google Maps JavaScript API v3. My array is defined as global so I can manage all the Infowindow objects like this:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.