6

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?

Erik
  • 61
  • 1
  • 1
  • 2

4 Answers4

5

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?

Tony
  • 9,672
  • 3
  • 47
  • 75
  • When I do that I get the same results back twice only the first set has the extra field and the seconed set doesnt. – Erik May 01 '11 at 02:47
  • Interesting, which database are you using (SQL Server, MySQL etc)? – Tony May 01 '11 at 02:50
  • 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: – Erik May 01 '11 at 02:55
  • Try executing only the second part of the `UNION` query to see if you get results with an additional field with `NULL` in it. I notice you are using the same parameter for the `WHERE` clause in both queries even though they have different names: `PART_NBR` & `REFERENCE_NUMBER`. Is that intentional? – Tony May 01 '11 at 02:56
  • They are named differently in the 2 tables – Erik May 01 '11 at 02:57
  • I see the problem, you want the `NAME` field to be repeated in the rows from table `parts2` rather than displaying an empty (null) field. You say you are getting the same results twice but they are actually coming from the other table, with NULL as the name. – Tony May 01 '11 at 03:00
  • I just don't want it repeated. Because of the NULL field in the second query the results are seen as unique? So they are being displayed again? – Erik May 01 '11 at 03:08
  • Yes, that would be the case. If you don't want to do that you could use the UNION query to get the first info you need and then wrap it in another query to join it to the first table again to get the name. You could use a Common Table Expression to make the query a little easier to read. – Tony May 01 '11 at 03:10
3
SELECT parts.PART_NBR, parts.NIIN, parts1.ANOTHER_FIELD
FROM  (
        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'
      ) AS parts(PART_NBR, NIIN)
  LEFT OUTER JOIN parts1
    ON parts.PART_NBR = parts1.PART_NBR AND
       parts.NIIN = parts1.NIIN
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
0

Simple as below:

select part_number, niin, max(field)
from (     
    SELECT parts1.PART_NBR part_number, parts1.NIIN niin, parts1.ANOTHER_FIELD field
    FROM parts1
    WHERE parts1.PART_NBR='$pn'
    UNION
    SELECT parts2.REFERENCE_NUMBER part_number, parts2.NIIN niin, "" field
    FROM parts2
    WHERE parts2.REFERENCE_NUMBER='$pn')
group by part_number, niin

You just need to accommodate a placeholder in the second query to match the extra field location of the first.

cmutt78
  • 861
  • 1
  • 9
  • 18
  • This just brings the results back twice. The second time they dont have that field. Like: 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: – Erik May 01 '11 at 02:52
  • Can you give an example of what your expected output would like like? – cmutt78 May 01 '11 at 03:03
  • Part Number: 21223 NIIN: 008194914 Name: CAPACITOR Part Number: 21223 NIIN: 011241926 Name: HEAT SINK Part Number: 21223 NIIN: 003901600 Name: KNIFE – Erik May 01 '11 at 03:06
  • changed my query above to accomodate – cmutt78 May 01 '11 at 03:31
0

You have to have matching columns, however you can place a "fake" column in the second one that comes back null or whatever default value you want.

SELECT parts1.PART_NBR, parts1.NIIN, parts1.ANOTHER_FIELD
FROM parts1
WHERE parts1.PART_NBR='$pn'
UNION
SELECT parts2.REFERENCE_NUMBER, parts2.NIIN, NULL AS ANOTHER_FIELD
FROM parts2
WHERE parts2.REFERENCE_NUMBER='$pn'
Dustin Laine
  • 37,935
  • 10
  • 86
  • 125