[MySQL] Selecting from two tables, but no duplicates

Discussion in 'Web Design & Coding' started by vern, Dec 28, 2004.

  1. vern

    vern Dominus Political User Folding Team

    Messages:
    1,571
    Location:
    Minnesota, USA
    I want to select from two tables. Each table has a column called "course_id". I want to select from one table if the "course_id" doesn't exist in the other table. How would I do this in one or multiple queries? I was looking at unions, joins, subqueries but I don't know if that is the most efficient or easiest solution. I've messed with all three but with no luck. Any help is appreciated. I'm only really selecting from the first table, but making sure that the "course_id" doesn't exist in the second table.
     
  2. Khayman

    Khayman I'm sorry Hal... Political User Folding Team

    Messages:
    5,518
    Location:
    England
    One way would be somthing like

    SELECT * FROM table1 t1 WHERE t1.course_id NOT IN (select t2.course_id from table2 t2)

    Also might want to look up the EXISTS command
     
    vern likes this.
  3. X-Istence

    X-Istence * Political User

    Messages:
    6,498
    Location:
    USA
  4. vern

    vern Dominus Political User Folding Team

    Messages:
    1,571
    Location:
    Minnesota, USA
    Kayman, thanks, that worked out really well. I was playing with EXISTS and NOT EXISTS before, but it seems NOT IN gave me the desired results.

    I also played around with joins but with no luck. Thanks.
     
  5. Enhand

    Enhand OSNN Junior Addict

    Messages:
    13
    mysql should used "NOT IN"