MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

SQL Query

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
coolman
Intermediate


Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
PostPosted: Thu May 06, 2004 9:31 am    Post subject: SQL Query Reply with quote
Hello,

I have 2 tables. Table X and Y.

Table X structure is as below with just 1 column.

Code:

Region_Code
========
1
2
3
4
5

Table Y structure is as below with 2 columns
Code:

State Code     Region_Code
=======        =========
A                 1
A                 2
A                 3
A                 5
B                 1
B                 5

Now, I want the output with the state codes and the missing region codes.

Expected Result
===========
Code:

State Code     Region_Code
=======        =========
A                 4
B                 2
B                 3
B                 4

Is there a way to accomplish this using a query?

Cheers,
Coolman.
________
marijuana strain green crack

Last edited by coolman on Sat Feb 05, 2011 1:36 am; edited 1 time in total
Back to top
View user's profile Send private message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany
PostPosted: Thu May 06, 2004 10:52 am    Post subject: Reply with quote
Hi,

in table Y: you meen StaeCode = A,B,C, ........
Region code = 1,2,3,4,5

Your exampleo looks like StateCode = A1, A2,..... and Region Code = Empty ?
Back to top
View user's profile Send private message
coolman
Intermediate


Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
PostPosted: Thu May 06, 2004 11:49 am    Post subject: Reply with quote
Bauer,
You are right. The state code is just A,B,C ....and Region Code=1,2,3,4...

I'm not sure I was not able to edit my post. Moderator, can you please help me out.

Cheers,
Coolman.
________
Alfredo Ferrari specifications

Last edited by coolman on Sat Feb 05, 2011 1:36 am; edited 1 time in total
Back to top
View user's profile Send private message
Himesh
CICS Forum Moderator
CICS Forum Moderator


Joined: 20 Dec 2002
Posts: 80
Topics: 21
Location: Chicago
PostPosted: Thu May 06, 2004 12:13 pm    Post subject: Reply with quote
Coolman,

If you want to select all the statecodes that do not have a corresponding region code in tablex, the following SQL should suffice:

Pls note: You need to provide more details while posting a topic. I have assumed that you are using DB2.

Basically, what the SQL query does is:

Create a superset of a combination of all Statecodes and distinct RegionCodes and search for this combination in a new resultant table formed by joining the two tables. All the records that do not match are the ones you are looking for.


Code:

SELECT substr(P.totalstring,2,1) statecode, substr(P.totalstring,1,1) regioncode
FROM
   (SELECT W.regioncode||E.statecode totalstring
   FROM
      (select regioncode from
           tablex ) W ,
      (select distinct statecode
           from tabley) E
   WHERE
   W.regioncode||E.statecode NOT IN
      (select R.regioncode||R.statecode
             from tabley R, tablex T
       where R.regioncode = T.regioncode)
   ) P
;


regards,
himesh
Back to top
View user's profile Send private message Yahoo Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12370
Topics: 75
Location: San Jose
PostPosted: Thu May 06, 2004 12:30 pm    Post subject: Reply with quote
Coolman,

A good query to test your sql skills. The following sql will give you desired results.

Code:

SELECT DISTINCT A.STATE_CODE,B.REGION_CODE                                   
 FROM TABLE_Y A                                                   
    ,TABLE_X B                                                   
 WHERE (A.STATE_CODE,B.REGION_CODE) NOT IN (SELECT STATE_CODE
                                                 ,REGION_CODE
                                              FROM TABLE_Y)
;


Hope this helps...

Cheers

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
coolman
Intermediate


Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
PostPosted: Thu May 06, 2004 3:28 pm    Post subject: Reply with quote
Thanks Himesh and Kolusu for your help.
________
silversurfer reviews

Last edited by coolman on Sat Feb 05, 2011 1:36 am; edited 1 time in total
Back to top
View user's profile Send private message
Himesh
CICS Forum Moderator
CICS Forum Moderator


Joined: 20 Dec 2002
Posts: 80
Topics: 21
Location: Chicago
PostPosted: Fri May 07, 2004 12:38 am    Post subject: Reply with quote
As Kolusu said, it is indeed a nice query (sort of an un-orthodox case, if i may say so).

Kolusu's solution is the ideal one. It shows that you need to have a good understanding of how DB2 would interpret SQL statements.

I had gone the roundabout way. (Maybe my long hours is taking its toll now Embarassed ).


regards,
himesh
Back to top
View user's profile Send private message Yahoo Messenger
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group