NEWBIE - Joins
-
Hi
I'm a newbie to SQL and am having a problem with joins.
I have 3 tables, BDI, Client and Policy. In BDI I have a column that contains a clientref, which could be in one of 2 formats.
ABCD01 or ABCD01PC01
ABCD01 is a client reference, ABCD01PC01 is a policy reference.
In the client table there is a clientref column that contains the above reference ABCD01 but could also contain ABCD01PC01. This I can get the correct client_ID using an inner join:
inner join client as c on substring(bdi.polref,1,6) = c.clientref
I have to use substring to only get the 6 char reference.
In the policy table there is no clientref column, only a client_id and a policyref. For example
Policy_id PolicyRef Client_ID
1 PC01 1
My aim is to get client_ids for the ABCD01 references and the policy_ids for ABCD01PC01 references.
I think I have confused myself with this. If anyone can understand what I'm trying to do and can help it would be much appreciated.
-
better design would be you seperate them into 2 fields and connect seperately
However, this would do:
Select A.*, B.client_ID from BDI A, Client B where substring(A.clientref,1,6) = B.clientref and LEN(A.clientref)=6
Select A.*, B.policy_ID from BDI A, policy B where substring(A.clientref,7,4) = B.policyref and LEN(A.clientref)>6
of couse this is not the best practice
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
I too, would suggest that you might want to restructure the data. You are mixing 2 discrete things into one column. WHy not have a column for client and another for policy?
your current structure relies on brute force (i.e. substrings) to do the work. very ugly to query this way eespecially if your database grows to any size. getting all records for a specific client would be a large hit. etc.etc.etc
ymmv
Tal McMahon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply