|
View previous topic :: View next topic
|
| Author |
Message |
kanisha_prabha
New User
Joined: 10 Mar 2006
Posts: 26
|
|
|
|
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 |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
kanisha_prabha
New User
Joined: 10 Mar 2006
Posts: 26
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10872
Location: italy
|
|
|
|
| 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  |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
kanisha_prabha
New User
Joined: 10 Mar 2006
Posts: 26
|
|
|
|
| Thanks. I am trying this first thing in the morning tomorrow. |
|
| Back to top |
|
|
|
|
