How to subquery - 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)

How to subquery

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-09-04 : 11:14:56

I have these 2 queries that are the SQL for a report in a report writer. The 2 are joined together, I believe with and inner join and they have a 1 to many relationship. But the join is just created on a graphical interface. I can't retrieve the actual SQL after they are joined. Can you help me put the second query as a subquery to the first joining on RESOURCE_ID?

SELECT DISTINCT VW_RESOURCES.RESOURCE_ID, 
                VW_RESOURCES.NAME_LAST, 
                VW_RESOURCES.ADDRESS_1, 
                VW_RESOURCES.STATE, 
                VW_RESOURCES.ZIP, 
                VW_RESOURCES.CITY, 
                VW_RESOURCES.WORKPH, 
                VW_RESOURCES.ORGANIZATION_NAME, 
                VW_RESOURCES.CAN_BE_ASSIGNED_TASK
FROM VW_RESOURCES VW_RESOURCES
WHERE ( VW_RESOURCES.ADMIN_SET_ID = 49 )
       AND ( VW_RESOURCES.RESOURCE_TYPE IN ('AL','IL','NH') )
       AND ( VW_RESOURCES.STATUS = 'A' )
       AND ( VW_RESOURCES.CAN_BE_ASSIGNED_TASK = 'Y' )
ORDER BY VW_RESOURCES.CITY, 
         VW_RESOURCES.ORGANIZATION_NAME

--RES_ROLE
SELECT DISTINCT RES_ROLE.RESOURCE_ID, 
                A_RESOURCE_TYPE.DESCRIPTION, 
                A_RESOURCE_TYPE.RESOURCE_TYPE_CODE, 
                Case RES_Role.Resource_Type when 'AL' then 'ALF' when 'IL' then 'ILF' when 'NH' then 'SNF' else null end Case_RES_Role_Resource_Ty
FROM RES_ROLE RES_ROLE
      LEFT OUTER JOIN A_RESOURCE_TYPE A_RESOURCE_TYPE ON 
     (A_RESOURCE_TYPE.ADMIN_SET_ID = RES_ROLE.ADMIN_SET_ID)
      AND (A_RESOURCE_TYPE.RESOURCE_TYPE_CODE = RES_ROLE.RESOURCE_TYPE)
ORDER BY A_RESOURCE_TYPE.RESOURCE_TYPE_CODE

I know you can't have the ORDER BY in the subquery, but other than that, I still don't know how to code it. I would appreciate any help. Thank you.

Duane

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-04 : 12:05:28

I don't quite understand how those two queies work together so here is a guess:

SELECT DISTINCT 
	VW_RESOURCES.RESOURCE_ID, 
	VW_RESOURCES.NAME_LAST, 
	VW_RESOURCES.ADDRESS_1, 
	VW_RESOURCES.STATE, 
	VW_RESOURCES.ZIP, 
	VW_RESOURCES.CITY, 
	VW_RESOURCES.WORKPH, 
	VW_RESOURCES.ORGANIZATION_NAME, 
	VW_RESOURCES.CAN_BE_ASSIGNED_TASK
FROM 
	VW_RESOURCES AS VW_RESOURCES
INNER JOIN
	(
		SELECT DISTINCT 
			RES_ROLE.RESOURCE_ID, 
			A_RESOURCE_TYPE.DESCRIPTION, 
			A_RESOURCE_TYPE.RESOURCE_TYPE_CODE, 
			CASE 
				RES_Role.Resource_Type 
					when 'AL' then 'ALF' 
					when 'IL' then 'ILF' 
					when 'NH' then 'SNF' 
					else null 
			END AS Case_RES_Role_Resource_Ty
		FROM 
			RES_ROLE RES_ROLE
		LEFT OUTER JOIN 
			A_RESOURCE_TYPE A_RESOURCE_TYPE 
			ON A_RESOURCE_TYPE.ADMIN_SET_ID = RES_ROLE.ADMIN_SET_ID
			AND A_RESOURCE_TYPE.RESOURCE_TYPE_CODE = RES_ROLE.RESOURCE_TYPE
	) AS T
	ON VW_RESOURCES.RESOURCE_ID = T.RESOURCE_ID
	
WHERE
	VW_RESOURCES.ADMIN_SET_ID = 49 
	AND  VW_RESOURCES.RESOURCE_TYPE IN 'AL','IL','NH' 
	AND  VW_RESOURCES.STATUS = 'A' 
	AND  VW_RESOURCES.CAN_BE_ASSIGNED_TASK = 'Y' 
