Return multiple rows from unrelated SQL subqueries

Question

I'm writing a SQL script to check the integrity of a MySQL DB. The queries check different aspects of different tables, so they're not related. I don't want to relate them. I just want to see the results of the different queries one after the other.

The individual queries work fine, but I'd like to run them at the same time, but keep the results separate. They can return nothing or they can return multiple rows. (Ideally, I'd like headings to make the results more legible.)

The error is

"Error code: 1242. Subquery returns more than 1 row"

Here's what I've tried:

SELECT
(-- Teams not associated with any employees
SELECT id 
  FROM teams
 WHERE NOT EXISTS
          (SELECT id 
             FROM employee_teams
            WHERE employee_teams.teamID IN(teams.id))) AS TeamNoEmployee,

(-- Teams deleted but associated with employees
SELECT teamID 
  FROM employee_teams
 WHERE NOT EXISTS
          (SELECT id 
             FROM teams
            WHERE teams.id IN(employee_teams.teamID))) AS TeamsDeleted

Update: @JohnHC got it. Here's my new version. I need to be able to add a header for each query so I can tell them apart.

-- Teams not associated with any employees
SELECT 'Team No Employee' AS Test, id
  FROM teams
 WHERE NOT EXISTS
          (SELECT id 
             FROM employee_teams
            WHERE employee_teams.teamID IN(teams.id))
UNION
-- Teams deleted but associated with employees
SELECT 'Teams Deleted', teamID 
  FROM employee_teams
 WHERE NOT EXISTS
          (SELECT id 
             FROM teams
            WHERE teams.id IN(employee_teams.teamID))

the Problem is that you may have a different number of Teams in each subquery, so a different number of rows. and SQL does not like that. — RealCheeseLord
– RealCheeseLord, Commented Aug 25, 2017 at 8:11
Thx @RealCheeseLord. How do I just show the results of each sub query separately? — beachCode
– beachCode, Commented Aug 25, 2017 at 14:03

JohnHC · Accepted Answer · 2017-08-25 08:32:21Z

3

You're not really relating those in a table. Try it the other way round, like this:

select 'Empty Team' as Scenario, id
from teams t1
left join employee_teams et
  on et.teamID = t1.id
where et.id is null
union 
select 'Deleted Team', teamid
from employee_teams et
left join teams t2
  on t2.id = et.teamID
where t2.id is null

edited Aug 25, 2017 at 8:32

answered Aug 25, 2017 at 8:07

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Serg Over a year ago

Check the second query. OP needs team ID, I would expect something like SELECT DISTINCT teamID ..

JohnHC Over a year ago

@Serg good spot, thanks. No distinct, I've moved to a union instead of union all

beachCode Over a year ago

Thanks for the answer. I actually don't want want to relate them. I just want to see the results of the different queries one after the other.

Roberto Over a year ago

@beachCode What do you mean by "I don't want to relate them"?

beachCode Over a year ago

Sorry if I wasn't clear. I just want to see the results of the different queries. I don't need them combined into a single results set

|

Collectives™ on Stack Overflow

Return multiple rows from unrelated SQL subqueries

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related