Find matching records of two different tables in SQL Server

Question

I have two tables in one of them a seller saves a record for a product he is selling. and in another table buyers save what they need to buy.

I need to get a list of user ids (uid field) from buyers table which matches a specific product on sales table. this is what I have written:

select n.[uid] from needs n left join ads(getdate()) a on n.mid=a.mid and a.[year] between n.from_year and n.to_year and a.price between n.from_price and n.to_price and n.[uid]=a.[uid] and a.pid=n.pid

Well I need to use a where clause to eliminate those records which doesn't match. as I think all of these conditions are defined with ON must be defined with a where clause. but joining needs at least one ON clause. may be I shouldn't join two tables? what can I do?

Please use a INNER JOIN and see if that meets your requirements. In order words, delete the word LEFT from your query. — Guttsy
– Guttsy, Commented Sep 20, 2013 at 6:48
greattttt. I have always been curious about the differences of left and inner join. This was what I was looking for. — Ashkan Mobayen Khiabani
– Ashkan Mobayen Khiabani, Commented Sep 20, 2013 at 6:50

Guttsy · Accepted Answer · 2013-09-20 06:54:17Z

There is an important difference between LEFT JOIN and JOIN, or more accurately OUTER and INNER joins respectively.

Inner joins require that both sides of the join match. In other words, if you:

had a table representing People
you had another table representing Automobiles
and each automobile had a PersonId
and joined these tables using ON with the PersonId

using LEFT (OUTER) JOIN would return all people, even those without automobiles. INNER JOIN only returns the people with vehicles.

This article may help: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Collectives™ on Stack Overflow

Find matching records of two different tables in SQL Server

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related