Monday, March 26, 2012

Outer join problem

Hi.
I mean left outer join must return all rows in the left table.
Why?
SELECT DISTINCT a.cliente, a.Expositor
FROM prm_Exp_x_PV a
LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_expv
LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
WHERE (a.cliente = '4306500007')
Returns 22 Rows. and
SELECT DISTINCT a.cliente, a.Expositor
FROM prm_Exp_x_PV a
LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_expv
LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
WHERE (a.cliente = '4306500007')
and d.grupo is null
Returns only 9 rows
I need a query that returns all rows in the prm_Exp_x_PV table and only the
rows with null grupo in the prm_paneles_x_pv.
Table a and b have a master detail relation with cero or more rows in the
detail table.
Any help?
Thanks.
Pau.Having not seen your data (and DDL for that matter) I cannot see anything
unexpected in those queries.
And if you want rows where prm_paneles_x_pv.grupo is null then use the
appropriate alias - c instead of d.
For better help post DDL and sample data.
ML|||You keep mentioning table "b". Where is it? It's not referenced by any query
.
I don't know what happened to the DDL/sample data, but I can't see it.
Please read more on this here:
http://www.aspfaq.com/etiquette.asp?id=5006
ML|||Pau, if I'm correctly guessing what you want, you need to apply the
condition in the join condition rather than the where clause ...
...
FROM
a
LEFT JOIN b ON a.x = b.x AND b.y IS NULL
...
"Pau Dom_nguez" wrote:

> Ok. here it is the DDL and data.
> The field grupo is a field of table d used to filter the matching rows in
> table b.
> I need a query that returns all rows in the prm_Exp_x_PV table and only th
e
> rows from the prm_paneles_x_pv that have null grupo in prompaneles.
> Table a and b have a master detail relation with cero or more rows in the
> detail table.
> SELECT DISTINCT a.cliente, a.Expositor
> FROM prm_Exp_x_PV a
> LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_ex
pv
> LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
> WHERE a.cliente = '4306500007'
> and d.grupo is null
> What is wrong in this select?
> Thanks ML.
> Pau.
> "ML" <ML@.discussions.microsoft.com> escribió en el mensaje
> news:54B5E0ED-DD74-4A75-B4BD-F95FE6915DF5@.microsoft.com...
>
>|||Thanks you very much KH.
Now it works fine.
Pau.
"KH" <KH@.discussions.microsoft.com> escribi en el mensaje
news:3989ADA9-5111-4927-AD1A-64192018DCC9@.microsoft.com...
> Pau, if I'm correctly guessing what you want, you need to apply the
> condition in the join condition rather than the where clause ...
> ...
> FROM
> a
> LEFT JOIN b ON a.x = b.x AND b.y IS NULL
> ...
>
>
> "Pau Domnguez" wrote:
>|||Thank you for nothing ML.
You are burned, this work is not for you.
Pau.
"ML" <ML@.discussions.microsoft.com> escribi en el mensaje
news:93A4AF26-5094-4C10-863D-AC5C7E39A597@.microsoft.com...
> You keep mentioning table "b". Where is it? It's not referenced by any
> query.
> I don't know what happened to the DDL/sample data, but I can't see it.
> Please read more on this here:
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> ML

No comments:

Post a Comment