SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql question for finding unmatched records

    I'm trying to come up with a way of displaying records that don't match between two tables.

    For example, I can join table1.col1 to table2.col14 as an "inner join", or "left join", or "right join" to show matching records that exist, but is there a way to link them by only showing results that don't match instead of matching? Hope this makes sense. I'm using this sql as records entries in a list box. I don't want users to be able to select records from the list box that have already been picked.

    I'm using Postgresql 7.4

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

  3. #3
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does something like this work too?

    SELECT tbl75.col1, tbl75.col2
    FROM tbl75
    WHERE ((tbl75.col1<>(SELECT tbl74.col2 FROM tbl74 WHERE ((tbl74.col22=0) AND (tbl74.col24=16463)))) AND (tbl75.col3=1));

  4. #4
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I gave this a try but need some additions:

    SELECT *
    FROM tbl75
    WHERE not exists (select * from tbl74 where (tbl75.col1 = tbl74.col2)

    I need to eliminate records that are returned from tbl75 where col3=0. And also need to the query to ignore records in tbl74 where col22=1. And then finally I need to limit tbl74.col24 to the url parameter "recordid1".

    I've tried every combination I can think of but can't get it working, such as the following:
    SELECT *
    FROM tbl75
    WHERE not exists (select * from tbl74 where (tbl75.col1 = tbl74.col2 AND tbl75.col3=1 AND tbl74.col22=0 AND tbl74.col24='recordid1'))

    But I'm thinking I have things in the wrong order or the wrong place. It never returns the records properly.

  5. #5
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm getting closer. I've tried this:

    SELECT *
    FROM tbl75
    WHERE (not exists (select * from tbl74 where (tbl75.col1 = tbl74.col2 AND tbl74.col22=0))) AND (tbl75.col3=1) AND (tbl74.col24='recordid1')

    It works great except it lists the same records multiple times.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i have read all those attempts, and they are all different

    what exactly are you trying to show?

    and why are you using NOT EXISTS?

    i think a LEFT OUTER JOIN might work:
    Code:
    select tbl75.col1
         , tbl75.col2
      from tbl75
    left outer
      join tbl74
        on tbl75.col1
         = tbl74.col2
       and tbl74.col22 = 0
       and tbl74.col24 = 16463
     where tbl75.col3 = 1
       and tbl74.col2 is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •