CODEWITHSUNDEEP
mysqlsqljoin
Kuubs
Kuubs

Reputation: 1340

Joining and combining multiple tables MYSQL

Got a problem with combining data with MySQL. I have 4 tables which are relationally linked to eachother. 

exhibitions
-id
-name
-location
-form_id

form
-id
-name

form_trans
-id
-form_id
-lang_id
-content

languages
-id
-name
-code

As you can notice, I link the ID's to eachother so I can make changes to any of the tables without doing things doubly. This was my query at firsthand:

SELECT a.id, a.name, b.name, c.name 
FROM `form` a, `exhibitions` b, `languages` c, `form_trans` d 
WHERE a.id = b.form_id 
AND d.form_id = a.id 
AND d.lang_id = c.id

Problem with this query is that it's not taking the forms which aren't linked to any of the exhibitions. To do that, I presume to use a JOIN statement, but I cannot wrap my head around the statement I have to use. I have this but it isn't even a valid query...

SELECT a.id, a.name
FROM `forms` a
OUTER JOIN `exhibitions` b ON a.id = b.form_id

Sorry I didn't describe what I wanted to achieve. I want to select all the forms and I want to match the Exhibition (I can match the form_id with the id of the form table) and I want to match the Language in the Language table with the form ( I do that by linking the form_trans table with the form table by using id and form_id and link the language table with the form_trans table by using the lang_id <> id relation)

Upvotes: 1

Views: 53

Answers (2)

javabot
javabot

Reputation: 171

Try this, LEFT JOIN lists the entries that do not have corresponding associations with the table on the right. So in our case I guess the query would go like,

SELECT a.id, a.name, b.name, c.name FROM `form` a LEFT JOIN 
`exhibitions` b INNER JOIN `languages` c INNER JOIN `form_trans` d
 WHERE a.id = b.form_id AND d.form_id = a.id AND d.lang_id = c.id;

Haven't tested the query but this should work.

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

This query should select all the forms even if they don't have exhibitions or translations:

SELECT f.id As FormId
     , f.name as FormName
     , e.name As exhibitionName
     , l.name As LanguageName
FROM `form` f
LEFT JOIN  `exhibitions` e ON(f.id = e.form_id )
LEFT JOIN `form_trans` ft ON(ft.form_id = f.id )
LEFT JOIN `languages` l ON(ft.lang_id = l.id)

Your mistakes:

  • Using implicit joins instead of explicit ones.
  • Using arbitrary aliases instead of meaningful ones.

Upvotes: 1

Related Questions