join or union type of problem - 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

General SQL Server Forums

New to SQL Server Programming

join or union type of problem

Author

Topic

mrtweaver
Yak Posting Veteran

67 Posts

Posted - 2009-11-09 : 21:44:10

I have a small problem that I am trying to accomplish. I have tried joins and unions but just can not seem to get it right.

I have two views. One is called report_a and the other is called report_b. Both contain the same number of columns.

What I want to do is take all of rows in report_a, and any rows that are not in report_a but are on report_b fill those in. What I am trying to do is key off of the column mach_num. The reason why is because even though there would be a row for mach_num=1 in both tables not all the data in that row for each table is equal. Example:

report_a
mach_num jobtask operator standard
1 1234567 12345 2700
2 1234567 11223 7000
3 1234567 98765 5000
5 9876542 77665 2000
6 9876542 87654 9000

report_b
1 1234567 12345 2500
2 1234567 11223 6500
3 1234567 98765 5000
4 9864875 13711 5500
5 9876542 77665 2200
6 9876542 87654 9000

If you notice in report_a there is no mach_num = 4 but in report_b there is. So when the query is run here is what the output would look like. As I stated in there is the mach_num in report_a ignore the data in report_b. I can only hope I am clear. but anyway here is the output.

1 1234567 12345 2700
2 1234567 11223 7000
3 1234567 98765 5000
4 9864875 13711 5500
5 9876542 77665 2200
6 9876542 87654 9000

I have tried as mentioned using the UNION without the ALL but because one piece of data was different it pulled both in anyway. When I tried using a left outer join the results were undesirable as well.

Thanks and have a good day.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-10 : 01:17:37

I think INTERSECT is what you need:
http://msdn.microsoft.com/en-us/library/ms188055(SQL.90).aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-10 : 01:26:12

this ?


select * from report_a

union all

select * from report_b 
where not exists (select * from report_a where report_a.match_num = report_b.match_num)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources