CODEWITHSUNDEEP
sqlsql-server
Alan
Alan

Reputation: 279

SQL Server count() returns different results when using joins

I am new to SQL Server and I am not looking for a solution (but it may help others), rather, I would like to understand the behaviour / why I get two different results from the two pseudo queries below.

The reason I have joined two other tables is because I will need to count all items in Vehicle against the 'Date_Recorded' in 'Garage'. All recorded since 2010. So before I did this I wanted to be sure I was getting the same total count from the table 'Car' and also get the same result with the joins, before I added the 'isDate' on 'Garage' condition, that is when I noticed the difference in the results.

I would have thought the joins would have been ignored?

Hope someone can advise? Thanks in advance!

SELECT count(Car.CAR_ID) AS Car_ID
FROM Vehicle Car
INNER JOIN Road Rd
ON Car.CAR_ID = Rd.CAR_ID
JOIN Garage g
ON Rd.GARAGE_ID = g.GARAGE_ID
----------------------------------------------
Car_ID
----------------------------------------------
226923


SELECT count(Car.CAR_ID) AS Car_ID
FROM Vehicle Car
----------------------------------------------
Car_ID
----------------------------------------------
203417

Upvotes: 0

Views: 146

Answers (2)

A_Sk
A_Sk

Reputation: 4630

INNER JOIN: Returns all rows when there is at least one match in BOTH tables.

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table.

RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table.

FULL JOIN: Return all rows when there is a match in ONE of the tables.

you are using inner join thats why you are getting the wrong result from the actual count() result. if you want to get all the record from the left table the you have to use left join in this query then you'll get the result of count() same as the main table[left table].

Upvotes: 3

Aheho
Aheho

Reputation: 12821

You either have

  • multiple records in the Road table with the same Car_ID or
  • multiple records in the Road table with the same Garage_ID or
  • Both of the above

You may be able to run the following to get what you want (assuming there is always a match in the road and garage tables):

 SELECT count(Distinct Car.CAR_ID) AS Car_ID
 FROM Vehicle Car
 INNER JOIN Road Rd
 ON Car.CAR_ID = Rd.CAR_ID
 JOIN Garage g
 ON Rd.GARAGE_ID = g.GARAGE_ID

Upvotes: 1

Related Questions