MySQL join multiple columns from multiple tables

Question

I am trying to combine multiple columns from multiple tables, but some of my data seem to be recurring. How should my query be to avoid this?

I have the following tables:

LEGE_ROM
+----------+------------+-----+
| ansattnr | dag        | rom |
+----------+------------+-----+
| L102     | 2012-09-23 | b01 |
+----------+------------+-----+
| L100     | 2012-09-12 | k10 |
+----------+------------+-----+
| L100     | 2013-03-05 | k10 |
+----------+------------+-----+
| L100     | 2014-03-02 | k10 |
+----------+------------+-----+
| L100     | 2014-10-15 | K10 |
+----------+------------+-----+
| L100     | 2014-11-03 | k10 |
+----------+------------+-----+
| L102     | 2012-09-12 | k11 |
+----------+------------+-----+
| L100     | 2014-11-10 | k12 |
+----------+------------+-----+
| L110     | 2012-09-13 | k12 |
+----------+------------+-----+

ROM_BEHANDLING
+-----+--------------+
| rom | behandling   |
+-----+--------------+
| b01 | kirurgisk    |
+-----+--------------+
| k10 | konsultasjon |
+-----+--------------+
| k11 | konsultasjon |
+-----+--------------+
| k12 | konsultasjon |
+-----+--------------+

The desired output table is (table has been edited):

+----------+-----+--------------+
| ansattnr | rom | behandling   |
+----------+-----+--------------+
| L100     | k10 | konsultasjon |
+----------+-----+--------------+
| L102     | k11 | konsultasjon |
+----------+-----+--------------+
| L110     | k12 | konsultasjon |
+----------+-----+--------------+

And join should be used to achieve the desired output table.

I tried to use the following query:

SELECT lr.ansattnr, lr.rom, rb.behandling
FROM LEGE_ROM lr JOIN ROM_BEHANDLING rb
WHERE rb.behandling='konsultasjon';

You need an `On` clause for your `Join`. `FROM LEGE_ROM lr JOIN ROM_BEHANDLING rb ON lr.rom = rb.rom` — Siyual, Nov 05 '14 at 17:01

Donal · Accepted Answer · 2014-11-05T18:12:36.070

1

I think this is what you are looking for.

It uses a LEFT JOIN, so that it will return the row from ROM_BEHANDLING even if there is no corresponding row in LEGE_ROM.

SELECT lr.ansattnr, lr.rom, rb.behandling
FROM ROM_BEHANDLING rb 
INNER JOIN LEGE_ROM lr on lr.rom = rb.rom
GROUP BY lr.rom
HAVING rb.behandling = 'konsultasjon';

Working example here

edited Nov 05 '14 at 18:12

answered Nov 05 '14 at 17:06

Donal

31,121
10
63
72

Is there any way to remove the recurring instances of L100, so that L100 k10 konsultasjon will show only one time in the table? – slimmey Nov 05 '14 at 17:23
@slimmey Yes, you can do that by adding a `GROUP BY` clause. I have updated the answer with it. – Donal Nov 05 '14 at 17:26
The update gives me the following error in my phpMyAdmin (client version 5.1.73): "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE rb.behandling='konsultasjon' LIMIT 0, 30' at line 5" – slimmey Nov 05 '14 at 17:32
WHERE cannot come after GROUP BY. HAVING can, but in the absence of any aggregating functions, it's hard to see why you would do this. Still, I like that someone upvoted this. – Strawberry Nov 05 '14 at 17:33
@slimmey sorry, I have fixed that now. – Donal Nov 05 '14 at 17:39
The edit with HAVING removed ansattnr L102 k11 konsultasjon from the result for some reason. I will edit the question for the desired table. – slimmey Nov 05 '14 at 17:55
OK, I see the issue. Should be grouping by rom. This is because there is 2 L102 ansattnr values in there for different roms in the LEGE_ROM table. – Donal Nov 05 '14 at 18:13

MySQL join multiple columns from multiple tables

1 Answers1