Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Query details required

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
kanisha_prabha

New User


Joined: 10 Mar 2006
Posts: 26

PostPosted: Thu Jul 19, 2012 9:49 am    Post subject: Query details required
Reply with quote

I have tables city1, city2 and city3 each of which have account no. and ssn. 1 account no. can have multiple ssns and can span across the 3 tables. eg. account - 00101 can have 2 ssns in city1, 1 ssn in city2 and 2 ssns in city3.

I have a separate employee table that have a list of ssn #s.

I need to find all accounts from city1, city2, city3 (combined) that do not have any ssn matching the employee table.

Please provide me with the query details.
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Thu Jul 19, 2012 12:34 pm    Post subject:
Reply with quote

You DO understand that we do NOT provide you with the query. we assist you to correct what you have done wrong - if you have. So, what have you tried so far with what results?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jul 19, 2012 8:57 pm    Post subject:
Reply with quote

Hello,

Quote:
Please provide me with the query details.
As Nic mentioned - we are a Help forum, not a do-my-work-for-me forum.

It is our intent to help people learn rather than grab something they don't understand and run it on their system.

Once you post what you have tried and where you are stuck, someone should be able to help.
Back to top
View user's profile Send private message
kanisha_prabha

New User


Joined: 10 Mar 2006
Posts: 26

PostPosted: Tue Aug 21, 2012 8:55 pm    Post subject:
Reply with quote

Concentrate on the question - not on the decorum of the forum. If I havent tried anything I wouldnt have come here in the first place.

From the question itself it should be clear that I intend to understand how to write a particular type of query.

I DO NOT INTEND TO COME HERE TO LEARN SYNTAX. Given the details I provided - if you cannot provide me with how you would do it - STAY AWAY.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Tue Aug 21, 2012 9:10 pm    Post subject: Reply to: Query details required
Reply with quote

Quote:
Concentrate on the question - not on the decorum of the forum.


You are not in the position to tell people what to do.

Quote:
if you cannot provide me with how you would do it - STAY AWAY.


wiser to use more respectful ways ...
we reply on our own time and free of charge ...

Your bad manners make difficult to concentrate on the question
so the chances of getting help are very slim icon_cool.gif
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Tue Aug 21, 2012 9:12 pm    Post subject: Reply to: Query details required
Reply with quote

When you run your own forum, then you can dictate.

Until then, if you have a question which you want assistance with, it would be wise to provide all information requested and answer all questions asked.

No-one has suggested you came here to learn syntax, or anything else. You came here for an easy answer to your question. We prefer you to learn.

Now, you can stop digging yourself into a hole and come up with what has been asked of you, or you can get nowhere.

Your choice.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Aug 21, 2012 9:19 pm    Post subject:
Reply with quote

First union all 3 cities together in one table
than see if for each account there is at least one row in that table with an Emp with a corresponding ssn.

this should work :
Code:
with cte (Acc,ssn)  as
(select acc,ssn from City1 union
 select acc,ssn from City2 union
 select acc,ssn from City3 )

select Acc from cte X1
where not exists (select Emp from cte X2, Employee E
      where X2.acc = X1.acc and X2.ssn = e.ssn)
group by acc


Not to mention that having 3 seperate tables with the same data is not a sign of good modeling
Back to top
View user's profile Send private message
kanisha_prabha

New User


Joined: 10 Mar 2006
Posts: 26

PostPosted: Wed Aug 22, 2012 9:20 am    Post subject:
Reply with quote

Thanks. I am trying this first thing in the morning tomorrow.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts hot to get details when "EXEC CI... Andi1982 CICS 11 Tue Sep 20, 2016 5:01 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us