rafael bonametti rafael bonametti - 7 months ago 9
SQL Question

MySQL - Display null column from child table if all values are not distinct

I have the following tables, for example:

invoices

ID Name
1 A
2 B
3 C
4 D
5 E


transactions

ID Invoice_ID User_ID
1 1 10
2 1 10
3 1 10
4 2 30
5 3 20
6 3 40
7 2 30
8 2 30
9 4 40
10 3 50


Now I want to make a select that will pull the invoices and the user_id from the related transactions, but of course if I do that I won't get all the ids, since they may be distinct but there will be only one column for that. What I want to do is that if there are distinct User_ids, I will display a pre-defined text in the column instead of the actual result.

select invoices.id, invoices.name, transactions.user_id(if there are distinct user_ids -> return null)

from invoices
left join transactions on invoices.id = transactions.invoice_id


and then this would be the result

ID Name User_ID
1 A 10
2 B 30
3 C null
4 D 40
5 E null


Is this possible?

Answer

You can do the following :

select 
   invoices.id, 
   invoices.name, 
   IF (
      (SELECT COUNT(DISTINCT user_id) FROM transactions WHERE transactions.invoice_id = invoices.id) = 1, 
      (SELECT MAX(user_id) FROM transactions WHERE transactions.invoice_id = invoices.id), 
      null
   ) AS user_id
from invoices

Or, alternatively, you can use the GROUP_CONCAT function to output a comma-separated list of users for each invoice. It is not exactly what you asked, but maybe in fact it will be more useful :

select 
    invoices.id, 
    invoices.name, 
    GROUP_CONCAT(DISTINCT transactions.user_id SEPARATOR ',') AS user_ids
from invoices
left join transactions on invoices.id = transactions.invoice_id
group by invoices.id