Results 1 to 6 of 6
Nov 24, 2004, 10:54 #1
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
Nov 24, 2004, 11:34 #2
- Join Date
- Jan 2004
- Uppsala, sverige
- 2 Post(s)
- 1 Thread(s)
select * from table1 where not exists (select * from table2 where table1.col1 = table2.col2)
Nov 24, 2004, 14:56 #3
Does something like this work too?
SELECT tbl75.col1, tbl75.col2
WHERE ((tbl75.col1<>(SELECT tbl74.col2 FROM tbl74 WHERE ((tbl74.col22=0) AND (tbl74.col24=16463)))) AND (tbl75.col3=1));
Nov 24, 2004, 15:29 #4
I gave this a try but need some additions:
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:
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.
Nov 24, 2004, 15:42 #5
I'm getting closer. I've tried this:
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.
Nov 24, 2004, 15:47 #6
- Join Date
- Jul 2002
- Toronto, Canada
- 58 Post(s)
- 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