Interesting query - hard to title

Triune · #1 (**permalink**) 11-06-06, 10:33

I have an 'albums' table and I have a 'restrictions' table. I want to pull all of the albums except the ones that are restricted from a certain territory.

e.g.
SELECT * FROM albums [except WHERE album.cat_no = restrictions.cat_no AND territory = 'GB']

How would I go about turning the bit in the brackets into a real life query? Bearing in mind some albums will have no restrictions.

GongXi · #2 (**permalink**) 11-07-06, 01:23

try this

SELECT * FROM albums WHERE album.cat_no not in (select cat_no from restrictions ) AND territory = 'GB'

Kris Zywczyk · #3 (**permalink**) 11-07-06, 03:12

I would use something like that:

Code:

select a.* 
from albums a left outer join restrictrions r on a.cat_no = r.cat_no and
                                                 territory = 'GB'
where r.cat_no is null

Regards,
Kris

Kris Zywczyk · #4 (**permalink**) 11-07-06, 03:19

I am sorry - I just entered onto improper forum

Abowe syntax is for MS SQL Server.
I am not sure if the syntax is the same on MySQL - the idea still should work.

Regards,
Kris

Triune · #5 (**permalink**) 11-07-06, 04:48

Quote:

Originally Posted by GongXi

try this

SELECT * FROM albums WHERE album.cat_no not in (select cat_no from restrictions ) AND territory = 'GB'

That's almost it, except the territory column is in the restrictions table (which is in the sub query so not available). Any idea how to fix that?

Thanks, GongXi.

Triune · #6 (**permalink**) 11-07-06, 07:31

Ack, sorry. I didn't have the restrictions table in the FROM clause.

Triune · #7 (**permalink**) 11-07-06, 11:38

Okay, the query still doesn't do what I want it to. This is hard!

The query is looking for 'albums.cat_no' in 'territory.restrictions' - so none of the albums are found if no restrictions are set!

Any ideas anyone?

Triune · #8 (**permalink**) 11-07-06, 11:47

Quote:

Originally Posted by Kris Zywczyk

I would use something like that:

Code:

select a.* 
from albums a left outer join restrictrions r on a.cat_no = r.cat_no and
                                                 territory = 'GB'
where r.cat_no is null

Regards,
Kris

Hi Kris,

I tried the following:

Quote:

SELECT DISTINCT(id), title, artist, cat_no, description, release_date FROM albums LEFT OUTER JOIN territory_restrictions ON albums.cat_no = territory_restrictions.restrictions_cat_no AND territory_restrictions.restrictions_territory = 'US' WHERE publish = 1 AND deleted != 1 AND territory_restrictions.restrictions_cat_no = NULL ORDER BY cat_no ASC LIMIT 0, 5

Which does not give me an error, but yields no results.