SQL match rows from two table with same fields

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

Site Related Forums

Site Related Discussions

SQL match rows from two table with same fields

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-18 : 08:03:20

Ammar writes "I have two tables with three common fields. I would like to know SQL Query to match the rows on (<>) basis on all three fields as complete row. Here is an example.


Table 1.

Price  Size  Year
10      2     2003
10      3     2003
20      2     2003
20      3     2003

Table 2.

Price  Size  Year
10      2     2003
20      3     2003

I need Following Results

10      3     2003
20      2     2003

Explaination

Logically it should subtract Table 2 From Table 1 and give me the remainder, and it should match complete row not column by column match. I have another solution of converting to a varchar string and then matching but that is not acceptable."

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-18 : 08:36:54

select t1.*
from tbl1 t1
left outer join tbl2 t2
on t1.Price = t2.Price
and t1.Size = t2.Size
and t1.Year = t2.Year
where t2.Price is null

==========================================
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.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-02-18 : 08:38:01

select * from table1 t1
left join table2 t2 on t1.price=t2.price and t1.size=t2.size and t1.year=t2.year
where t2.price is null
union
select * from table2 t1
left join table2 t2 on t1.price=t2.price and t1.size=t2.size and t1.year=t2.year
where t2.price is null

should get you there....

aw
Starting Member

1 Post

Posted - 2004-02-18 : 10:30:10

quote:
Originally posted by AndrewMurphy

select * from table1 t1
left join table2 t2 on t1.price=t2.price and t1.size=t2.size and t1.year=t2.year
where t2.price is null
union
select * from table2 t1
left join ~~table2~~ table1 t2 on t1.price=t2.price and t1.size=t2.size and t1.year=t2.year
where t2.price is null

should get you there....

slight typo fix -

also, assuming the question is not (answer 1)

select * from table1
minus
select * from table2

but instead

select * from
(select * from table1
union all
select * from table2)
group by price, size, year
having count(*) = 1

how about -

select * from table1 t1
full outer join table2 t2 on t1.price=t2.price and t1.size=t2.size and t1.year=t2.year
where t2.price is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources