display different table data in one row

Question

I have 3 tables:

1st Table - Staff:

------------------------------------
| person_id |   name   | studio_id |
|-----------|----------|-----------|
|     1     |   Bill   |     1     |
|     2     |   Kate   |     1     |
------------------------------------

2nd Table - Studio:

-----------------------------
| studio_id |  studio_name  |
|-----------|---------------|
|     1     |   PeopleProd  |
|     2     |    TheBest    |
-----------------------------

3rd Table - Album:

-----------------------------------
| album_id |   Name   | studio_id |
|----------|----------|-----------|
|     1    |   Hits   |     1     |
|     2    |  Goldset |     1     |
-----------------------------------

I want to display them in one row, so the result will be:

------------------------------------------------------
| studio_id | studio_name | person_name | album_name |
|-----------|-------------|-------------|------------|
|     1     |  PeopleProd |     Bill    |     Hits   |
|     1     |  PeopleProd |     Kate    |   GoldSet  |
------------------------------------------------------

But when I'm using the JOIN query, I'm receiving duplicates:

SELECT studio_id studio_name  person_name album_name 
FROM Studio s
JOIN Album a ON s.studio_id = a.studio_id 
JOIN Staff ss ON s.studio_id = ss.studio_id`.

How can this be fixed?

Now the output:

------------------------------------------------------
| studio_id | studio_name | person_name | album_name | 
|-----------|-------------|-------------|------------|
|     1     |  PeopleProd |     Bill    |    Hits    |
|     1     |  PeopleProd |     Kate    |    Hits    |
|     1     |  PeopleProd |     Bill    |   GoldSet  |
|     1     |  PeopleProd |     Kate    |   GoldSet  |
------------------------------------------------------

You're missing commas around your columns, but I'll assume that's just a typo in the question. Look into the keyword `DISTINCT` to fix your duplicate issue. Unless you're referring to duplicates as the multiple `album_name`s, then look into `GROUP BY` — yanman1234, Jul 26 '17 at 17:33
@yanman1234 distinct doesn't help. Group by needs aggregate function. — Martin, Jul 26 '17 at 17:45
query gives you correct result, both `Bill` and `Kate` have two album names: ` Hits` and `GoldSet`. so 4 rows in result is quite expected. is not? — Oto Shavadze, Jul 26 '17 at 18:01
@OtoShavadze Person and Album are not related. I understand that the result is expected according to the keys, but I need one row result — Martin, Jul 26 '17 at 18:09

Aaron · Answer 1 · 2017-07-26T18:19:30.523

The problem is that you're not technically getting duplicates, if you look at the output you can see that no two rows are exactly the same. In this case, since both album and staff link to studio, the only way to get the desired output is to change the table structure.

The easiest way I see to fix this is to add a person_id foreign key to your album table and then change join Staff ss on s.studio_id = ss.studio_id to join Staff ss on ss.person_id = a.person_id. This way, you can link albums directly to staff and avoid your duplication issue.

Since you can't modify the table structure, your only chance (that I can think of anyway) is to use group by. Yes, you will need to call functions, but assuming album and staff will join to the same studio for each row, you should be able to get away with this:

select max(s.studio_id), max(s.studio_name), ss.person_name, a.album_name
from Studio s
join Album a on s.studio_id = a.studio_id
join Staff ss on s.studio_id = ss.studio_id
group by ss.person_name, a.album_name

If you run that, I'd be really interested to see the output as I unfortunately can't test right now. Which singer gets aligned with which album will likely be either random or duplicate if you can't edit the structure. I'd recommend talking to whoever's in charge of the database and explaining that it's structured improperly, and asking him/her to fix it.

As mentioned in my updated answer, your only real option is to get the structure changed, otherwise there's no way to link a singer to an album. You should talk to whoever maintains the DB and see what you can do @Martin. — Aaron, Jul 26 '17 at 18:24

arielnmz · Answer 2 · 2017-07-26T18:18:07.647

There is nothing that tells what album belongs to what staff, since the album only has a foreign key to the studio and you have 2 of staff with a relation to the same studio, it is correct that you get 4 results each with every album and every staff related to the same studio. Your model is flawed so you have to change it by e.g. add a relation to staff from album. Since JOIN acts as an arithmetic × you will always get this result. If what you want is just put them side by side take a look at this question.

Basically just "create" a virtual key and join according to that:

select s.studio_id, s.studio_name, ss.person_name, a.album_name
from Studio s left join 
     (select st.*, row_number() over (order by staff_id) as seqnum
      from Staff st
     ) ss on s.studio_id = st.studio_id
     full outer join
     (select al.*, row_number() over (order by album_id) as seqnum
      from Album al
     ) a
     on ss.seqnum = a.seqnum;
where s.studio_id = 1;

display different table data in one row

2 Answers2