Simple SQL query issue

Question

I have a table called Languages which stores the languages every person stored in Persons table is able to talk, then I have a table called Homework which stores all the homework every person has to do, but related to a language. I'm able to select the homework person 1 has to do for language 1 (Left join), but also would like to be able to select in the same query the language person 1 is able to talk. Could someone give me a hint how to accomplish that? A left join to Languages will screw the results as will mess with languages on Homework table.

Languages and Persons are Many2One, the same goes for Homework and Persons.

That works but gets screw when I add a second left join:

SELECT p.id, l.id, h.id
FROM Persons AS p
LEFT JOIN Homework AS h ON h.person_id = p.id
#LEFT JOIN Languages AS l ON l.person_id = p.id #Screws the result multipliying by the number of available languages
WHERE p.id = 1
AND h.language_id = 1

I would like to combine the above query and the following:

 SELECT *
 FROM languages
 WHERE person_id=1

Can you please describe in more detail, how does a second join mess with your result? An example of what you get and what you would like to get would be nice. — socha23
– socha23, Commented Nov 3, 2011 at 11:16
Could you try and outline the table you would like to end up with — fluent
– fluent, Commented Nov 3, 2011 at 11:25
Hi, thx a lot for the answer. I can't provide you results as that's a simplified problem i have from a more complex one. The thing is, if you have 3 languages in Languages table, then the last left join will make the results be 3 times "big". — peris
– peris, Commented Nov 3, 2011 at 11:31

fluent · Accepted Answer · 2011-11-03 14:47:18Z

1

Are you trying to find?

Person | Language | Homework 
------------------------------
Bob | English | 
Bob | French | 
Bob | Spanish | Geography

James | English |
James | French | History
James | Spanish |

Using this...

SELECT p.id, l.id, h.id
FROM Persons AS p

LEFT JOIN Languages AS l ON l.person_id = p.id 

LEFT JOIN Homework AS h ON h.language_id = l.id
    And h.person_id = p.id

Or

Person | Language | Homework 
------------------------------
Bob | Spanish | Geography
James | French | History

Which I believe is what has already been suggested

UPDATE : This should now give you the person with all the languages but only show homeworks matching the language you want

SELECT p.id, l.id, h.id
FROM Persons AS p

LEFT JOIN Languages AS l ON l.person_id = p.id 

LEFT JOIN Homework AS h ON h.language_id = l.id
    And h.person_id = p.id
    And h.language_id = 1

edited Nov 3, 2011 at 14:47

answered Nov 3, 2011 at 11:39

fluent

2,4034 gold badges24 silver badges33 bronze badges

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

2 Comments

peris Over a year ago

That query will work fine, but i also would like to append something like: AND h.language_id = X so it shows only the homework belonging to language = x but also will show me the available languages linked to a person. Thx a lot :)

fluent Over a year ago

Additional criteria added that should limit the homeworks returned based on languages.

Collectives™ on Stack Overflow

Simple SQL query issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related