Home » SQL & PL/SQL » SQL & PL/SQL » Help in outer join / not exists (merged)
Help in outer join / not exists (merged) [message #216112] Thu, 25 January 2007 01:15 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi Experts,

My sql is this

SELECT DISTINCT c.code "MODEL",
decode(COUNT(a.id),0,'N','Y') "IN STOCK"
FROM CCR a,CSM b,CDM c,CNLH d
WHERE a.ID = d.card_id
AND a.subm_id=b.ID
AND b.dchm_id=c.ID
and substr(C.code,1,3) in ('TFS','TFR','NHR','NPR','COM','VIV')
GROUP BY c.code;

My current output like this
-----------------
Model | In Stock
-----------------
ABC | Y
CDF | Y
XYZ | Y

Since there are other models also which don't have stock I want the output like this

-----------------
Model | In Stock
-----------------
ABC | Y
CDF | Y
XYZ | Y
LMN | N
DEF | N
UVW | Y
RSD | N

Can anyone help me from the above sql to modity and get me the 2nd requested output I have mentioned.

Re: Please kindly help in this SQL [message #216116 is a reply to message #216112] Thu, 25 January 2007 01:24 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would OUTER JOIN help?
Re: Please kindly help in this SQL [message #216118 is a reply to message #216112] Thu, 25 January 2007 01:27 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I'm confused....
How can you get the output as
-----------------
Model | In Stock
-----------------
ABC | Y
CDF | Y
XYZ | Y
Quote:
and substr(C.code,1,3) in ('TFS','TFR','NHR','NPR','COM','VIV')


If that is a sample output and not relevant to that query....
try outer join.

By
Vamsi
Help in OUTER JOIN / NOT EXISTS [message #216265 is a reply to message #216112] Thu, 25 January 2007 20:55 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi Experts,

I have the following query

SELECT DISTINCT c.code "MODEL",d.DESC "COLOUR",
decode(COUNT(a.id),0,'N','Y') "IN STOCK"
FROM CCR a,CSM b,CDM c,CMC d
WHERE exists (select null from CNLH d where a.id=d.card_id)
AND a.sid=b.ID AND b.did=c.ID AND a.MCO=d.COD and
a.id not in (select cid from CCR) AND
substr(C.cod,1,3) in ('ABC','DEF','GHI','JKL','MNO'')
GROUP BY c.cod,d.desc ORDER BY 1,2,3;

My current output is like this

MODEL|COLOUR|IN STOCK
ABC |WHITE |Y
ABC |BLUE |Y
DEF |BLUE |Y

I am unable to get the other models GHI,JKL,MNO which has no stock and I want the output like this

MODEL|COLOUR|IN STOCK
ABC |WHITE |Y
ABC |BLUE |Y
DEF |BLUE |Y
GHI |WHITE |N
GHI |BLUE |N
GHI |YELLOW|N
JKL |BLUE |N
JKL |WHILE |N
MNO |YELLOW|N
MNO |WHILE |N

Can anyone help me to get the required output for me? Thanks in advance.



Re: Help in OUTER JOIN / NOT EXISTS [message #216275 is a reply to message #216265] Fri, 26 January 2007 00:55 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi experts,

I tried using + also the answer is same?
Re: Help in OUTER JOIN / NOT EXISTS [message #216279 is a reply to message #216275] Fri, 26 January 2007 01:44 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi Experts,

I got it thank you for your help
Re: Help in OUTER JOIN / NOT EXISTS [message #216283 is a reply to message #216279] Fri, 26 January 2007 02:05 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm glad you did! However, it would be nice to post the solution so that other people could learn something from it.
Re: Please kindly help in this SQL [message #216311 is a reply to message #216118] Fri, 26 January 2007 06:23 Go to previous messageGo to next message
pptenga
Messages: 1
Registered: February 2006
Junior Member

use right join
Re: Please kindly help in this SQL [message #216481 is a reply to message #216311] Sun, 28 January 2007 19:13 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi,

The solution for the problem I did was like this

SELECT DISTINCT c.code "MODEL",d.DESC "COLOUR",
decode(COUNT(d.card_id),0,'N','Y') "IN STOCK"
FROM CCR a,CSM b,CDM c,CMC d
WHERE a.id=d.card_id(+)
AND a.sid=b.ID AND b.did=c.ID AND a.MCO=d.COD and
a.id not in (select cid from CCR) AND
substr(C.cod,1,3) in ('ABC','DEF','GHI','JKL','MNO'')
GROUP BY c.cod,d.desc ORDER BY 1,2,3;
Re: Please kindly help in this SQL [message #216517 is a reply to message #216481] Mon, 29 January 2007 00:52 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you for sharing the solution!
Previous Topic: Collections
Next Topic: SP2-0611: Error enabling STATISTICS report
Goto Forum:
  


Current Time: Wed Dec 07 22:30:25 CST 2016

Total time taken to generate the page: 0.14251 seconds