SQL UNION Query with Extra Field in Results

Question

I have a SQL query that is working well except I would like to bring the results of another column in the first db with it. This is what I currently have:

SELECT parts1.PART_NBR, parts1.NIIN
FROM parts1
WHERE parts1.PART_NBR='$pn'
UNION
SELECT parts2.REFERENCE_NUMBER, parts2.NIIN
FROM parts2
WHERE parts2.REFERENCE_NUMBER='$pn'

This is what I basically want (of course this wont work):

SELECT parts1.PART_NBR, parts1.NIIN, parts1.ANOTHER_FIELD
FROM parts1
WHERE parts1.PART_NBR='$pn'
UNION
SELECT parts2.REFERENCE_NUMBER, parts2.NIIN
FROM parts2
WHERE parts2.REFERENCE_NUMBER='$pn'

How do I write the query so that it does the same thing but actually brings back the extra field in the results?

Answer 1

Create an empty field, returning NULL

SELECT parts1.PART_NBR, parts1.NIIN, parts1.ANOTHER_FIELD
FROM parts1
WHERE parts1.PART_NBR='$pn'
UNION ALL
SELECT parts2.REFERENCE_NUMBER, parts2.NIIN, NULL AS ANOTHER_FIELD
FROM parts2
WHERE parts2.REFERENCE_NUMBER='$pn'

EDIT: From your comment you are seeing the results as

Part Number: 21223 NIIN: 008194914 Name: CAPACITOR
Part Number: 21223 NIIN: 011241926 Name: HEAT SINK
Part Number: 21223 NIIN: 003901600 Name: KNIFE
Part Number: 21223 NIIN: 003901600 Name: 
Part Number: 21223 NIIN: 008194914 Name:
Part Number: 21223 NIIN: 011241926 Name:

The first results are coming from the table parts1 the second from parts2, the blank Name fields are where you are returning NULL.

From the information you have given I don't see why you are using a UNION to get the results from these two tables as they seem to contain the same information except the first table also has the Name field.

Would it not be better to JOIN the tables on the Part/Reference number in order to select the name?

EDIT: As you said in your comment, previously you were getting a DISTINCT result set because of using UNION. With the NULL field the rows are no longer unique and the query returns all the rows.

I said in a comment that I do not see what the current UNION statement is doing for you as it seems the same information is both tables. Is there more to this query than what you have told us?