joining 4 tables together

Refresh

March 2019

Views

61 time

1

I wonder if anyone could help me.

I have 4 tables that need to be joined to retrieve the data i need.

reviews

themes

reviewthemes

reviewsubthemes

So i want to do a simple select * from reviews. With this i require the theme name from themes and the subtheme name from themes.

A review can have more than one theme and subtheme. Also a review may only have a theme with no subthemes and then another theme with subthemes.

themes table look like this

enter image description here

Both reviewthemes and reviewsubthemes look like this

enter image description here

reviews table looks like this:

enter image description here

I have tried but i am getting too confused.

So essentially the review table holds the reviews. The themes table is where the name of the themes are, and both reviewthemes and reviewsubthemes have a reviewID column to join with the reviews table and a themeID to joni to the themes table.

Does anyone know how to do this and also can you explain how the query is made.

Thanks

2 answers

1

You may need to do a left join using reviews as your left table and then match themes based on themeid from either one of the tables: reviewthemes or reviewsubthemes.

select r.*,
       t.name
from reviews r
left join reviewthemes rt on rt.reviewId = r.reviewId
left join reviewsubthemes rst on rst.reviewid = r.reviewid
left join themes t on (t.themeid = rt.themeid or t.themeid = rst.themeid)

You could also use UNION or UNION ALL to do this (preferable method):

select r.*,
       t.name
from reviews r
left join reviewthemes rt on rt.reviewId = r.reviewId
left join themes t on t.themeid = rt.themeid 

union all

select r.*,
       t.name
from reviews r
left join reviewsubthemes rst on rst.reviewid = r.reviewid
left join themes t on t.themeid = rst.themeid

UPDATE:

Based on your comments, here is the answer:

select r.*,
       GROUP_CONCAT(t.name) as Name
from reviews r
left join reviewthemes rt on rt.reviewId = r.reviewId
left join reviewsubthemes rst on rst.reviewid = r.reviewid
left join themes t on (t.themeid = rt.themeid or t.themeid = rst.themeid)
group by r.*
1

Maybe I don't have the full picture but you might have too many tables, a themeID field in the Review table plus a theme/subtheme flag could have been enough. Anyway, try this:

select r.*, t.name
  from reviews as r
  left join reviewthemes as rt on r.themeID = rt.themeID
  left join reviewsubthemes as rst on r.themeID = rst.themeID
  left join themes as t on r.themeID = t.themeID

This should work if it's the themeID field has the ID you want and not the ID field. Add more fields to the select as you wish.