How to select tables with records existing in another table?

[bullzzz]

Hi Everyone.

I am having trouble writing a select statement to get some records from one table with a reference ID existing in another record. For example.

Table X has ID 1,2,4,6,9. And Table Y has ID 1,2,3,4,5,6,7,8,9.

How can I retrieve all the records in Table Y that has the ID 1,2,4,6 & 9 without using a do while loop?

I tried using the statement "select * from table Y where exists (select * from table X where Y.ID=X.ID)". But only one record is shown. Can any one explain why?

[gavwvin]

SELECT * FROM tableX, tableY WHERE tableX.id=tableY.id

[Rivux]

Its called Joins, look them up. You have your choice of Inner Joins, Outer Joins and Cartesian Joins. For example you would have

Select someinfo from tableX
Inner Join tableY On tableX.ID = tableY.ID

You should always specify what table the data is coming from, so tableX.ID instead of just ID. But its a must when you do a join and the same field exists in both tables, but you can avoid that error just by doing it for every field.

[bullzzz]

SELECT * FROM tableX, tableY WHERE tableX.id=tableY.id

This select statement merges tableX and tableY together where both IDs are the same. Its almost the same, but I only want data from tableY.

[bullzzz]

Its called Joins, look them up. You have your choice of Inner Joins, Outer Joins and Cartesian Joins. For example you would have

Select someinfo from tableX
Inner Join tableY On tableX.ID = tableY.ID

You should always specify what table the data is coming from, so tableX.ID instead of just ID. But its a must when you do a join and the same field exists in both tables, but you can avoid that error just by doing it for every field.

I do not need to join data from 2 tables where tableX.ID=tableY.ID. The only set of data important to me are only data from tableY. Anyway, I will go read up on the JOIN's statements again. Thanks.

Regards.

[DaveMaxwell]

This select statement merges tableX and tableY together where both IDs are the same. Its almost the same, but I only want data from tableY.

Then you could use

SELECT TableY.* FROM tableX, tableY WHERE tableX.id=tableY.id

[r937]

I do not need to join data from 2 tables where tableX.ID=tableY.ID. The only set of data important to me are only data from tableY.

then instead of SELECT * you would select only those columns you want from table Y


so instead of

select * from tableX, tableY 
 where tableX.id=tableY.id

you would say

select tableY.foo
     , tableY.bar
     , tableY.qux
  from tableY
     , tableX 
 where tableY.id
     = tableX.id

thus listing only columns from tableY after the SELECT

the above is an inner join and it can also be written like this --

select tableY.foo
     , tableY.bar
     , tableY.qux
  from tableY
inner
  join tableX 
    on tableY.id
     = tableX.id

you mentioned you didn't need the join, but you do

you have to somehow specify that you want only those rows from Y which are also in X, and the easiest way to do that is with a join

another way is with a subquery --

select tableY.foo
     , tableY.bar
     , tableY.qux
  from tableY
 where exists
       ( select 1
           from tableX
          where tableX.id
              = tableY.id )

this might actually be more understandable to some people's way of thinking, but -- and this is a good thing -- the subquery is actually executed by the database engine as a join

[bullzzz]

Thanks guys.