Selecting multiple tables with matching columns but only displaying unique results

Question

Here is my existing SQL query

SELECT DISTINCT
  par.WorkOrder,
  l.Address,
  l.Subdivision,
  eml.MailDate,
  lp.WorkDate
FROM
  parsed AS par
  LEFT JOIN emails AS eml      ON eml.EmailID = par.OriginID
  LEFT JOIN list AS l          ON par.WorkOrder = l.WorkOrder
  LEFT JOIN locateparsed AS lp ON par.WorkOrder = lp.WorkOrder
WHERE
  par.Status != 0 
  AND l.Completed = 0
  AND (lp.WorkDate IS NOT NULL OR eml.MailDate IS NOT NULL)
GROUP BY
  par.WorkOrder

Right now it will only select WorkOrder matches from the parsed table. How can I also have it select WorkOrder from the locateparsed table and combine them? The best way to describe this would be something like

SELECT DISTINCT par.WorkOrder OR lp.WorkOrder FROM....

UPDATE:

Here is the completed query I am using. I just need to sort out an issue with dates now of results that come up uncompleted that were mistakenly entered.

SELECT
  temp.WorkOrder,
  l.Address,
  l.Subdivision,
  eml.MailDate,
  temp.WorkDate
FROM (
    (SELECT par.WorkOrder, lp.WorkDate, par.OriginID, par.Status
    FROM parsed AS par
    LEFT JOIN locateparsed AS lp ON par.WorkOrder = lp.WorkOrder)
    UNION ALL
    (SELECT lp.WorkOrder, lp.WorkDate, par.OriginID, '1' AS Status
    FROM parsed AS par
    RIGHT JOIN locateparsed AS lp ON par.WorkOrder = lp.WorkOrder
    WHERE par.WorkOrder IS NULL)
    ) AS temp
  LEFT JOIN emails AS eml      ON eml.EmailID = temp.OriginID
  LEFT JOIN list AS l          ON temp.WorkOrder = l.WorkOrder
WHERE
  (temp.Status != 0 OR eml.Parsed IS NULL)
  AND l.Completed = 0
  AND (temp.WorkDate IS NOT NULL OR eml.MailDate IS NOT NULL)
GROUP BY
  temp.WorkOrder

Can you provide sample data and desired results? As written, your `outer join` is negated for most tables with the `where` criteria. Perhaps you're looking to use `union` -- difficult to say without sample data... — sgeddes, Nov 23 '15 at 22:46
Seeing the output would be pretty confusing so ill try to explain best whats happening. I have a parsed table and a locatesparsed table, both containing a "WorkOrder" column. The way it is setup now it selects from the parsed table and then joins a couple other tables. The issue I am facing is where a WorkOrder result is in the locatesparsed table but not in parsed therefore its negated and wont be shown in my results. Thats why I am looking for some sort of OR statement in the SELECT. — Taylor Reed, Nov 23 '15 at 22:58
Maybe FULL OUTER JOIN or this http://stackoverflow.com/a/6744815/1151408 can help you — Yuri, Nov 23 '15 at 23:13
If you stick DISTINCT and GROUP BY in the same query, there's a fair chance you're heading off track — Strawberry, Nov 23 '15 at 23:14

Tomaso Albinoni · Accepted Answer · 2015-11-24T16:04:52.460

SELECT DISTINCT par.WorkOrder and GROUP BY par.WorkOrder will accomplish the same thing, so you don't need to use both.

As Yuri suggested, use a full outer join:

SELECT
  temp.WorkOrder,
  l.Address,
  l.Subdivision,
  eml.MailDate,
  temp.WorkDate
FROM (
    (SELECT par.WorkOrder, lp.WorkDate, par.OriginID, par.Status
    FROM parsed AS par
    LEFT JOIN locateparsed AS lp ON par.WorkOrder = lp.WorkOrder)
    UNION ALL
    (SELECT lp.WorkOrder, lp.WorkDate, par.OriginID, '1' AS Status
    FROM parsed AS par
    RIGHT JOIN locateparsed AS lp ON par.WorkOrder = lp.WorkOrder
    WHERE par.WorkOrder IS NULL)
    ) AS temp
  LEFT JOIN emails AS eml      ON eml.EmailID = temp.OriginID
  LEFT JOIN list AS l          ON temp.WorkOrder = l.WorkOrder
WHERE
  temp.Status != 0 
  AND l.Completed = 0
  AND (temp.WorkDate IS NOT NULL OR eml.MailDate IS NOT NULL)
GROUP BY
  temp.WorkOrder

Note: In order to include the results from locateparsed, I added '1' AS Status, otherwise the WHERE condition would exclude all rows from locateparsed without a matching WorkOrder in parsed.

Sorry, `lp` should be `temp` of course. But that `WHERE` condition kind of defeats the purpose of the outer join. If you gave a table with desired output I could help better. — Tomaso Albinoni, Nov 24 '15 at 16:08
Thanks, it's working like I wanted from what I can tell. The only issue I am facing now has to do with dates so I'll try to sort that myself. — Taylor Reed, Nov 24 '15 at 22:16

Selecting multiple tables with matching columns but only displaying unique results

1 Answers1