CODEWITHSUNDEEP
mysqlsql
starvator
starvator

Reputation: 1017

Multiple sql joins from same reference table

I currently have the following query:

select  title,location,categorie,division, 
    reference.description as CA 
from emploi 
left join reference on emploi.categorie = reference.code_ref 
where id_type = 'CA' and emploi.status ='1'

and I have a second query, which is the same but is taking a different value from the reference table:

select  title,location,categorie,division, 
    reference.description as DI 
from emploi 
left join reference on emploi.division = reference.code_ref 
where id_type = 'DI' and emploi.status ='1'

Is there a way to execute both these queries?

I was thinking it was something along the lines of the following, but it clearly does not work:

select  title,location,categorie,division, 
    reference.description as DI, reference.description as CA 
from emploi 
(left join reference on emploi.division = reference.code_ref 
    where id_type = 'DI')
(left join reference on emploi.categorie = reference.code_ref 
    where id_type = 'CA')
where emploi.status ='1'

Thank you!

NOTE: Each entry from the emploi table has a division and categorie column, which need to both be mapped to separate entries ON THE SAME ROW

EDIT:
This is the working solution, for those interested:

select  title,location,categorie,division, 
    r.description as DI, r1.description as CA 
from emploi 
left join reference r on emploi.division = r.code_ref 
    and r.id_type = 'DI'
left join reference r1 on emploi.categorie = r1.code_ref 
    and r1.id_type = 'CA'
where emploi.status ='1'

Upvotes: 0

Views: 752

Answers (4)

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You may try this:

select  title,location,categorie,division, 
    r.description as DI, r1.description as CA 
from emploi 
left join reference r on emploi.division = r.code_ref 
left join reference r1 on emploi.categorie = r1.code_ref 
where emploi.status ='1'
and id_type in ('CA', 'DI')

Presently you are having an incorrect syntax as you are using the where clause two times with your left join. Also put the alias to your tables and use those alias to get the column name

Something like

select  tablename.title,tablename.location,tablename.categorie,tablename.division, 
    r.description as DI, r1.description as CA 
from emploi 
left join reference r on emploi.division = r.code_ref 
left join reference r1 on emploi.categorie = r1.code_ref 
where emploi.status ='1'
and id_type in ('CA', 'DI')

Note: Use the correct table name in place of tablename above

EDIT:-

If you dont want to use the IN clause then

select  title,location,categorie,division, 
    r.description as DI, r1.description as CA 
from emploi 
left join reference r on emploi.division = r.code_ref 
    and r.id_type = 'DI'
left join reference r1 on emploi.categorie = r1.code_ref 
    and r1.id_type = 'CA'
where emploi.status ='1'

Assuming that id_type is a column of emploi

Upvotes: 1

RSCode
RSCode

Reputation: 1593

select  title,location,categorie,division, 
    reference.description as CA 
from emploi 
left join reference  reference on emploi.categorie = reference.code_ref 
left join reference reference1 on emploi.division = reference1.code_ref
where id_type in('CA','DI') and emploi.status ='1' order by id_type

this will work for you , here order by is used to define the order of select statement

Upvotes: 0

1010
1010

Reputation: 1848

it should work without the brackets ( ), aliasing the tables and prefixing the fields with alias.

Upvotes: 0

Alex Szabo
Alex Szabo

Reputation: 3266

There are a couple of ways of achieving this:

select  title,location,categorie,division, 
reference.description as DI 
from emploi 
left join reference on emploi.division = reference.code_ref 
where id_type IN ('DI','CA') and emploi.status ='1'

or you can change the where clause to an OR

where (id_type = 'DI' OR id_type = 'CA') and emploi.status ='1'

or if you are looking forward to what you wrote originally then

select  title,location,categorie,division, 
reference.description as DI, reference.description as CA 
from emploi 
left join reference r1 on emploi.division = r1.code_ref 
and r1.id_type = 'DI'
left join reference r2 on emploi.categorie = r2.code_ref 
and r2.id_type = 'CA'

Or a couple of other ways also...

Upvotes: 1

Related Questions