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
 General SQL Server Forums
 New to SQL Server Programming
 Three different tabl output to single table output

Author  Topic 

dhinasql
Posting Yak Master

195 Posts
Posted - 2010-08-10 : 08:01:38
Dear Friends,

I have the three different select statement and returning the three different table output, But i want to optimize everything into single output, please find my select statement and expected output below

SELECT RCode, DCode FROM Request WHERE ARCode = 'ST00008' AND RefCode = 71

From this Query i will will the RCode and Dcode in the Below format


RCode DCode
------------- -------------
104 77
111 116
109 24
108 19

I want to pass the Rcode and DCode to the another SELECT statment to select the related values, please find the below Query


Pass RCode to Select Statment

SELECT P.ProCode,P.ProName, M.MoDCode,M.MODName, R.RoleCode,R.ROLEName
FROM Roles R, Projects P, Modules M
WHERE R.RoleCode IN(104,111,109,108) AND P.ProCode = 4 AND M.MoDCode = 12

The output of the Above query is

ProCode | ProName | MoDCode | MODName |RoleCode |ROLEName ------------------------------------------------------------
4 | HRMS | 12 | MATERIAL REQUEST | 104| Finance

4 | HRMS | 12 | MATERIAL REQUEST | 108| Purchase

4 | HRMS | 12 | MATERIAL REQUEST | 109| Offer

4 | HRMS | 12 | MATERIAL REQUEST | 111| Material


Pass DCode to Select Statment

SELECT C.DCode, O.PCode, O.PDescription
FROM Combinations C, Operation O
WHERE C.DCode IN(77,116,24,19) AND C.DPCode = O.DPCode

Output of this Query Will be

DCode PCode PDescription
--------------- --------------- --------------------------------------------------
24 S SELECT
77 D DELETE
116 S SELECT
116 I INSERT
116 U UPDATE
116 D DELETE
19 I INSERT
19 D DELETE


Using three different select statments i can find the values like this, But what i need is I will the Output in the Single table format like the below one.

RowNumber | Description | RoleCode | DCode | PDescription | ParentRowId
1 | HRMS | NULL| NULL | NULL | NULL
2 | MATERIAL REQUEST| NULL| NULL | NULL | 1
3 | Finance | 104 | 77 | DELETE| 2
4 | Purchase| 108 | 19 | INSERT, DELETE| 2
5 | Offer | 109 | 24| SELECT| 2
6 | Material | 111| 116| SELECT, INSERT, UPDATE, DELETE| 2


Please let me know is this possible to get my expected output like above one in the single Stored Procedure??

Experts i need all your help !!

vaibhavktiwari83
Aged Yak Warrior

843 Posts
Posted - 2010-08-10 : 10:14:38
Here is one sample solution modify it according to you -
As I do not have sample data 


SELECT <What ever columns you want to display>
SELECT P.ProCode,P.ProName, M.MoDCode,M.MODName, R.RoleCode,R.ROLEName
FROM Roles R, Projects P, Modules M
INNER JOIN 
( SELECT RCode, DCode FROM Request WHERE ARCode = 'ST00008' AND RefCode = 71 ) A
ON A.RCode = R.RolwCode
INNER JOIN 
(
SELECT C.DCode, O.PCode, O.PDescription
FROM Combinations C, Operation O
C.DPCode = O.DPCode
) B
ON A.DCode = B.DCode
WHERE P.ProCode = 4 AND M.MoDCode = 12



Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-08-10 : 13:27:45
didnt understand how values in column ProName and ROLEName all end up in the same column Description

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

dhinasql
Posting Yak Master

195 Posts
Posted - 2010-08-10 : 23:40:46
Dear Visakh,

Thank you for your reply.

Yes actually the ProName AND ROLEName Need to End up with Description Column for my functionality to build the tree view.

But no worries please display the ProName and ROleName in the two different column accordingly, But the Role name is not available for any row please make it as NULL.

Thanks and looking forward to hear
Go to Top of Page

dhinasql
Posting Yak Master

195 Posts
Posted - 2010-08-11 : 00:24:28
Hi vaibhavktiwari,

After modified your query like below

SELECT P.ProCode,P.ProName, M.MoDCode,M.MODName, R.RoleCode,R.ROLEName
FROM Roles R, Projects P, Modules M
INNER JOIN
( SELECT RCode, DCode FROM Request WHERE ARCode = 'ST00008' AND RefCode = 71 ) A
ON A.RCode = R.RoleCode
INNER JOIN
(
SELECT C.DCode, O.PCode, O.PDescription
FROM Combinations C, Operation O
WHERE C.DPCode = O.DPCode
) B
ON A.DCode = B.DCode
WHERE P.ProCode = 4 AND M.MoDCode = 12


