SQL UNION only returning result from first SELECT

Question

I am attempting to perform a SQL call that fetches a row from one table and counts the records from another table. However, the call only returns results from the first SELECT. Code is as follows:

$sql = "SELECT row_1 FROM table_1 WHERE user_id = :user_id
UNION
SELECT COUNT(row_2) as new_var FROM table_2 WHERE user_id = :user_id";

$query = $this->db->prepare($sql);
$params = array('user_id' => $var);
$query->execute($params);
$result = $query->fetch();

The result should be:

array(
  'row_1' => val_1,
  'new_var' => val_2
)

The solution to the problem is undoubtedly simple, yet eludes me! Thanks in advance!

Solved!

It would appear UNION was inappropriate in this case. Revamping the SQL call to use JOIN solved the issue:

SELECT table_1.row_1, COUNT(table_2.row_2) AS new_var 
FROM table_1 LEFT JOIN table_2
ON table_1.user_id = table_2.user_id AND table_1.user_id = :user_id

Thanks for all your help!

Answer 1

According to your script, you seem like you want 2 columns. One with row_1 and one with count(user_id). If so, UNION is not the correct operator.

Your code as it stands returns 2 rows with one column each. But in your PHP you called $query->fetch(). That only returns the first row.

Option 1

Fetch both rows with $query->fetchAll()

Option 2

Make it two columns by using JOIN instead of a UNION

Answer 2

If I had to guess, then row_1 is an integer, and one of the values exactly matches the count.

UNION not only combines two tables/subqueries. It also removes duplicates. So, my first guess for fixing the problem is:

SELECT row_1 FROM table_1 WHERE user_id = :user_id
UNION ALL
SELECT COUNT(user_id) as new_var FROM table_2 WHERE user_id = :user_id

Note: You should also identify which rows are the original data rows and which is the summary row. Result sets in SQL are unordered. There is no guarantee that the rows will be returned in a particular order -- unless you use ORDER BY.