Help with joins - 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 2008 Forums

Transact-SQL (2008)

Help with joins

Author

Topic

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2012-06-15 : 07:50:59

---------------------- SCRIPTS

Create Table IPP_1
(
Section_Ref varchar (50),
Policy_part_key varchar (50)
)

Create Table AQ_1
(
Policy_part_key varchar (50),
Q_1440 varchar (50),
Q_175 varchar(50)
)

Insert into IPP_1 Values ('B','B1')
Insert into IPP_1 Values ('B','B2')
Insert into IPP_1 Values ('C','C1')
Insert into IPP_1 Values ('C','C2')
Insert into IPP_1 Values ('C','C3')

Insert into AQ_1 values ('B1','Something','DOnt Need')
Insert into AQ_1 values ('B2', 'We Need', 'VALUE')
Insert into AQ_1 values ('C1','Something','DOnt Need')
Insert into AQ_1 values ('C2','Something','DOnt Need')
Insert into AQ_1 values ('C3','Something','DOnt Need')

Select * from IPP_1
Select * from AQ_1

------------------------- LOGIC

For Each Section_ref I need values like below
Section_ref = 'B' has 2 Policy_part_key againt it. Check for Both policy_part_keys in AQ_1 . If Q_1440 is = 'WE Need' then We take values in Q_175
So 1st recors will look like this
B 'VAlue'

However For C Q_1440 is never = 'We Need' so 2nd record will look like this
C NULL

Need to accomplish this using joins and joins a some join conditions
only since this is iny join condition in between lots of other joins. No group by can be used. Case staement in Select would do.
-------------------------- EXPECTED RESULT
B 'VAlue'
C NULL

--------------------------- MY TRIALS

-- Try 1 -- Using Inner join -- missing row for
Select IPP_1.Section_ref,AQ_1.Q_175
from IPP_1
inner join AQ_1 on IPP_1.Policy_part_key =AQ_1.Policy_part_key and Q_1440 = 'We Need'

-- Result
B VALUE

Select IPP_1.Section_ref,AQ_1.Q_175
from IPP_1
left join AQ_1 on IPP_1.Policy_part_key =AQ_1.Policy_part_key and Q_1440 = 'We Need'

-- Result
B NULL
B VALUE
C NULL
C NULL
C NULL

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-15 : 11:28:58

I'm not sure how you would want to handle multiple matches (assuming there can be), but maybe this might help:

SELECT
	A.Section_Ref,
	B.Q_175
FROM
	(
		SELECT DISTINCT Section_ref
		FROM IPP_1
	) AS A
LEFT OUTER JOIN
	(
		SELECT 
			IPP_1.Section_Ref, 
			AQ_1.Q_175
		FROM
			IPP_1
		INNER JOIN
			AQ_1
			ON IPP_1.Policy_part_key = AQ_1.Policy_part_key
		WHERE
			AQ_1.Q_1440 = 'We Need'
	) AS B
	ON A.Section_Ref = B.Section_Ref

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources