SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    display the records from table2 which arent used in table1

    Hi Guys,

    I'm not sure how I will go at explaining this, so trap yourselves in....

    I am trying to display the values from table2 which are not present in table1. table2's pk is a fk in table1, and I want to be able to get a list of the 'unused' values.

    kinda like

    select table2.modelId from table2, table1 where table1.modelId <> table2.modelId and table1.otherId = 5;

    table1.otherId is the other value, used to make a composit key in table1.

    So for otherId = 5, in table1 I dont want to see the modelId's that is HAS, (a fk from table2) but rather thoes it HAS NOT.

    Do I need a temporary table or a sub query? Its been a while since i have dont much sql, thanks for looking,
    p.

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select modelId 
    from table2
    where not exists(
    select *
     from table1 
    where table1.modelId = table2.modelId 
    and table1.otherId = 5)

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT 
       t.modelId
    FROM
       table2 t
    LEFT JOIN
       table1 t1
    ON
      t.modelId=t1.modelId
      AND
      t1.otherId=5
    WHERE
      t1.modelId IS NULL

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi guys, thanks.

    me thinks i will need to upgrade to mysql 4.1 b4 i can get these queries to work.

    Thanks again,
    p.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    nope, not necessary, tombell's method works in versions before 4.1, you should try it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey your right! (of course your right hehe).

    I had a feeling the join method worked, tho I tested and had no luck. Since then I have done some more reading and such, retried it an it worked not sure why is wouldn't the other day. Go Team!

    The purpose of this query is to weed out model id's that already exist for 'otherid'. that way eliminating the 1062 error.

    Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY)

    Message: Duplicate entry '%s' for key %d

    Thanks to everyone for there help
    Paul.


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
  •