Matching records in 2 tables - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Matching records in 2 tables

Author

Topic

Zentosoft
Starting Member

1 Post

Posted - 2004-05-15 : 22:59:20

I have 2 tables. 1 called Sub, the other called Issues.

Sub has the following structure.
PUB|CUSTNO|STRT_DTE|LAST_ISU|

Issues has the following structure.
PUB|ISS_ID|VOL_ISU|

Because table Sub has some invalid entries, i.e. Sub has some records that has STRT_DTE and some LAST_ISU dates that are not found in the Issues table, I want to filter all these out with a SQL query. I have yet to work it out though.

Note that the Issues table contains records such as...
ACP|20040101|VOL1
ACP|20040201|VOL2
ACP|20040301|VOL3
BBY|20040115|VOL1
BBY|20040220|VOL2

So I'm trying to match the STRT_DTE and LAST_DTE from the SUB table with the ISS_ID, and if the dates don't exist in the ISSUES table, then I don't select that record.

Please help me. I've been stuck on this for 2 hours now.

Thanks.
John.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-16 : 00:01:31

If you do an inner join to the Issues table on both dates, you won't pull back any records that don't exist in Issues. Isn't that all you're saying you want?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-16 : 09:41:49

This will give everything from sub with a match in issues
select s.*
from Sub s
join issues i1
on s.PUB = i1.PUB
and s.STRT_DTE = i1.ISS_ID
join issues i2
on s.PUB = i2.PUB
and s.LAST_ISU = i1.ISS_ID

This will give the sub and issues recs where they exist - nulls for issues recs that don't exist
select *
from Sub s
left join issues i1
on s.PUB = i1.PUB
and s.STRT_DTE = i1.ISS_ID
left join issues i2
on s.PUB = i2.PUB
and s.LAST_ISU = i1.ISS_ID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources