How do write a query to get data from 3 tables to display in 1 row

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

user_orgs.userid	user_orgs.orgsequence	user_orgs.orgid	user_orgs.membershiptype
BSS000002556	1	BSS000007403	1
BSS000002556	2	BSS000017078	1
BSS000002556	3	BSS000024650	1
BSS000002556	4	BSS000027067	2

orgstructure.orgid	orgstructure.parentid	orgstructure.orgcode	orgstructure.description
BSS000007403	BSS000000001	ABCCorp	ABC Corp
BSS000017078	BSS000007403	Asia	Asia
BSS000024650	BSS000017078	China	China
BSS000027067	BSS000024650	Beijing	Beijing - Head Office

users.userid	users.givenname	users.familyname	users.level1id	users.level2id	users.level3id	users.level4id	users.level5id	users.leaf_orgid
BSS000002556	John 	Doe	ABCCorp	Asia	China	Beijing	NULL	BSS000027067

How do I get these 3 tables into 1 row like this?

users.givenname	users.familyname	orgstructure.orgid1	orgstructure.description1	orgstructure.orgid2	orgstructure.description2	orgstructure.orgid3	orgstructure.description3	orgstructure.orgid4	orgstructure.description4
John 	Doe	BSS000007403	ABCCorp	BSS000017078	Asia	BSS000024650	China	BSS000027067	Beijing - Head Office

Posted 24-Sep-13 15:15pm

guerinto

Updated 24-Sep-13 18:52pm

Aarti Meswania

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PIEBALDconsult · Answer 1 · 2013-09-24T15:54:00

Solution 1

Usually with a couple of JOINs; but you could do it other ways if you want.

Posted 24-Sep-13 15:54pm

PIEBALDconsult

VeeraAnnapureddy · Answer 2 · 2013-09-24T20:26:00

SELECT	Usersgivenname
,	Usersfamilyname
,	Tem2.orgstructureorgid	  AS	'Orgstructureorgid1'
,	Tem2.orgstructureorgcode  AS	'Orgstructuredescription1'
,	T1.orgstructureorgid	  AS	'Orgstructureorgid2'
,	T1.orgstructureorgcode	  AS	'Orgstructuredescription2'
,	T2.orgstructureorgid	  AS	'Orgstructureorgid3'
,	T2.orgstructureorgcode	  AS	'Orgstructuredescription3'
,	T3.orgstructureorgid	  AS	'Orgstructureorgid4'
,	T3.orgstructureorgcode    AS	'Orgstructuredescription4'
	FROM Tem3  
	INNER JOIN Tem1 
	ON Tem3.usersuserid = Tem1.user_orgsuserid
	INNER JOIN Tem2 
	ON Tem2.orgstructureorgid = Tem1.user_orgsorgid
	AND Tem2.orgstructureorgcode = Tem3.userslevel1id
	INNER JOIN Tem2 AS T1 
	ON T1.orgstructureorgcode = Tem3.userslevel2id
	INNER JOIN Tem2 AS T2
	ON T2.orgstructureorgcode = Tem3.userslevel3id
	INNER JOIN Tem2 AS T3
	ON T3.orgstructureorgcode = Tem3.userslevel4id