SQL - Display Multiple rows in one single row

[PAUL41]

I have a query which will always display amounts on 2 seperate rows. Which i would like to merge together into 1 row. The complication of this is that 1 of the queries may return nothing.

SELECT amount
FROM TABLE a
WHERE REFERENCE IN(1,2)

RETURNS 2 rows. I need the above to display my answer as 1 row;

amount (ref1), amount (ref2).

The following does combine and give me what i want. However, if one of the queries does NOT return anything from the database then nothing is returned. I need it always to return something. (There will always be 1 query that will return something).

What i have so far which is not quite right;

SELECT * FROM(
SELECT amount
FROM Table a
WHERE REFERENCE = 1) A
(SELECT amount
FORM Table b
WHERE REFERENCE = 2)B

This does return what i need. However, if i amend the query for one of the select statements to say reference 3 which does not exist. Nothing is returned. I would still want reference 1 returned and null for the second query.

Requre outputs;

1, 1
1, null
null, 1

null, null will never occur.

Any help would be appreciated.

Thanks in advance

[frostymarvelous]

I'm not quite good with multiple queries as I never needed them, but have you tried using a union?

[PAUL41]

Hi thanks a union on the query I want sounds like it should work. However, I can't quite get the syntax correct. Anyone help?

[frostymarvelous]

Just use UNION in between the two statements

Select 1 condition 1 UNION select 2 condition 2

Added after 2 minutes:

If it works right, you will have both result sets even if condition two returns no rows.

[GumSlone]

Here is an example for union all which ive used at one of my sites

(SELECT buddies.buddy2 AS buddy FROM buddies WHERE buddies.buddy1='1')
          UNION ALL (SELECT buddies.buddy1 AS buddy FROM buddies WHERE buddies.buddy2='1')
          ORDER BY lastactive DESC LIMIT 1,10

[PAUL41]

Hi thanks for the replies. The union query does half the solution. As soon as I do a union I am back to displaying my results in 2 separate rows when I require them to always be returned in 1 row.