SQL: Two similar queries into one query such that returns one table with two columns

Question

I want to run two queries that are pretty similar such that the query returns one table with two columns (one column for each query ran)

Query #1:

SELECT groupID 
FROM tbl 
WHERE username = 'admin' AND permission LIKE 'sample.%'

Query #2:

SELECT groupID 
FROM tbl 
WHERE username = 'administrator' AND permission LIKE 'sample.%'

Desired return:

admin	administrator
groupID.1	groupID.1
groupID.3	groupID.2

Is it possible? I was able to get the expected return by doing a FULL OUTER JOIN but I read that JOINS are between two tables and this only looks at one table.

And what do you expect when one query returns x rows and the other query returns y rows? How do you know that "groupID.2 is associated with "groupID.3"? Or is this simply a "display" issue of two columns where the values in any particular row are not related? — SMor, Jun 03 '22 at 18:52
If these columns are independent then you better of combining them in the presentation layer. You can however create a single query that for each group will have something like "Y/N" for each of the two user names in one row. — PM 77-1, Jun 03 '22 at 18:52
@SMor I was thinking of returning null. I don't care too much about the rows of the first column (admin) correlating with the other. I just wanted to produce one table with the data I need so I can work off of that. — chrisans, Jun 03 '22 at 18:58
This feels like a presentation question (so, for whatever technology will present the results to the user), not a data question (or a `sql` question). Plus, it doesn't appear that data for the columns in each row of your output have any relationship. Have your report run 2 queries and present the results side-by-side. — dougp, Jun 03 '22 at 19:02
What if the two queries have a different number of results? Think through this carefully. It seems obvious to say the shorter query should just fill in `NULL` values, but how would it know how many it needs? — Joel Coehoorn, Jun 03 '22 at 19:18

lemon · Accepted Answer · 2022-06-03T19:58:57.510

4

You could try assigning a row number first to each row of each table, then match the two table on this ranking:

WITH cte_admin AS (
         SELECT groupID, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rn
         FROM tbl1 
     ),
     cte_administrator AS (
         SELECT groupID, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rn
         FROM tbl2
     )
SELECT t1.groupID AS g1,
       t2.groupID AS g2
FROM       cte_admin t1
INNER JOIN cte_administrator t2
        ON t1.rn = t2.rn
UNION
SELECT t1.groupID AS g1,
       t2.groupID AS g2
FROM      cte_admin t1
LEFT JOIN cte_administrator t2
       ON t1.rn = t2.rn
WHERE t2.rn IS NULL
UNION
SELECT t1.groupID AS g1,
       t2.groupID AS g2
FROM      cte_administrator t1
LEFT JOIN cte_admin  t2
       ON t1.rn = t2.rn
WHERE t2.rn IS NULL

A fully tested solution will be provided if you can share contents from the table tbl.

For the full outer join, three joins are needed:

INNER JOIN to match corresponding row numbers between the two tables
LEFT JOIN from table1 to table2 WHERE table2 is null to match excess rows from table1 in case this has more rows than table2
LEFT JOIN from table2 to table1 WHERE table1 is null to match excess rows from table2 in case this has more rows than table1

A pseudo test is done here.

edited Jun 03 '22 at 19:58

answered Jun 03 '22 at 19:12

lemon

14,875
6
18
38

1

Nearly exactly what I was working up. You beat me by seconds. – dougp Jun 03 '22 at 19:14
Yeah it's a rather simple idea behind here, as long as there's no relationship between the two tables, other than the current order of the rows. @dougp – lemon Jun 03 '22 at 19:16
2

You might need a `full join` here, to allow for different numbers of results in each original query. – Joel Coehoorn Jun 03 '22 at 19:20
1

It is important to highlight the fact that `ORDER BY (SELECT 1)` is logical nonsense - intentionally used here because the goal is simply not logical. Run this multiple times and you can get the values "associated" in different order. And you need a full join since there is no guarantee that the number of "admins" is the same as the number of "administrators". – SMor Jun 03 '22 at 19:20
@SMor Not saying this is the best solution, this is just a working one, and find `ORDER BY (SELECT 1)` a really cool trick to make it work, learnt from other experienced people on this platform. – lemon Jun 03 '22 at 19:21
@Joel Coehoorn Yeah, I think for a comprehensive solution, data is needed. – lemon Jun 03 '22 at 19:23
@lemon this helps a lot! I had a question with the `INNER JOIN` with the conditional that t1.rn = t2.rn, if lets say column 1 (admin) has data that column 2 doesn't have, would there be a new row where column has data and column 2 is null? – chrisans Jun 03 '22 at 19:34
I've updated the answer with a working full outer join, following Joel's relevant suggestion. You can try it in a purposefully built environment at the link added at the bottom of the answer. @chrisans – lemon Jun 03 '22 at 19:53
Not sure if there's a reason to do the unioned full join. But I think the null test on the last one has the wrong table alias. – shawnt00 Jun 03 '22 at 20:08
@lemon Instead of the unions, wouldn't what you had earlier work with replacing the INNER JOIN with a FULL OUTER JOIN – chrisans Jun 03 '22 at 20:09
I think table aliases are correct, the order of the two tables are inverted and I'm using only LEFT JOIN operations, the null values should be always in the second called tables. Check the link for testing. For what concerns FULL OUTER JOIN operations, they don't exist in MySQL, you can only emulate them: https://stackoverflow.com/questions/4796872/how-can-i-do-a-full-outer-join-in-mysql. Main reason why there are two left joins is basically because we don't know which of the two tables has more rows than the other. – lemon Jun 03 '22 at 20:11
1

Ok. I should have looked at the table names. I do suppose it feels more natural to me that the aliases remain consistent throughout. – shawnt00 Jun 03 '22 at 20:16
@lemon I was not criticizing the solution - that was the approach that popped into my head. Just raising the awareness of OP to the issue and hopefully encouraging you to highlight that in your discussion. Usually that ORDER BY approach is a kludge to avoid some other problem but introduces a logic fault that can be easily missed. – SMor Jun 03 '22 at 21:17
That's a rather interesting detail to know about the `ORDER BY` clause employed with a `SELECT 1`, thanks! @SMor – lemon Jun 03 '22 at 21:25

SQL: Two similar queries into one query such that returns one table with two columns

1 Answers1