2 different columns from 2 different tables into 1

Question

2 different columns from 2 different tables into 1

davdam8

Hall of Fame

Points: 3620
More actions
September 11, 2012 at 4:44 am

#267593

Hi people!
I need to display 2 different columns from 2 different tables into 1.
In this particular case: I want to display the RESOURCES and the GROUPS in the same column - here's the SQL:
SELECT DISTINCT MSP_WEB_SECURITY_CATEGORIES.WSEC_CAT_NAME, MSP_RESOURCES.RES_NAME, MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME
FROM MSP_RESOURCES INNER JOIN
MSP_WEB_SECURITY_CATEGORIES INNER JOIN
MSP_WEB_SECURITY_SP_CAT_RELATIONS AS MSP_WEB_SECURITY_SP_CAT_RELATIONS_1 ON
MSP_WEB_SECURITY_CATEGORIES.WSEC_CAT_UID = MSP_WEB_SECURITY_SP_CAT_RELATIONS_1.WSEC_CAT_UID INNER JOIN
MSP_WEB_SECURITY_GROUPS ON
MSP_WEB_SECURITY_SP_CAT_RELATIONS_1.WSEC_SP_GUID = MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID INNER JOIN
MSP_WEB_SECURITY_SP_CAT_RELATIONS ON
MSP_WEB_SECURITY_CATEGORIES.WSEC_CAT_UID = MSP_WEB_SECURITY_SP_CAT_RELATIONS.WSEC_CAT_UID ON
MSP_RESOURCES.RES_SECURITY_GUID = MSP_WEB_SECURITY_SP_CAT_RELATIONS.WSEC_SP_GUID AND
MSP_RESOURCES.RES_SECURITY_GUID = MSP_WEB_SECURITY_SP_CAT_RELATIONS.WSEC_SP_GUID
WHERE (MSP_WEB_SECURITY_CATEGORIES.WSEC_CAT_NAME IN (@CAT_NAME))
ORDER BY MSP_WEB_SECURITY_CATEGORIES.WSEC_CAT_NAME
(SSRS). And if you can give me a solution to have no repetition, both on the GROUPS and RESOURCES.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

Without any data or DDL's can't really help as don't know your structure. You could try UNIONing these in a sub query but that's just a shot in the dark..

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

davdam8 Hall of Fame Points: 3620 More actions · Answer 2

Here's the result I get:

CAT NAME RESOURCES GROUPS

My Resource Grey Manager

My Resource Grey Team Member

My Resource Holmes Team Member

My Resource Jones Team Member

I want:

CAT NAME RES & GROUPS

My Resource Grey

My Resource Holmes

My Resource Jones

My Resource Manager

My Resource Team Member

Does it help?

Regards

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

davdam8 (9/11/2012)
Here's the result I get:
CAT NAME RESOURCES GROUPS
My Resource Grey Manager
My Resource Grey Team Member
My Resource Holmes Team Member
My Resource Jones Team Member
I want:
CAT NAME RES & GROUPS
My Resource Grey
My Resource Holmes
My Resource Jones
My Resource Manager
My Resource Team Member
Does it help?
Regards

No not really, we've still no idea what your tables contain or how they are structured..

If you could post DDL's and sample data in a consumable format I'm sure we could help..

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Does this work for you?

http://msdn.microsoft.com/en-us/library/ms177561.aspx

http://msdn.microsoft.com/en-us/library/ms187831(v=SQL.105).aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

davdam8 Hall of Fame Points: 3620 More actions · Answer 5

Thanks!

Already solved the problem! Thanks for the link Cazares, all usefull info.

Regards