ORDER BY 
	VW_RESOURCES.CITY, 
	VW_RESOURCES.ORGANIZATION_NAME

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-09-04 : 12:31:04

quote:

Originally posted by Lamprey

I don't quite understand how those two queies work together so here is a guess:

SELECT DISTINCT 
	VW_RESOURCES.RESOURCE_ID, 
	VW_RESOURCES.NAME_LAST, 
	VW_RESOURCES.ADDRESS_1, 
	VW_RESOURCES.STATE, 
	VW_RESOURCES.ZIP, 
	VW_RESOURCES.CITY, 
	VW_RESOURCES.WORKPH, 
	VW_RESOURCES.ORGANIZATION_NAME, 
	VW_RESOURCES.CAN_BE_ASSIGNED_TASK
FROM 
	VW_RESOURCES AS VW_RESOURCES
INNER JOIN
	(
		SELECT DISTINCT 
			RES_ROLE.RESOURCE_ID, 
			A_RESOURCE_TYPE.DESCRIPTION, 
			A_RESOURCE_TYPE.RESOURCE_TYPE_CODE, 
			CASE 
				RES_Role.Resource_Type 
					when 'AL' then 'ALF' 
					when 'IL' then 'ILF' 
					when 'NH' then 'SNF' 
					else null 
			END AS Case_RES_Role_Resource_Ty
		FROM 
			RES_ROLE RES_ROLE
		LEFT OUTER JOIN 
			A_RESOURCE_TYPE A_RESOURCE_TYPE 
			ON A_RESOURCE_TYPE.ADMIN_SET_ID = RES_ROLE.ADMIN_SET_ID
			AND A_RESOURCE_TYPE.RESOURCE_TYPE_CODE = RES_ROLE.RESOURCE_TYPE
	) AS T
	ON VW_RESOURCES.RESOURCE_ID = T.RESOURCE_ID
	
WHERE
	VW_RESOURCES.ADMIN_SET_ID = 49 
	AND  VW_RESOURCES.RESOURCE_TYPE IN 'AL','IL','NH' 
	AND  VW_RESOURCES.STATUS = 'A' 
	AND  VW_RESOURCES.CAN_BE_ASSIGNED_TASK = 'Y' 
ORDER BY 
	VW_RESOURCES.CITY, 
	VW_RESOURCES.ORGANIZATION_NAME

Thank you for your reply. It worked after I added parentheses to the 'IN' keyword. I also added the CASE statement field to the top query (I needed that) and I didn't need the 2nd and 3rd line of the subquery. So here is my final version, and thank you again:

SELECT DISTINCT 
	VW_RESOURCES.RESOURCE_ID, 
	VW_RESOURCES.NAME_LAST, 
	VW_RESOURCES.ADDRESS_1, 
	VW_RESOURCES.STATE, 
	VW_RESOURCES.ZIP, 
	VW_RESOURCES.CITY, 
	VW_RESOURCES.WORKPH, 
	VW_RESOURCES.ORGANIZATION_NAME, 
	VW_RESOURCES.CAN_BE_ASSIGNED_TASK,
        CASE_RES_ROLE_RESOURCE_TYPE
FROM 
	VW_RESOURCES AS VW_RESOURCES
INNER JOIN
	(
		SELECT DISTINCT 
			RES_ROLE.RESOURCE_ID, 
			CASE 
				RES_Role.Resource_Type 
					when 'AL' then 'ALF' 
					when 'IL' then 'ILF' 
					when 'NH' then 'SNF' 
					else null 
			END AS CASE_RES_ROLE_RESOURCE_TYPE
		FROM 
			RES_ROLE RES_ROLE
		LEFT OUTER JOIN 
			A_RESOURCE_TYPE A_RESOURCE_TYPE 
			ON A_RESOURCE_TYPE.ADMIN_SET_ID = RES_ROLE.ADMIN_SET_ID
			AND A_RESOURCE_TYPE.RESOURCE_TYPE_CODE = RES_ROLE.RESOURCE_TYPE
	) AS T
	ON VW_RESOURCES.RESOURCE_ID = T.RESOURCE_ID
	
WHERE
	VW_RESOURCES.ADMIN_SET_ID = 49 
	AND  VW_RESOURCES.RESOURCE_TYPE IN ('AL','IL','NH') 
	AND  VW_RESOURCES.STATUS = 'A' 
	AND  VW_RESOURCES.CAN_BE_ASSIGNED_TASK = 'Y' 
ORDER BY 
	VW_RESOURCES.CITY, 
	VW_RESOURCES.ORGANIZATION_NAME

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources