Jump to content


Photo

slect a record that is in table 1 but not in table 2


  • Please log in to reply
3 replies to this topic

#1 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 17 July 2006 - 11:00 AM

hi,

say i have

table 1--------------

members_id
members_name

and then table 2

selected_id

wat i want to do is select all the records in table 1 that do not have their id in table 2?

#2 hvle

hvle
  • Members
  • PipPipPip
  • Advanced Member
  • 667 posts
  • Locationmelbourne, Australia

Posted 17 July 2006 - 11:41 AM

my sql is rusty but try this, don't yell if it doesn't work:

$sql = "select * from table1 where members_id not in (select selected_id from table2 where 1)";
Life's too short for arguing.

#3 micah1701

micah1701
  • Members
  • PipPipPip
  • Advanced Member
  • 613 posts
  • LocationEllington, CT USA

Posted 17 July 2006 - 11:43 AM

I think hvle's is a little better then mine, i would do

"SELECT table_1.id FROM table_1.id WHERE (SELECT id FROM table_2.id WHERE table_2.id NOT LIKE table1.id)";


but again, don't yell at me if it doesn't work.
"Confidence in the face of risk."

#4 ShogunWarrior

ShogunWarrior
  • Members
  • PipPipPip
  • Advanced Member
  • 528 posts
  • LocationIreland

Posted 17 July 2006 - 01:23 PM

Another Option, I think:
"SELECT table_1.id,table_1.id as t1id  FROM table_1 WHERE (SELECT COUNT(*) FROM table_2 WHERE table_2.id=t1id) < 1"
<a href="http://www.daviddora...nmedia.com/">My New Site/Blog</a> | <a href="http://www.daviddora...m/check/">Check your page for broken links/images/scripts</a>

Zend Certified Engineer
Follow me on Twitter: http://twitter.com/davidd




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users