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

Outer Join Problem

I am trying to gather two sets of data for a form. I want distinct values from the pricebook.productid and pricebook.productname to be displayed in two columns and the results of the cast statement in a third. problem is the cast statement may not contain all of the productid's or productnames from the first two columns but i still need them displayed with a zero in the third....

Can anyone steer me in the right direction?

SELECT pricebook.productid, pricebook.productname, CAST(ROUND(SUM(PRODQTY), 2) AS Decimal(10, 2))
FROM ticket left outer JOIN
pricebook ON TICKET.Plantid = pricebook.pricegroupid
WHERE (TICKET.RUNDATE = '2004-02-17') AND (pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
GROUP BY ticket.PRODID, pricebook.PRODuctid, pricebook.productname
ORDER BY pricebook.productidOriginally posted by hellhound
I am trying to gather two sets of data for a form. I want distinct values from the pricebook.productid and pricebook.productname to be displayed in two columns and the results of the cast statement in a third. problem is the cast statement may not contain all of the productid's or productnames from the first two columns but i still need them displayed with a zero in the third....

Can anyone steer me in the right direction?

SELECT pricebook.productid, pricebook.productname, CAST(ROUND(SUM(PRODQTY), 2) AS Decimal(10, 2))
FROM ticket left outer JOIN
pricebook ON TICKET.Plantid = pricebook.pricegroupid
WHERE (TICKET.RUNDATE = '2004-02-17') AND (pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
GROUP BY ticket.PRODID, pricebook.PRODuctid, pricebook.productname
ORDER BY pricebook.productid

Use ISNULL|||Could you elaborate? Something like this?

SELECT pricebook.productid, pricebook.productname,
(select distinct isnull(CAST(ROUND(SUM(PRODQTY), 2) AS Decimal(10, 2)), '0') as SoldTons
from ticket WHERE (RUNDATE = '2004-02-17') AND (plantid = '00045') AND (prodid != '888')
group by prodid)
FROM ticket full JOIN
pricebook ON TICKET.Plantid = pricebook.pricegroupid
WHERE (TICKET.RUNDATE = '2004-02-17') AND(pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
GROUP BY pricebook.PRODuctid, pricebook.productname
ORDER BY pricebook.productid

Originally posted by smasanam
Use ISNULL|||Select CAST(ISNULL(null, 0) AS Decimal(10, 2))

SELECT pricebook.productid, pricebook.productname,
CAST(ISNULL(ROUND(SUM(PRODQTY), 2), 0) AS Decimal(10, 2))
FROM ticket left outer JOIN
pricebook ON TICKET.Plantid = pricebook.pricegroupid
WHERE (TICKET.RUNDATE = '2004-02-17') AND (pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
GROUP BY ticket.PRODID, pricebook.PRODuctid, pricebook.productname
ORDER BY pricebook.productid|||I don't think I am being clear enough. Forgive me...

Basically i have data in two tables, one contains the prodid and name, the other contains the prodid and qty. i want one table with all the prodid's and names combined with the qty values from the other. Not all prodid's will have qty data, so i need those as '0'......

clear as mud?

thanks for your help.

Originally posted by r123456
Select CAST(ISNULL(null, 0) AS Decimal(10, 2))

SELECT pricebook.productid, pricebook.productname,
CAST(ISNULL(ROUND(SUM(PRODQTY), 2), 0) AS Decimal(10, 2))
FROM ticket left outer JOIN
pricebook ON TICKET.Plantid = pricebook.pricegroupid
WHERE (TICKET.RUNDATE = '2004-02-17') AND (pricebook.pricegroupid = '00045') AND (pricebook.Productid != '888')
GROUP BY ticket.PRODID, pricebook.PRODuctid, pricebook.productname
ORDER BY pricebook.productid|||Originally posted by hellhound
I don't think I am being clear enough. Forgive me...

Basically i have data in two tables, one contains the prodid and name, the other contains the prodid and qty. i want one table with all the prodid's and names combined with the qty values from the other. Not all prodid's will have qty data, so i need those as '0'......

clear as mud?

thanks for your help.
If you mean

Product Id Product Name, QTY
-----------
1 abc 100
2 efg 50
3 effff NULL

And QTY is in the 2nd Table then I think r123456's Query should work. If not

Can you please give more explanation|||Originally posted by smasanam
If you mean

Product Id Product Name, QTY
-----------
1 abc 100
2 efg 50
3 effff NULL

And QTY is in the 2nd Table then I think r123456's Query should work. If not

Can you please give more explanation

ok you are very close there, but when I run r123456's Query, i get multiple Productid and product name values.

ex.

Product Id Product Name, QTY
-----------
1 abc 100
1 abc 50
1 abc NULL
2 efg 100
2 efg 50
2 efg NULL
3 effff 100
3 effff 50
3 effff NULL|||Can you sum them up?

Or you need induvidual records??|||Can you please tell us the relationship between those 2 tables??|||Originally posted by smasanam
Can you please tell us the relationship between those 2 tables??

Here is an example of the table setup.

pricebook

productid productname
--------
abc abc product
def def product
efff efff product

Ticket

Prodid qty
----------
abc 20
abc 100
abc 50
def 150
def 200


Here is what I need from these;

Productid Productname QTY
--------------
abc abc product 170
def def product 350
efff efff product 0

Make any better sense?

i only need one distinct listing for the productid as determined by the where clause, and a sum for the qty... or 0 if it is not in the qty table.|||The join condition between the two tables is not clear. In your example, Pricebook.ProductID = 'abc abc', however Ticket.productID = 'abc'.

select pb.productID, sum(NVL(t.QTY, 0))
from priceBook pb
LEFT OUTER JOIN
ticket t ON
pb.productID = t.productID;
group by pb.productid;|||Originally posted by r123456
The join condition between the two tables is not clear. In your example, Pricebook.ProductID = 'abc abc', however Ticket.productID = 'abc'.

select pb.productID, sum(NVL(t.QTY, 0))
from priceBook pb
LEFT OUTER JOIN
ticket t ON
pb.productID = t.productID;
group by pb.productid;

now that i look back that was confusing;;;

tblpricebook

productid | productname
--------------------
abc | abc product
def | def product
efff | efff product

tblTicket

Prodid | qty
---------------------
abc | 20
abc | 100
abc | 50
def | 150
def | 200


Here is what I need from these;

Result

Productid | Productname | QTY
------------------------
abc | abc product | 170
def | def product | 350
efff | efff product | 0

Basically some productid appears multiple times with differing qty's in the ticket tbl. All productid's appear once in the pricebook table.
I need all the productid's from pricebook listed with appropriate sums from the ticket table and where there are no sums from the ticket table a null or zero int the result.

I really appreciate your patience on this.|||OK, this works...

select distinct pb.productid, isnull(sum(t.prodQTY), 0) as SOLDTONS
from priceBook pb left outer JOIN
ticket t ON
pb.productID = t.prodID
where pb.pricegroupid = '00045' /*and t.rundate = '2004-02-18'*/
group by pb.productid

only problem is if i narrow down the selection with the rundate in the where clause, I then limit my results to only what is contained in the ticket table....

Is Having my answer?|||move the condition on the t column into the ON clause for the left outer join

oh, and you do not need DISTINCT when you use GROUP BY, groups are distinct by definition:select pb.productid
, isnull(sum(t.prodQTY), 0) as SOLDTONS
from priceBook pb
left outer
join ticket t
on pb.productID = t.prodID
and t.rundate = '2004-02-18'
where pb.pricegroupid = '00045'
group
by pb.productid|||Originally posted by r937
move the condition on the t column into the ON clause for the left outer join

oh, and you do not need DISTINCT when you use GROUP BY, groups are distinct by definition:select pb.productid
, isnull(sum(t.prodQTY), 0) as SOLDTONS
from priceBook pb
left outer
join ticket t
on pb.productID = t.prodID
and t.rundate = '2004-02-18'
where pb.pricegroupid = '00045'
group
by pb.productid

EXCELLENT! Thanks for all your help!

mb

outer join oracle query translate in sql server

Hi,
I have this oracle query with outer join situation. how can i convert it into sql server query.

SELECT distinct ae.dB_CONTRACT,CP.PC_CODE,BID_ITEM.ITEM_NO,
'N',BID_ITEM.PRICE_WORDS,OFF_ITEM.DESCPT,
OFF_ITEM.UNITS,OFF_ITEM.TYPE_ITEM,
PRES_ITEM.RET_PERC
FROM BID_TOTAL,BID_ITEM,OFF_ITEM,PRES_ITEM, AE_CONTRACT AE, CONTRACT_PC CP
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
AND BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO (+)
AND AE.DB_CONTRACT=BID_TOTAL.DB_cONTRACT
AND CP.DB_CONTRACT = AE.DB_CONTRACT
AND CP.pc_code = 1

Any Help will be appreciated.I thhink this is it

SELECT DISTINCT
ae.dB_CONTRACT
, CP.PC_CODE
, BID_ITEM.ITEM_NO
, 'N'
, BID_ITEM.PRICE_WORDS
, OFF_ITEM.DESCPT
, OFF_ITEM.UNITS
, OFF_ITEM.TYPE_ITEM
, PRES_ITEM.RET_PERC
FROM BID_TOTAL
JOIN BID_ITEM
ON BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
JOIN OFF_ITEM
ON BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
LEFT JOIN PRES_ITEM
ON OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO
JOIN AE_CONTRACT AE
ON AE.DB_CONTRACT = BID_TOTAL.DB_cONTRACT
JOIN CONTRACT_PC CP
ON CP.DB_CONTRACT = AE.DB_CONTRACT
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND CP.pc_code = 1

Test it out...|||Hi,
thank you very much..it worked excellent

Originally posted by Brett Kaiser
I thhink this is it

SELECT DISTINCT
ae.dB_CONTRACT
, CP.PC_CODE
, BID_ITEM.ITEM_NO
, 'N'
, BID_ITEM.PRICE_WORDS
, OFF_ITEM.DESCPT
, OFF_ITEM.UNITS
, OFF_ITEM.TYPE_ITEM
, PRES_ITEM.RET_PERC
FROM BID_TOTAL
JOIN BID_ITEM
ON BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
JOIN OFF_ITEM
ON BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
LEFT JOIN PRES_ITEM
ON OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO
JOIN AE_CONTRACT AE
ON AE.DB_CONTRACT = BID_TOTAL.DB_cONTRACT
JOIN CONTRACT_PC CP
ON CP.DB_CONTRACT = AE.DB_CONTRACT
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND CP.pc_code = 1

Test it out...|||if your oracle install is 9i
the ansi join syntax actually works
after 20 years!!!!!