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
– Siyual, Commented Nov 5, 2014 at 17:01

Donal · Accepted Answer · 2014-11-05 18:12:36Z

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 5, 2014 at 18:12

answered Nov 5, 2014 at 17:06

Donal

33.2k10 gold badges69 silver badges75 bronze badges

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

7 Comments

slimmey Over a year ago

Is there any way to remove the recurring instances of L100, so that L100 k10 konsultasjon will show only one time in the table?

Donal Over a year ago

@slimmey Yes, you can do that by adding a GROUP BY clause. I have updated the answer with it.

slimmey Over a year ago

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"

Strawberry Over a year ago

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.

Donal Over a year ago

@slimmey sorry, I have fixed that now.

|

Collectives™ on Stack Overflow

MySQL join multiple columns from multiple tables

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related