April 15, 2011 at 9:59 am
:ermm:
I just cant figure out this. I have 3 tables as such
Table A
Name Gender City
Jo M St
Lina F Pk
Sandy F Li
**Column name is PK
Table D
Name Type
Jo A
Lina B
Susan C
Table Type
ID In_Type Out_Type
1 A,M,N,J Primary
2 Z,B,D Secondary
3 P,Q,C,X Third
**Column ID is PK
I know the above data might not make sense, but I cant disclose the original data but the data structure is the same as the original.
Here is what I need to do, get the Name,Type and Out_Type information by joining all 3 tables
I have tried the following's but they dont seem to work.I know Im missing something.
SELECT a.NAME,d.[Type],t.[Out_Type] FROM A a
INNER JOIN D d
ON a.Name=d.Name
INNER JOIN [TYPE]t
ON d.[Type] IN ( SELECT t.[Out_Type]FROM [Type])
SELECT a.NAME,d.[Type],
CASE
WHEN d.[Type] IN (t.[In_Type]) AND t.[Out_Type]='Primary' THEN 'Primary'
WHEN d.[Type] IN (t.[In_Type]) AND t.[Out_Type]='Secondary' THEN 'Secondary'
WHEN d.[Type] IN (t.[In_Type]) AND t.[Out_Type]='Third' THEN 'Third'
ELSE 'No Support' END AS [Out_Type]
FROM A a INNER JOIN D d
ON a.Name=d.Name
INNER JOIN [TYPE]t
ON d.[Type] IN ( SELECT t.[Out_Type]FROM [Type])
SELECT a.NAME,d.[Type],
CASE
WHEN d.[Type] IN (t.[In_Type]) AND t.[Out_Type]='Primary' THEN 'Primary'
WHEN d.[Type] IN (t.[In_Type]) AND t.[Out_Type]='Secondary' THEN 'Secondary'
WHEN d.[Type] IN (t.[In_Type]) AND t.[Out_Type]='Third' THEN 'Third'
ELSE 'No Support' END AS [Out_Type]
FROM A a INNER JOIN D d
ON a.Name=d.Name
INNER JOIN [TYPE]t
ON d.[Type] LIKE ('%' + t.[Out_Type] + '%')
All above Queries Do not return anything
My output should look like this
Name Type Out_0Type
Jo A Primary
Lina B Secondary
April 15, 2011 at 10:24 am
SELECT a.NAME,d.[Type],t.[Out_Type] FROM A a
INNER JOIN D d
ON a.Name=d.Name
INNER JOIN [TYPE]t
ON d.[Type] IN ( SELECT t.[Out_Type]FROM [Type])
In you Query How does the Type table join to Table A. You Join the Type table to with a in statement but there is no join back to the primary Table A. you could ust the in as and AND extention after you extablish the relationship to Table A.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 15, 2011 at 10:40 am
Hi Dan, Thanks for your prompt reply.Could you show how this can be done ?
April 15, 2011 at 10:46 am
The way the type table is defined currently it would be difficult do you have any control over the desing of that table? Rather than have the type stored in one field as A,B,C Each type should be a seperate row. Having them combined in one field make life difficult.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 15, 2011 at 10:56 am
I got it to work
SELECT a.NAME,d.[Type],t.[Out_Type] FROM A a
INNER JOIN D d
ON a.Name=d.Name
INNER JOIN [TYPE]t
ON t.[In_Type] LIKE '%' + d.[Type] + '%'
SELECT a.NAME,d.[Type],
CASE
WHEN t.[In_Type] LIKE '%' + UPPER(d.[Type]) + '%' AND t.[Out_Type]='Primary' THEN 'Primary'
WHEN t.[In_Type] LIKE '%' + UPPER(d.[Type]) + '%' AND t.[Out_Type]='Secondary' THEN 'Secondary'
WHEN t.[In_Type] LIKE '%' + UPPER(d.[Type]) + '%' AND t.[Out_Type]='Third' THEN 'Third'
ELSE 'No Support' END AS [Out_Type]
FROM A a INNER JOIN D d
ON a.Name=d.Name
INNER JOIN [TYPE]t
ON t.[In_Type] LIKE '%' + UPPER(d.[Type]) + '%'
April 15, 2011 at 11:03 am
Yes Dan I am aware of that but I have no control on the design of the table.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply