Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JeffT
Posting Yak Master
111 Posts |
 Posted - 2006-02-14 : 08:48:41
|
| Hi,I want to search through 3 tables (TableB, TableC, TableD) to find which hdr_ctl_nbr(s) are on those tables but not on TableA. In other words, TableA should match the combined tables B, C, and D but it doesn't, so I want to find what's missing on TableA. I know how to search TableB (see SELECT below) but how would I add TableC and TableD to this statement ? Thanks, Jeff Select hdr_ctl_nbr, count(*) from TableA cwhere c.hdr_ctl_nbr not exists (select hdr_ctl_nbr from TableB)group by c.hdr_ctl_nbr |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-02-14 : 08:55:30
|
| Select * from TableB B where not exists(select * from TableA where keycol=B.keycol)Similarly write qurey for other tablesMadhivananFailing to plan is Planning to fail |
 |
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2006-02-14 : 09:19:00
|
| Thanks very much Madhivanan ! So, if I'm understanding this statement correctly, it is saying:"select all rows in TableB that are NOT in the result set from the select of TableA, where the key field in Table A matches the key field in TableB" ? Select * from TableB B where not exists(select * from TableA where keycol=B.keycol)Thanks again,Jeff |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-02-14 : 09:21:58
|
Yes it is  MadhivananFailing to plan is Planning to fail |
|
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2006-02-14 : 09:42:42
|
| Thanks ! I just ran the following (below) but got zero results. Does this look right ? Thanks, JeffSelect hdr_ctl_nbr from TableB d where not exists(select hdr_ctl_nbr from TableA where hdr_ctl_nbr=d.hdr_ctl_nbr)Select hdr_ctl_nbr from TableC d where not exists(select hdr_ctl_nbr from TableA where hdr_ctl_nbr=d.hdr_ctl_nbr)Select hdr_ctl_nbr from TableD d where not exists(select hdr_ctl_nbr from TableA where hdr_ctl_nbr=d.hdr_ctl_nbr) |
|
|
|
jhermiz
3564 Posts |
Posted - 2006-02-14 : 10:14:44
|
quote:
Originally posted by JeffT
Thanks ! I just ran the following (below) but got zero results. Does this look right ? Thanks, JeffSelect hdr_ctl_nbr from TableB d where not exists(select hdr_ctl_nbr from TableA where hdr_ctl_nbr=d.hdr_ctl_nbr)Select hdr_ctl_nbr from TableC d where not exists(select hdr_ctl_nbr from TableA where hdr_ctl_nbr=d.hdr_ctl_nbr)Select hdr_ctl_nbr from TableD d where not exists(select hdr_ctl_nbr from TableA where hdr_ctl_nbr=d.hdr_ctl_nbr)
Is it what you expected? I mean think about what you're asking...you wrote a query and asked us if what you got back is correct. How in hell are we supposed to know? Check the data in the tables, is it supposed to return 0 rows or do you expect some rows to be returned. If you do expect rows to return provide us with some DDL and DML, some sample data...something!
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
|
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2006-02-14 : 10:47:44
|
| Actually what I was wondering was if my statements, created based on Madhivanan's example, looked correct not if the results looked correct. I understand that I have more research to do to determine why I got zero results which, by the way, is not what I expected... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-02-15 : 00:42:23
|
| Then, post some sample data and the result you wantMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Search Multiple Tables