Requete SQL UNION

Version imprimable

Requete SQL UNION

Bonjour
j'essaye de récupérer des valeurs pour une la date X et pour la date Y je crois que pour cela je suis obligé d'utiliser l'UNION

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
(SELECT kw.kw , kw.id_kw , kw.url_destination , rank_rapport.position AS col1
							FROM prestation
							JOIN kw ON kw.id_prestation = prestation.id_prestation
							JOIN rank_conf ON rank_conf.id_prestation = prestation.id_prestation
							JOIN rank_rapport ON rank_rapport.id_kw = kw.id_kw 
							WHERE prestation.id_prestation = '185'
							AND rank_rapport.date_position = '2012-07-25')
UNION ALL
(SELECT kw.kw , kw.id_kw , kw.url_destination , rank_rapport.position AS col2
							FROM prestation
							JOIN kw ON kw.id_prestation = prestation.id_prestation
							JOIN rank_conf ON rank_conf.id_prestation = prestation.id_prestation
							JOIN rank_rapport ON rank_rapport.id_kw = kw.id_kw 
							WHERE prestation.id_prestation = '185'
							AND rank_rapport.date_position = '2012-07-26')

sauf que col2 ne s'affiche pas et je sais pas pourquoi :s

Ton union est équivalente à cette requete:

Code:

1
2
3
4
5
6
7
8
SELECT kw.kw , kw.id_kw , kw.url_destination , rank_rapport.position AS col1
							FROM prestation
							JOIN kw ON kw.id_prestation = prestation.id_prestation
							JOIN rank_conf ON rank_conf.id_prestation = prestation.id_prestation
							JOIN rank_rapport ON rank_rapport.id_kw = kw.id_kw 
							WHERE prestation.id_prestation = '185'
							AND (rank_rapport.date_position = '2012-07-25'
							  OR rank_rapport.date_position = '2012-07-26')

Tu peux faire comme ça, mais c'est pas terrible :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
(SELECT kw.kw , kw.id_kw , kw.url_destination , rank_rapport.position AS col1,null AS col2
							FROM prestation
							JOIN kw ON kw.id_prestation = prestation.id_prestation
							JOIN rank_conf ON rank_conf.id_prestation = prestation.id_prestation
							JOIN rank_rapport ON rank_rapport.id_kw = kw.id_kw 
							WHERE prestation.id_prestation = '185'
							AND rank_rapport.date_position = '2012-07-25')
UNION ALL
(SELECT kw.kw , kw.id_kw , kw.url_destination , null AS col1, rank_rapport.position AS col2
							FROM prestation
							JOIN kw ON kw.id_prestation = prestation.id_prestation
							JOIN rank_conf ON rank_conf.id_prestation = prestation.id_prestation
							JOIN rank_rapport ON rank_rapport.id_kw = kw.id_kw 
							WHERE prestation.id_prestation = '185'
							AND rank_rapport.date_position = '2012-07-26')

Ou comme ça :

Code:

1
2
3
4
5
6
7
8
9
SELECT kw.kw , kw.id_kw , kw.url_destination , CASE rank_rapport.date_position WHEN '2012-07-25' THEN rank_rapport.position ELSE null END  AS col1,
                                               CASE rank_rapport.date_position WHEN '2012-07-26' THEN rank_rapport.position ELSE null END  AS col2
							FROM prestation
							JOIN kw ON kw.id_prestation = prestation.id_prestation
							JOIN rank_conf ON rank_conf.id_prestation = prestation.id_prestation
							JOIN rank_rapport ON rank_rapport.id_kw = kw.id_kw 
							WHERE prestation.id_prestation = '185'
							AND (rank_rapport.date_position = '2012-07-25'
							  OR rank_rapport.date_position = '2012-07-26')

27/07/2012, 11h00
misakilou

Il n'est pas possible d'avoir rank_rapport.position pour la date X et rank_rapport.position pour la date Y en parallèle ?

Je ne sais pas si ça va fonctionner et si c'est la bonne façon de faire, mais tu peux toujours essayer ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT t1.kw,t1.id_kw,t1.url_destination,col1,col2 FROM (
(SELECT kw.kw , kw.id_kw , kw.url_destination , rank_rapport.position AS col1
							FROM prestation
							JOIN kw ON kw.id_prestation = prestation.id_prestation
							JOIN rank_conf ON rank_conf.id_prestation = prestation.id_prestation
							JOIN rank_rapport ON rank_rapport.id_kw = kw.id_kw 
							WHERE prestation.id_prestation = '185'
							AND rank_rapport.date_position = '2012-07-25') t1
JOIN 
(SELECT kw.kw , kw.id_kw , kw.url_destination , rank_rapport.position AS col2
							FROM prestation
							JOIN kw ON kw.id_prestation = prestation.id_prestation
							JOIN rank_conf ON rank_conf.id_prestation = prestation.id_prestation
							JOIN rank_rapport ON rank_rapport.id_kw = kw.id_kw 
							WHERE prestation.id_prestation = '185'
							AND rank_rapport.date_position = '2012-07-26') t2
ON t1.kw= t2.kw AND t1.id_kw=t2.id_kw AND t1.url_destination=t2.url_destination
)

27/07/2012, 11h44
misakilou

Ah bah c'est ce que je recherchais :) , je vais vraiment me plonger dans le langage SQL ^^
Merci ! :)