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.
mikha eAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
Question abandoned
Provided Solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.