Select what is in one table and not in the other

[andnos]

Hello,

I have a dilema, I have two tables, the primary ID from the first is a foreign ID in the second. How can I structure a query to select only the records from the first table ONLY IF the primary ID of this table is not listed in the secondary table as a foreign key.

thanks for any help

Andy

 

[cLFlaVA]

what version of mysql?



*cLFlaVA
----------------------------
[tt]somebody set up us the bomb![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[andnos]

Sorry for leaving out that critical info:

MySQL 4.1.19

 

[cLFlaVA]

you should be able to do:

select * from table1
 where primaryID not in (
    select foreignID
      from table2
);

*cLFlaVA
----------------------------
[tt]somebody set up us the bomb![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[andnos]

You rock Cory!!!! works great..
I need to read up more on my sql skills..

 

[cLFlaVA]

haha...

you're welcome. take a look at

http://www.mysql.com

- it's got verything you need to know about mysql, as well as what version support what functionality.



*cLFlaVA
----------------------------
[tt]somebody set up us the bomb![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[andnos]

Thanks for the tip, I've tried mysql.com, but it takes like an hour and 10 pages before I can exactly what I need.

Can you help me with this, since you're on a roll:

In table1, I have a foreign key from table2, in table two, the key has listed titles, is it possible to write one query where I select the foreign key from table 1 (among other things), and in the same query it pulls the titles that is refereced in table2? or do I have to do two queries?

Again, thanks for the help...

 

[cLFlaVA]

look up joins.

http://dev.mysql.com/doc/refman/4.1/en/join.html

try this for starters:

  select a.foreignID
       , a.otherColumn
       , b.title
    from table1 a join table2 b
      on a.foreignID = b.foreignID

*cLFlaVA
----------------------------
[tt]somebody set up us the bomb![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[andnos]

Killer... works like magic.. and I need to go to barnes and noble and buy two books on MySQL.

thanks for the help Cory

 

[r937]

andnos: why two books?

cLFlaVA: what you say? main screen turn on



r937.com | rudy.ca

 

[andnos]

I always buy two books on a subject that I'm fuzzy in.

1) is the XXX For Dummies to get the big picture of the subject, and 2) The Advanced/Professional Guide to XXX, to get the details once I know what can be accomplished

 

[cLFlaVA]

r937: i know, i plugged it in for you.



*cLFlaVA
----------------------------
[tt]somebody set up us the bomb![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]