Unmatched Query 1

[megmogg]

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

 

[SQLBill]

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

 

[megmogg]

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

 

[jbenson001]

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

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 )

 

[megmogg]

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.

 

[megmogg]

anyone else any ideas?

 

[amrita418]

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

 

[megmogg]

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.

 

[megmogg]

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!

 

[jbenson001]

Glad it is working..