Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1894
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: 10327
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: 7315

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: 1281
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
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Historical/statistical details for me... elixir1986 TSO/ISPF 13 Wed Aug 30, 2017 9:02 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us