SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Sep 2004
    Location
    singapore
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to select tables with records existing in another table?

    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?

  2. #2
    SitePoint Guru gavwvin's Avatar
    Join Date
    Nov 2004
    Location
    Cornwall, UK
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT * FROM tableX, tableY WHERE tableX.id=tableY.id

  3. #3
    SitePoint Member
    Join Date
    Jan 2004
    Location
    London, Ontario
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Member
    Join Date
    Sep 2004
    Location
    singapore
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by gavwvin
    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.

  5. #5
    SitePoint Member
    Join Date
    Sep 2004
    Location
    singapore
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wink

    Quote Originally Posted by 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.
    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.

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,266
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by bullzzz
    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
    Code:
    SELECT TableY.* FROM tableX, tableY WHERE tableX.id=tableY.id
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bullzzz
    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
    Code:
    select * from tableX, tableY 
     where tableX.id=tableY.id
    you would say
    Code:
    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 --
    Code:
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Sep 2004
    Location
    singapore
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •