CODEWITHSUNDEEP
mysqlsqlcountleft-join
matt
matt

Reputation: 2997

SQL: Getting the COUNT of one table and JOIN the others

I have a query that looks like such:

SELECT COUNT(A.Value1) AS Count, B.id AS Id1, C.id AS Id2
FROM Table1 A LEFT JOIN Table2 B ON (B.Name LIKE '%UserInput1%')
LEFT JOIN Table3 C ON (C.Name LIKE '%UserInput2%')
WHERE A.Value1 LIKE 'CertainValue'

The query runs perfectly fine; it's just that the COUNT I get from Table1 does not give me the actual count of the number of Value1s in Table1, but rather the number of Value1s multiplied by the number of tables that are actually joined.

Is there any way I can obtain the COUNT from just Table1 that fits into the criteria of A.Value1 LIKE 'CertainValue', while joining the other tables?

FYI: I'm using MySQL!

Upvotes: 4

Views: 9460

Answers (3)

Barmar
Barmar

Reputation: 780724

The problem is that you're doing the counting after joining, so you're counting the number of A.Value1's in the resulting cross-product. If you want the count from the original table, you should use a sub-query. I don't think this will cause much of a performance problem, it should actually improve it, since it reduces the size of the resulting join.

SELECT ACount, B.id AS Id1, C.id AS Id2
FROM (Select COUNT(*) ACount FROM Table1 WHERE Value1 LIKE 'CertainValue') A 
LEFT JOIN Table2 B ON (B.Name LIKE '%UserInput1%')
LEFT JOIN Table3 C ON (C.Name LIKE '%UserInput2%')

Upvotes: 2

Ampersand
Ampersand

Reputation: 1

The query as written will return a cartesian product because A, B and C are not joined at all. Also, count is a keyword, best to use another identifier in the select. Try writing the query as something like this:

SELECT COUNT(A.Value1) AS MyCount, B.id AS Id1, C.id AS Id2
FROM Table1 A
LEFT JOIN Table2 B ON A.?? = B.??
LEFT JOIN Table3 C ON A.?? = C.??
WHERE A.Value1 LIKE '%CertainValue%'
AND B.Name LIKE '%UserInput1%'
AND C.Name LIKE '%UserInput1%'
GROUP BY B.id, C.id

Upvotes: 0

Holger Brandt
Holger Brandt

Reputation: 4354

Well, I suppose you can add DISTINCT to your COUNT.

SELECT COUNT(DISTINCT A.Value1) AS Count, B.id AS Id1, C.id AS Id2
FROM Table1 A LEFT JOIN Table2 B ON (B.Name LIKE '%UserInput1%')
LEFT JOIN Table3 C ON (C.Name LIKE '%UserInput2%')
WHERE A.Value1 LIKE 'CertainValue'

The whole query really isn't very pretty since you are joining tables that do not seem to have any relationship with each other. The result set should be quite a mess.

Upvotes: 4

Related Questions