I am getting the error message on "ON A.RCode = R.RoleCode " - The multi-part identifier "R.RoleCode" could not be bound.

please let me know what i am missing here
Go to Top of Page

dhinasql
Posting Yak Master

195 Posts
Posted - 2010-08-11 : 02:26:51
Friends,

I have modified the Query as a single one, And i am getting the output in below Format

Query :

SELECT DISTINCT R.RoleCode,R.ROLEName,P.ProCode,P.ProName,M.MoDCode,M.MODName, C.DCode, O.PCode, O.PDescription
FROM Roles R, Projects P, Modules M,Combinations C, Operation O
WHERE R.RoleCode IN(SELECT RCode FROM Request WHERE ARCode = 'ST00008' AND RefCode = 71
) AND P.ProCode = 4 AND M.MoDCode = 12
AND C.DCode IN(SELECT DCode FROM Request WHERE ARCode = 'ST00008' AND RefCode = 71
) AND C.DPCode = O.DPCode

WRONG OUTPUT :

RoleCode | ROLEName | ProCode | ProName | MoDCode | MODName | DCode | PCode | PDescription
--------------------------------------------------------------------------------------------
104 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 24 | S | SELECT
108 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 24 | S | SELECT
109 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 24 | S | SELECT
111 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 24 | S | SELECT

104 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 77 | D | DELETE
108 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 77 | D | DELETE
109 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 77 | D | DELETE
111 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 77 | D | DELETE

104 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 116 | S | SELECT
108 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 116 | S | SELECT
109 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 116 | S | SELECT
111 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 116 | S | SELECT

104 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 116 | I | INSERT
108 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 116 | I | INSERT
109 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 116 | I | INSERT
111 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 116 | I | INSERT

Like this i am getting repeated values for DCode, PCode And PDescription

But my expected output will be

EXPECTED OUTPUT :
-------------------

RoleCode | ROLEName | ProCode | ProName | MoDCode | MODName | DCode | PCode | PDescription
--------------------------------------------------------------------------------------------
104 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 77 | D | DELETE
108 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 19 | I,D | INSERT, DELETE
109 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 24| S | SELECT
111 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 116 | S, I, U, D | SELECT,INSERT, UPDATE, DELETE

I would like to have my output in the Above format (PCode AND PDescription With Comma Separation ) . OR i need to have it in the Two different rows FOR EXAMPLE the Above Row 108 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 19 | I,D | INSERT, DELETE

CAN BE DISPLAYED AS

108 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 19 | I | INSERT
108 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 19 | D | DELETE

Hope i have explained better now, please let me know is there still anything confusing.

All your helps are much appreciated.
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

843 Posts
Posted - 2010-08-11 : 02:28:08
Can you provide table structures ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER
Go to Top of Page

dhinasql
Posting Yak Master

195 Posts
Posted - 2010-08-11 : 02:41:14
Yes i am posting Structure and some of sample data for your kind reference

Request
---------
ARCode | RefCode | RCode


Roles
---------
RoleCode | R.ROLEName


Projects
---------
ProCode | ProName


modules
-----------
MoDCode | MODName


Combination
-------------
DCode | PCODE
19 I
19 D
24 S
77 D
116 S
116 I
116 U
116 D


Operations
-----------
PCODE | PDescription
S SELECT
I INSERT
U UPDATE
D DELETE

Thanks in advance
Go to Top of Page

vaibhavktiwari83
Aged Yak Warrior

843 Posts
Posted - 2010-08-11 : 02:54:07
Table names and columns names are not exactly what you are using in the above query ?

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER
Go to Top of Page

dhinasql
Posting Yak Master

195 Posts
Posted - 2010-08-11 : 04:30:58
There was some typo

Table Name : Combination --> Combunations in Table structure

and the Column Name PCODE --> DPCode in the Combinations table

And the Column Name PCODE -- > DPCode in the Operations table.

But other than are same.

please do not worry about my query, if you have better one i am much happy to use.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-08-11 : 10:24:02
can you explain how you got below in output?

111 | Finance | 4 | HRMS | 12 | MATERIAL REQUEST | 116 | S, I, U, D | SELECT,INSERT, UPDATE, DELETE

i cant see values UPDATE, DELETE being coming for RoleCode 111

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

dhinasql
Posting Yak Master

195 Posts
Posted - 2010-08-12 : 00:12:47
SELECT,INSERT, UPDATE, DELETE in NOT dependent on the Role Code, Its Dependent on DCode 116

Please let me know if i am missing anything
Go to Top of Page

dhinasql
Posting Yak Master

195 Posts
Posted - 2010-08-13 : 01:09:56
Friends.....

Any ideas ?? please let me know
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site