Jump to content


Photo

Query Problem


  • Please log in to reply
3 replies to this topic

#1 shan_cool

shan_cool
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts
  • LocationChennai,India

Posted 09 August 2006 - 04:53 AM

Hi all,
        I have two tables "table1" and "table2" and "id" is the field common to both the tables..  All id data will be in "table1" say 10,20,30,40,50,60  . but only few of them will get stored in "Table2" say 20 ,40.

Now i need the retrieve the values of id's that are in "table1" and not in "table2" Ex: 10,30,50,60.

My query is

"Select h1.id from table1 h1,table2 h2 where h1.id Not In(h2.id)"

"Select h1.id from table1 h1,table2 h2 where h1.id <>2.id)"

both display Repetition of all values and gives me 'n' number of records.. cartesian product

plz lemmme know how to do it?

Thx in advance,
Shan

#2 shan_cool

shan_cool
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts
  • LocationChennai,India

Posted 09 August 2006 - 05:01 AM

hi,
    I have tried that too. it displays the same numbaer of repeated records ony... any other way?

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 August 2006 - 02:06 PM

Try the following:

Select h1.id from table1 h1 left join table2 h2 on ( h1.id = h2.id) where h2.id is null

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 shan_cool

shan_cool
  • Members
  • PipPipPip
  • Advanced Member
  • 53 posts
  • LocationChennai,India

Posted 10 August 2006 - 04:33 AM

Hi,
      Thanks Verymuch for ur reply.. The Query works fine..

Rgds,
Shan




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users