stored procedure - sql select from multiple tables

can I select a value from one table and based on that value, select entries from a different table.

how can I do this in a stored procedure, so that, if possible , I can do both on one call.
novice programmerAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Please find the detailed explanation and sample solutions below including creation of stored procedure.

>>Can I select a value from one table and based on that value, select entries from a different table.
YES IT IS POSSIBLE.

Sample example below-

CREATE TABLE T1
(
  ID INT
)
GO

INSERT INTO T1 VALUES (1),(2),(3)
GO

CREATE TABLE T2
(
  ID INT 
 ,VAL VARCHAR(20)
)
GO

INSERT INTO T2 VALUES (1,'A'),(2,'C'),(5,'Z')
GO

Open in new window


>>Can I select a value from one table and based on that value, select entries from a different table.
SOLUTION 1 - CROSS APPLY

SELECT T1.ID,u.VAL from T1 
CROSS APPLY ( SELECT * FROM T2 WHERE T1.ID = T2.ID) u

Open in new window


SOLUTION 2 - INNER JOIN /*Can change to LEFT/RIGHT/FULL OUTER JOIN based on your need*/

SELECT T1.ID,T2.VAL from T1 
INNER JOIN T2 ON T1.ID = T2.ID

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
ID          VAL
----------- --------------------
1           A
2           C

(2 row(s) affected)

Open in new window


>>how can I do this in a stored procedure, so that, if possible , I can do both on one call.
YES It is POSSIBLE.

Sample code
--
CREATE PROC PROC_NAME
(
	@ID INT /*@ParameterName DATATYPE*/
)
AS
BEGIN
	SELECT T1.ID,T2.VAL from T1 
	INNER JOIN T2 ON T1.ID = T2.ID
	/*WHERE Clause if needed*/
END

Open in new window


EXECUTION

EXEC PROC_NAME 0

Open in new window


OUTPUT
/*------------------------
EXEC PROC_NAME 0
------------------------*/
ID          VAL
----------- --------------------
1           A
2           C

(2 row(s) affected)

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Yes you can.

declare @id int

select @id = id from table1 where <condtions>  -- note this must be a singular value.

-- OR

select id into #ids from table where <conditions> -- this can return multiple records.

Open in new window


then:

select * from table2 where id = @id 

--or 

select * from table2 where id in (select id from #ids)

Open in new window

0
 
Olaf DoschkeSoftware DeveloperCommented:
Why wouldn't you just use a simple join?

Select table2.* from table1 join table2 on somejoincondition WHERE table1.field=@aprameter

Open in new window


There's nothing magical about this, you can use one table to get your conditions on another table and via table2.* only select fields of table2, so table1 is only used to find your filter criteria values. You may want a left or right, inner or outer join, but that doesn't even need a stored procedure.

Bye, Olaf.
1
 
Pawan KumarDatabase ExpertCommented:
Question abandoned
Provided Solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.