CODEWITHSUNDEEP
mysqlsql
DroidOS
DroidOS

Reputation: 8900

SQL - Counting references in multiple tables

Here is the scenario. I have a MySQL table called modules which contains one or more entries each identified by a unique string - the module ID (mid).

There are a few other tables (scripts,images,sets...) which contain objects each of which"belong" to one of the modules - identified by the column 'mid' in each table.

Prior to allowing a user to drop a module entry, I need to check that the operation will not leave any orphaned objects in any of the other tables. Here is an example to make this clearer

Table modules
mname      mid
Mod1      abcd1234
Mod2      wxyz9876

Table scripts
sname     mid 
  A       abcd1234
  B       wxyz9876

Table images
iname   mid
 A      abcd1234

Table  sets
sname    mid

One or more of the tables may contain no, or no matching, entries.

I have written and tested a spot of SQL to handle this. 

SELECT COUNT(*) FROM `images` WHERE mid = 'abcd1234'
UNION
SELECT COUNT(*) FROM `sets` WHERE mid = 'abcd1234'
UNION
SELECT COUNT(*) FROM `scripts` WHERE mid = 'abcd1234'

which very obligingly returns 1 implying that the module is "in use" and cannot be dropped. However, my SQL skills are pretty basic. I would much appreciate anyone who could tell me if this is a safe way to do things.

Upvotes: 1

Views: 195

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

Not really a good way.

The UNION without ALL removes duplicate results. That would give you 1 if you had 3 rows returning 1. UNION ALL will make it return 3 rows with the count for each table, even when they are duplicate. After that you SUM them and you get the final count.

You should do:

SELECT SUM(cnt) FROM (
 SELECT COUNT(*) as cnt FROM `images` WHERE mid = 'abcd1234'
 UNION ALL
 SELECT COUNT(*) FROM `sets` WHERE mid = 'abcd1234'
 UNION ALL
 SELECT COUNT(*) FROM `scripts` WHERE mid = 'abcd1234'
) a

Upvotes: 2

Ronnis
Ronnis

Reputation: 12843

You could build something around the following concept, given that there is a one-to-many relation between modules and the other tables.

select mid
      ,count(scripts.sname) as scripts
      ,count(images.iname)  as images
      ,count(sets.sname)    as sets
  from modules
  left join images   using(mid)
  left join sets     using(mid)
  left join scripts  using(mid) 
 where mid = 'abcd1234'
 group 
    by mid;

You could for example add the count(..) together, or including a HAVING clause.

Upvotes: 0

Related Questions