Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2006
    Posts
    14

    Unanswered: Interesting query - hard to title

    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.

  2. #2
    Join Date
    Jun 2004
    Posts
    57
    try this

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

  3. #3
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    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

  4. #4
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    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

  5. #5
    Join Date
    Nov 2006
    Posts
    14
    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.

  6. #6
    Join Date
    Nov 2006
    Posts
    14
    Ack, sorry. I didn't have the restrictions table in the FROM clause.

  7. #7
    Join Date
    Nov 2006
    Posts
    14
    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?

  8. #8
    Join Date
    Nov 2006
    Posts
    14
    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:
    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.

  9. #9
    Join Date
    Jun 2004
    Posts
    57
    you can try this,

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

  10. #10
    Join Date
    Jun 2004
    Posts
    57
    Kris code should work, may be you have to check your data.

  11. #11
    Join Date
    Nov 2006
    Posts
    14
    Quote Originally Posted by GongXi
    you can try this,

    SELECT * FROM albums WHERE album.cat_no not in (select cat_no from restrictions where territory = 'GB')
    That's it! Yay!!!!

    Thank you so much GongXI! You genuis.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •