Combining SQL queries without JOIN or UNION
Solution 1
It might be late reply but below will work
SELECT tblactivate_account.activate_account, tblmembers.members
FROM
( SELECT COUNT(activate_account.id) activate_account
WHERE activate_account.email = email ) tblactivate_account,
( SELECT COUNT(members.id) members FROM members
WHERE members.email = email ) tblmembers
modification for nrathaus answer
Solution 2
You need to create two separate queries and join their result not JOIN their tables.
Some pseudo untested SQL but should work:
SELECT tblactivate_account.activate_account, tblmembers.members
FROM
( SELECT COUNT(activate_account.id) activate_account
WHERE activate_account.email = :email ) AS tblactivate_account,
( SELECT COUNT(members.id) FROM members
WHERE members.email = :email ) AS tblmembers
Solution 3
JOIN and UNION are differents.
In your query you have used a CROSS JOIN operation, because when you use a comma between two table you apply a CROSS JOIN. It becomes INNER JOIN if you in your WHERE clause put a link between two table, in your case members and activate_account aren't linked.
UNION can be used when you want get values from two distinct sets. i.e. if you want a list of employees and a list of sailors, supposed, employees and sailors are in two different table.
display-name-is-missing
Updated on November 25, 2022Comments
-
display-name-is-missing 12 months
I need to count rows in two tables, and I would like to make a single SQL-query do the job. After some research I found an solution which I modified to this:
SELECT COUNT(activate_account.id), COUNT(members.id) FROM members, activate_account WHERE members.email = :email or activate_account.email = :email
Now my question is: is this a good way to combine two SELECT queries, is this just as good solution as if I would have used JOIN or UNION (meaning should there be any peformance improvement compared to if I would have two separate SELECT queries)?