Nested select statement with MySql ...

[erkan30]

This select statement has an error .. what do i need to do for running this ? thx
i needed the nested statement to get NULL values too


select distinct
x.produkt_id,
y.farbe_text
from
(select distinct
a.produkt_id,
c.ausfuehrung_farbe_id,
c.ausfuehrung_groesse_id
from webshop_produkte a,
webshop_produkte_ausfuehrung_typ b,
webshop_produkte_ausfuehrung c
where a.ausfuehrung_typ_id = b.ausfuehrung_typ_id
and b.ausfuehrung_id = c.ausfuehrung_id) x,
webshop_produkte_farbe y
where x.ausfuehrung_farbe_id = y.farbe_id

 

[TonyGroves]

What sort of error is happening? What field do you need null values for?

 

[erkan30]

i am not quite sure but maybe my hosted mysql doesnt support nested joins. I got Error Code : 1064
"You have an error in your SQL syntax. Check the manual.."

the NULL values which i want too are from the fields c.ausfuehrung_farbe_id and c.ausfuehrung_groesse_id ..
the main idea is to get the text from table y (later also from z)
sometimes are both fields with values and sometimes only farbe or groesse..

i.e. table x table y
xID Farbe_ID Groesse yID Farbe
----------------------- -------------
10 1 99.99 1 white
20 2 NULL 2 red
30 NULL 99.99
40 NULL NULL

the result should like this:
xID Farbe
---------------
10 white 99.99
20 red NULL
30 NULL 99.99
40 NULL NULL

maybe it should possible to reorder the select statement but i am not really the best in sql..

thx
erkan

 

[r937]

sounds like a job for LEFT OUTER JOIN


why do you use DISTINCT in the subquery for x?

do you have the same product in the same size and colours more than once??


also, why select size in the subquery if you're just going to ignore it in the outer query?



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[erkan30]

i forged the word "ignore".. anyway, your answer sounds to be the solution.

thx a lot
erkan