Selecting from two tables, with different columns, where one needs a count

Question

I have two tables, TableA and TableB. I need to select one count value from TableA, based on a where condition. I need to select two values from TableB. I'd like all the values in one result set. There will never be more than one row in the result set.

Here's what I have now:

SELECT count(id) FROM TableA WHERE ($some_where_statement) SELECT owner, owner_ID from TableB

I know this should be simple, but this is throwing an error. Any suggestions?

Answer 1

You can cross join to join rows from two unrelated tables:

SELECT T1.cnt, T2.owner, T2.owner_ID
FROM (SELECT count(id) FROM TableA WHERE ($some_where_statement)) AS T1
CROSS JOIN (SELECT owner, owner_ID from TableB) AS T2

To have only one row in the result set, it is assumed that both subqueries only return one row. I suspect that this is not the case for the second subquery. You are probably missing a where clause.