stored procedure

  • I have a requirement whereby I have to query 3 different tables and return the results in one result set

    eg

    select column1, column2, column3 from table1 where something = a parameter value

    select column8, column10, column12 from table2 where something = a parameter value

    select column 2, column3, column4 from table3 where something = a parameter value

    the columns will all be the same datatype

    eg column1,column8 & column2 will all be varchar, etc

    i want to combine the results from each of the three queries into one resultset and return this from a stored procedure.

    can anyone give me some pointers/help ?

  • I think the UNION or UNION ALL operators are what you're looking for.

    John

  • You mostly have what you need. Put a "Union All" between the selects.

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • thanks, that does work. I do have another question related to the same problem though.

    Im using an IF ELSE statement, how do i get it to work with a UNION ALL. Heres what im trying to do

    IF Condition1

    THEN

    SELECT STATEMENT1

    ELSE IF CONDITION 2

    THEN

    SELECT STATEMENT 2

    ELSE

    SELECT STATEMENT 3

    END

    UNION ALL

    IF CONDITION 3

    THEN

    SELECT STATEMENT 4

    ELSE IF CONDITION 4

    THEN

    SELECT STATEMENT 5

    ELSE

    SELECT STATEMENT 6

    END

  • Use CASE instead of IF.

    John

  • CELKO (3/25/2011)


    We have a CASE expression; it is no0t a CASE statement! IMPORTANT DIFFERENCE.

    Nobody used the phrase "CASE statement", did they? It's a difference, granted, but an important one? Surely the important thing is how you use it, not what you call it?

    John

  • I eneded up approaching it from a different angle and used temporary tables in stead

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply