Re: Join three tables and specify criteria... I know this should be easy!

>> From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Paul Linehan

>> Sent: Friday, August 29, 2014 9:44 AM                                                                       

>> To: Chuck Roberts                                                                                          

>> Cc: pgsql-novice@postgresql.org                                                                            

>> Subject: [NOVICE] Join three tables and specify criteria... I know this should be easy!                    

>>                                                                                                            

>>                                                                                                             

>>                                                                                                            

>> Now, I want the user who speaks English and the German - I may need to specify 3, 4                        

>> or conceivably even 5 languages.                                                                           

>>                                                                                                            

>> I have done this, but I'm stuck :-)                                                                        

>> SELECT u.user_name, l.language_name                                                                        

>> FROM user u                                                                                                

>> JOIN user_language ul                                                                                       

>> ON u.user_id = ul.ul_user_id                                                                               

>> JOIN language l                                                                                             

>> ON ul.ul_iso_code = l.iso_code                                                                             

>>                                                                                                            

SELECT DISTINCT u.user_id

FROM user u

JOIN user_language ul

ON u.user_id = ul.ul_user_id

JOIN language l

ON ul.ul_iso_code = l.iso_code

WHERE u.user_language IN ('EN','DE')

(or)

WHERE "upper"(l.language_name) IN ('GERMAN','ENGLIGH')

See if this gives you what you want...

Ken Benson

>>                                                                                                            

>> this gives me                                                                                              

>>                                                                                                            

>> Sandor, German                                                                                             

>> Sandor, English                                                                                             

>> Gabor, English                                                                                             

>>                                                                                                             

>> I really want Sandor's id - that's all that really counts.                                                 

>>                                                                                                             

>> Give me the ids of all who speak 'EN' and 'DE' (or                                                         

>> possibly 'EN', 'DE', 'NL' and 'FR') for example.                                                           

>> TIA and rgs,                                                                                                

>>                                                                                                            

>> Paul...                                                                                                     

>>                                                                                                            

>>