Like's and Join's - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Like's and Join's

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-04-15 : 11:55:26

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.[In_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.[In_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.[In_Type] + '%')

All above Queries Do not return anything

My output should look like this
Name Type Out_0Type
Jo A Primary
Lina B Secondary

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-04-15 : 12:56:49

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]) + '%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources