Adding an Additional Column to SQL Result Set by Using two Que

Question

Basically, I want have two separate SQL queries, but I want them to be displayed in the same result set. However, the first query returns several columns and the second query only returns one column.

If I want the results from the second query to simply be added on as an additional column to the results from the first query, how do I do this?

Query 1:

SELECT cr.COMMUNICATION_ID, cr.CONSUMER_ID, cr.ACTION_LOG_ID, cal.CONSUMER_ID, cal.TIPS_AMOUNT, cal.LAST_MOD_TIME
FROM COMMUNICATION_RELEVANCE AS cr
JOIN consumer_action_log AS cal
ON cr.ACTION_LOG_ID=cal.ACTION_LOG_ID;

QUERY 2:

SELECT AVG(TIPS_AMOUNT) AS AVG_TIPS
FROM CONSUMER_ACTION_LOG
JOIN COMMUNICATION_RELEVANCE
ON CONSUMER_ACTION_LOG.SENDER_CONSUMER_ID=COMMUNICATION_RElEVANCE.consumer_id;

Basically, I want a UNION, but for queries with different number of columns.

What happens if the number of rows in the two queries are not the same? — Kirk Broadhurst
– Kirk Broadhurst, Commented Jan 25, 2011 at 2:39

RichardTheKiwi · Accepted Answer · 2011-01-25 02:35:58Z

3

I think you actually want to add one column, not one row as the other answers suggest

SELECT cr.COMMUNICATION_ID, cr.CONSUMER_ID, cr.ACTION_LOG_ID,
       cal.CONSUMER_ID, cal.TIPS_AMOUNT, cal.LAST_MOD_TIME,
       SINGLETON.AVG_TIPS
FROM COMMUNICATION_RELEVANCE AS cr
JOIN consumer_action_log AS cal ON cr.ACTION_LOG_ID=cal.ACTION_LOG_ID
CROSS JOIN
(
    SELECT AVG(TIPS_AMOUNT) AS AVG_TIPS
    FROM CONSUMER_ACTION_LOG
    JOIN COMMUNICATION_RELEVANCE
    ON CONSUMER_ACTION_LOG.SENDER_CONSUMER_ID=COMMUNICATION_RElEVANCE.consumer_id
) SINGLETON

The trick is that you join to the query that produces the single row, single column AVG value.

answered Jan 25, 2011 at 2:35

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

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

1 Comment

Spencer Over a year ago

However, why cannot I create a VIEW with results derived from a cross join?

Mano Kovacs · Accepted Answer · 2011-01-25 02:43:50Z

0

SELECT AVG(TIPS_AMOUNT) AS AVG_TIPS, NULL, NULL, NULL...

Add as much NULL as less column the shorter query has.

edited Jan 25, 2011 at 2:43

answered Jan 25, 2011 at 2:23

Mano Kovacs

1,5141 gold badge13 silver badges16 bronze badges

3 Comments

nybbler Over a year ago

Should be able to use NULLs in place of strings or values that may collide with actual results.

Mano Kovacs Over a year ago

@nybbler: should or shouldn't? I tryed now with null and "" and both works, but of course NULL!="".

nybbler Over a year ago

Sorry, I wasn't clear. Should be able to use NULL, which since some the results in his/her first column list are IDs should ensure that the results from the second part of the union are identifiable if they need to be for some reason. Otherwise, they're not wasting bandwidth transmitting anything.

Collectives™ on Stack Overflow

Adding an Additional Column to SQL Result Set by Using two Que

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related