SQL Query That Looks At 3 Columns And Only Pulls The Non Blank Value

user_15315494 0
Viewed 41

I am looking for a way to pull all invoices from a table with the tracking number for that invoice. For example:

SELECT Invoice, FedEx, UPS, DHL from shipfile

would show all invoices and all tracking numbers. If it was shipped FedEx, the tracking number will be in the FedEx column. Same for the others. I want to be able to pull out the Invoice and then 1 other "Tracking" column that will display either the FedEx, UPS or DHL result (which ever one contains the tracking number). So if there is a FedEx tracking number, UPS and DHL will be blank, so I'd want to see the Invoice and the FedEx tracking number. And same thing if it has UPS tracking, FedEx and DHL will be blank so I want to only see the UPS result.

Before:

Invoice  FedEx   UPS   DHL


1        F123    

2                U321

3                      D746

After:

Invoice    Tracking

1          F123

2          U321

3          D746
mysql sql database
2 Answers

Use coalesce():

select invoice, coalesce(fedex, ups, dhl) as tracking_number
from shipfile;
user_11440350

Assuming that these "blank" values are actually NULL, we can use COALESCE here:

SELECT Invoice, COALESCE(FedEx, UPS, DHL) AS Tracking
FROM shipfile;

If the blank values are really empty string, then use the scalar GREATEST function instead:

SELECT Invoice, GREATEST(FedEx, UPS, DHL) AS Tracking
FROM shipfile;
user_18632290
Related