Why doesn't this (mysql) UNION solve my problem?

Question

I'm new to sql. Basicially, I'm trying to SUM an amount from one table, over somekind of ID. I got that part, but I also want to know if that person is (in)active and that's from another table. I know I can use my ID, and I've tryed with a UNION, but no luck so far.

Gives the data p.inactive.

SELECT p.inactive
FROM deb d
    RIGHT JOIN pat p ON(d.debitor = p.cpr);

d.amount works but p.inactive does not, because of the LEFT JOIN

SELECT SUM(d.amount), p.inactive
FROM deb d
    LEFT JOIN pat p ON(d.debitor = p.cpr)
GROUP BY d.debitor;

So I've tryed to combine them with a UNION, but p.inactive is still null

(   SELECT d.debitor, p.inactive FROM deb d 
        LEFT JOIN pat p ON d.debitor = p.cpr
)
UNION
(
    SELECT d.debitor, p.inactive FROM deb d 
        RIGHT JOIN pat p ON d.debitor = p.cpr
    WHERE d.debitor IS NULL
);

I'm totally stucked now? Can anyone help? Is it even posible without seeing my DB. Let me know if I need to provide more data/info.

---------------------- UPDATE

Table deb

debitor | amount

1...|...10

1...|...20

1...|...45

2...|...30

2...|...10

Table pat

cpr...|...inactive

1...|...0

2...|...1

3...|...0

4...|...0

5...|...1

6...|...1

The person with p.cpr and d.debitor = 1, has a sum amount of 75 and is INACTIVE

The person with p.cpr and d.debitor = 2, has a sum amount of 40 and is ACTIVE

Final Update:

My bad, I assummed there was some deb.debitors in the pat.cpr, and there wasn't, hence the no matches. Next time I will test for that first ofcourse.

`p.cpr` is a foreign key to `d.debitor` or vice versa? Please provide some sample data — Robert Kock, Jan 08 '19 at 13:40
@radbyx `WHERE d.debitor IS NULL` is not needed in first union query? — Ingus, Jan 08 '19 at 13:44
`d.debitor` is unique? `p.cpr` is unique? Please provide some sample data and desired result so we might understand the relation between both tables. — Robert Kock, Jan 08 '19 at 13:59
Ups I mean it's not a primary key, but it's a foreign key. e.g. d.debitor matches the p.cpr's... If that make sence? — radbyx, Jan 08 '19 at 14:02
@RobertKock In my last query I tryed to make a UNION in mysql, that's how you are supose to make a FULL JOIN in MySql I think. But there must be something wrong with it. — radbyx, Jan 08 '19 at 14:29
I repeat: please add sample data and desired output. It's quite hard guessing this way. — Robert Kock, Jan 08 '19 at 14:29
@RobertKock I have updated my question with a clear example now. — radbyx, Jan 08 '19 at 14:40
In my 3rd query I followed the second query in the Accepted answer from https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql — radbyx, Jan 08 '19 at 14:47
My bad, I assummed there was some deb.debitors in the pat.cpr, and there wasn't, hence the no matches. Next time I will test for that first ofcourse. — radbyx, Jan 09 '19 at 07:54

Salman A · Accepted Answer · 2019-01-09T07:46:05.887

1

Assuming debitor - cpr are joined many to one you can use your left join example but include the desired column in your GROUP BY clause:

SELECT SUM(d.amount), p.inactive
FROM deb d
LEFT JOIN pat p ON d.debitor = p.cpr
GROUP BY d.debitor, p.inactive;

Or you can join after grouping (this allows you to include columns that you normally cannot use inside group by):

SELECT a.debitor, a.total, p.whatever
FROM (
    SELECT d.debitor, SUM(d.amount) total
    FROM deb d
    GROUP BY d.debitor
) AS a
LEFT JOIN pat p ON a.debitor = p.cpr

PS: If you want to eliminate NULL values from the right table then you need an INNER JOIN.

edited Jan 09 '19 at 07:46

answered Jan 08 '19 at 13:44

Salman A

262,204
82
430
521

Ok I'll give it a try. Debitor and cpr is not primary keys but the cpr is foreign key and debitor have the same format so you can use that too. Hopes it explain it enough, I'm new to sql and else let me know. – radbyx Jan 08 '19 at 14:00
I think your first query looks like my second right? I can't see the different. It produces the same. – radbyx Jan 08 '19 at 14:06
I added `p.inactive` in group by. Without it your second query would be illegal but MySQL allows it. – Salman A Jan 08 '19 at 14:10
I've replaced "p.whatever" with "p.inactive" to your last query, but it still gives me null values. – radbyx Jan 08 '19 at 14:11
As I see it, I still have the same problem. The issue is not the group by. It's that I can't call anything on `p` after a LEFT JOIN pat. I know that's how the LEFT JOIN work, but I want to call on both "d." and "p.". – radbyx Jan 08 '19 at 14:18
If inactive is null then either (i) the matched record (a.debitor = p.cpr) contains NULL in that column (ii) no record matched at all. For testing, you could try adding a primary key column from pat table inside select and group by and see if it is also NULL. – Salman A Jan 08 '19 at 14:20
Maybe I'm trying to compose something not possible or misunderstand it completely. I just think this should durable. When I run my first query, I can see all the values is either "0" or "1", there is no one with "null". The "null" must come from the join I think. – radbyx Jan 08 '19 at 14:24
You may wanna try a `FULL JOIN` – Robert Kock Jan 08 '19 at 14:24
@SalmanA when I change your last query to a RIGHT JOIN instead, it gives me the "inakt" data I want, but the "a.debitor" is null. – radbyx Jan 08 '19 at 14:26
1

According to your sample data, both queries provide expected result. If you get NULL then you have deb.debitor in your data that are not present in pat.cpr. – Salman A Jan 08 '19 at 14:46
@SalmanA I might be new to sql but I can admit my fault. I badly assummed there must have been some deb.debitor in the pat.cpr but there wasn't, I just did a test. – radbyx Jan 09 '19 at 07:45

Robert Kock · Answer 2 · 2019-01-08T15:06:13.243

1

So d.debitor is a foreign key towards p.cpr.
Maybe I'm missing something, but in that case the solution looks quite straightforward:

SELECT    pat.cpr,
          pat.inactive,
          COALESCE(SUM(deb.amount), 0) AS sum_amount
FROM      pat
LEFT JOIN deb
       ON pat.cpr = deb.debitor
GROUP BY  pat.cpr,
          pat.inactive;

It returns a total amount of 0 in case there's no corresponding deb.debitor for a specific pat.cpr.

edited Jan 08 '19 at 15:06

answered Jan 08 '19 at 15:00

Robert Kock

5,795
1
12
20

The sum column is all with 0's (because the COALESCE changes the null's to 0's i guess). I'm sure you know your stuff like Salman but I don't know how to describe my case better. I spent a hour writting my question so I put alot of effort into it, but I still feel I most work on that part. I know the answers are simple for you but I need to structure the question very good and with a sample or something to make it more simple and clear for you. – radbyx Jan 09 '19 at 07:11
Oh I have no matches in (pat.cpr = deb.debitor), I'm so sorry, my bad. – radbyx Jan 09 '19 at 07:55

Why doesn't this (mysql) UNION solve my problem?

2 Answers2