Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Unmatched Query 1

Status
Not open for further replies.

megmogg

Programmer
Jul 14, 2005
76
GB
Hi All

I have a query question I cannot get my head around.

The Tables are
Client Table joins to Mapping_Client Table
Mapping_Client table joins to Mapping_Codes Table
Mapping_Codes table joins to Codes table.

This provides client codes mapped to internal codes. Then we can provide the client with their own codes without having to change ours.

Key fields across the 3 tables are
Clientid
Mapping_client_id
Mapping_codes_id
Code_id

I want to be able to list all the Codes that have not been mapped to a Client. Sounds simple but I cannot get it.

Thanks


 
So does ClientID = Mapping_client_id?
does Mapping_client_id = Mapping_codes_id?
does Mapping_codes_id = Code_id?

If not, what are the relationships between each table? How do you link them?

-SQLBill

Posting advice: FAQ481-4875
 
Sorry, should have provided more info.

SQL 2000



Client Table
ClientID (primary)
ClientName

Mapping_Client Table
Mapping_ClientID (primary)
ClientID

Mapping_Code Table
Mapping_Codes_ID (primary)
Mapping_Client_ID
Code_ID

Codes Table
CodeID (primary)
CodeDesc


 
something like this??
FYI: I have not tested this...

Code:
Select * 
From Codes
Where NOT EXISTS ( Select CodeID
                   From Codes c
                   inner join mapping _code mc ON c.code_d = mc.mapping_codesID
                   inner join mapping_client mcl ON mc.mapping_code_id = mcl.mapping_client_id
                   inner join clients cl ON 
mcl.mapping_clien_id on cl.clientid )
 


Sorry, didn't work.

Looking at the query, it is similar to what I had.

In there is does need:
mapping_client.clientid=10

as it only should be checking for unmatched for client 10.



 
You did not mention criteria of client id in your previous post, so atleast I am confused about your requirement now.

Also you mentioned that the query that jbenson001 suggested did not work? was the reason just this additional clause? or did it give you any additional errors?

Regards,
AA

 

Thanks for the reply.

Sorry, yes, it is a little difficult to explain, but I do need the clientid check.

The query jbenson001 wrote just did not bring anything back when there was one unmapped.
But if there are none mapped, then it brings everything back.






 


Just tried jbenson001's query, but used NOT IN instead of exists and it worked!

I'll give jbenson a star for pointing me in the correct direction. Onto the next post! [noevil]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top