how to write multiple select queries in single procedure?

Question

I have to tables as: table1:

UID | COLLEGE_NAME | COLLEGE_ADDRESS
------------------------------------

table2:

UID | COMPANY_NAME | COMPANY_ADDRESS
------------------------------------

i have 2 queries:

select * from table1 where uid='$uid';   
select * from table2 where uid='$uid';

i want to write this two queries in one procedure.

do you want to combine their result?

John Woo
– John Woo

2013-02-22 09:35:33 +00:00
Commented Feb 22, 2013 at 9:35 — John Woo
– John Woo, Commented Feb 22, 2013 at 9:35
ya i have to combine the results also.

Harjeet Jadeja
– Harjeet Jadeja

2013-02-22 09:36:26 +00:00
Commented Feb 22, 2013 at 9:36 — Harjeet Jadeja
– Harjeet Jadeja, Commented Feb 22, 2013 at 9:36

Harjeet Jadeja · Accepted Answer · 2013-02-22 11:19:53Z

2

structure for multiple select quires in single procedure:

CREATE PROCEDURE sample(l_uid INT) BEGIN

SELECT * FROM college_edu WHERE uid= l_uid;

SELECT * FROM work_experience WHERE uid= l_id;

END

answered Feb 22, 2013 at 11:19

Harjeet Jadeja

1,6044 gold badges20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Woo · Accepted Answer · 2013-02-22 09:37:27Z

0

Here's the statement to create STORED PROCEDURE.

DELIMITER $$
CREATE PROCEDURE procedureName(IN _uid VARCHAR(15))
BEGIN
    SELECT UID, COLLEGE_NAME name, COLLEGE_ADDRESS address
    FROM    table1
    WHERE uid = _uid
    UNION ALL
    SELECT UID, COMPANY_NAME name, COMPANY_ADDRESS address
    FROM    table2
    WHERE uid = _uid
END $$
DELIMITER ;

notice that UNION has ALL keyword on it to add duplicate records on the result list. But if you prefer UNIQUE, remove the ALL keyword on the UNION.

answered Feb 22, 2013 at 9:37

John Woo

265k70 gold badges509 silver badges500 bronze badges

2 Comments

Harjeet Jadeja Over a year ago

what if i don't want to union result, i just want to inherit multiple select queries in procedure then?

Michael Fulton Over a year ago

@HarjeetJadeja Why did you accept this answer ? With this answer, you lose the information of whether that UID is for a college or company.

user2063626 · Accepted Answer · 2013-02-22 09:40:39Z

0

below code may serve you purpose. Also the additional tbl column will let you know from which table your data is coming and it would help in further manipulation.

 SELECT UID, COLLEGE_NAME name, COLLEGE_ADDRESS address , 1 as tbl
    FROM    table1
    WHERE uid = _uid
    UNION ALL
    SELECT UID, COMPANY_NAME name, COMPANY_ADDRESS address , 2 as tbl
    FROM    table2
    WHERE uid = _uid

answered Feb 22, 2013 at 9:40

user2063626

1 Comment

Harjeet Jadeja Over a year ago

what if i don't want to union result, i just want to inherit multiple select queries in procedure then?

Collectives™ on Stack Overflow

how to write multiple select queries in single procedure?

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related