Return Data based on Column

I am modifying a current query which returns n amount of rows. I would need to return the same amount of rows but with one more column of data.

I have two sql tables shown below. Products and Product Attribute Values. When i run this query

SELECT * FROM  dbo.Product WHERE DealerRowId = '10'

i get a few 1000 rows back. But when i run this query

SELECT * FROM Product p
inner JOIN ProductAttributeValues pav ON pav.SerialNo = p.SerialNo
WHERE (p.DealerRowId = '10') and pav.Name = 'sequence'

I get back less rows.

My question is how would i get back all the rows from the products table that match the DealerRowId and any rows from the Product Attribute Value tables that match on serial no, i would expect that any product that does not have a serial to return null.

Answer

You want to move the condition on the second table to the on caluse:

SELECT *
FROM Product p LEFT JOIN
     ProductAttributeValues pav
     ON pav.SerialNo = p.SerialNo AND pav.Name = 'sequence'
WHERE p.DealerRowId = '10';