Select all rows present in Table A OR Table B

Question

I would like to get the resulting row if the foreign id present in Table A or in Table B.

I have 3 tables: events, pdf_results and live_Results. I would like to select all events with pdf_results OR live_Results.

I have tried following query but it's not working when results are available in both tables.

SELECT
  events.*,
  live_results.id,
  pdf_results.id
FROM events
LEFT JOIN pdf_results
  ON pdf_results.eventId = events.id
LEFT JOIN live_Results
  ON live_Results.eventId = events.id;

Here is SQL Fiddle Demo

Hi. "get the resulting row if the foreign id present in Table A or in Table B" is not clear. "select all events with pdf_results OR live_Results" is also not clear, but is maybe closer to making sense. Also "it's not working" and "when results are available in both tables" are not clear. Please use enough words & phrases & sentences to say what you mean. Also please read & act on [mcve]. That includes DDL, DML, input, output & desired output. (And when you have an sqlfiddle link please also have relevant code in your question.) — philipxy, Nov 02 '17 at 02:15
`union` returns rows that are in a left table *or* are in a right table. `join` returns rows whose subrows are in a left table *and* in a left table. So a (`join` with) `on` or `where` can only return rows with this `or` that *and* the appropriate subrows are in each table. `left join` returns rows returns rows that are in an `inner join` *or* that don't and have a subrow in a left table and have `null` for each column of a right table. So again some queries of a special form with "or" and/or "and" can be phrased with it. https://stackoverflow.com/a/33952141/3404097 — philipxy, Nov 02 '17 at 02:19

score 0 · Answer 1 · answered Nov 02 '17 at 00:59

0

How about just using exists?

SELECT e.* 
FROM events e
WHERE EXISTS (SELECT 1 FROM pdf_results pr WHERE pr.eventId = e.id) OR
      EXISTS (SELECT 1 FROM live_Results lr WHERE lr.eventId = e.id);

answered Nov 02 '17 at 00:59

Gordon Linoff

1,242,037
58
646
786

Would this still display duplicate results if event has pdf+live result? – giolliano sulit Nov 02 '17 at 01:04
@giollianosulit . . . Of course not. Conditions in the `where` clause do not duplicate results. – Gordon Linoff Nov 02 '17 at 01:05
@giollianosulit Yes. It should give 2 rows if result is available in pdf and in live. Thank you for mentioning. – Mike Ross Nov 02 '17 at 01:05
@GordonLinoff - Yep thought so, was just asking as it looks like he wanted 2 results – giolliano sulit Nov 02 '17 at 01:06
@MikeRoss . . . I'm confused. Do you want two rows or not? You question is complaining about duplicates, so it certainly seems that you do not want multiple rows. – Gordon Linoff Nov 02 '17 at 01:14
@GordonLinoff I did not think of that scenario until Giolliano pointed it out. I have updated my question. – Mike Ross Nov 02 '17 at 01:16
1

@MikeRoss Where does it say that in the updated question? – Barmar Nov 02 '17 at 01:59

Barmar · Answer 2 · 2017-11-02T02:00:57.220

You can join with a UNION query:

SELECT e.*, r.result_type
FROM events AS e
JOIN (SELECT eventId, 'pdf' AS result_type FROM pdf_results
      UNION ALL
      SELECT eventId, 'live' AS result_type FROM live_results) AS r
ON e.id = r.eventId

Adding the result_type column allows us to know which results table it matched, and we'll get two rows if it matches both.

You could also use your original query, and add:

WHERE pdf_results.eventId IS NOT NULL OR live_results.eventId IS NOT NULL

You won't get two rows when it matches twice, but you'll be able to tell from the two eventId columns which results table it was found in.

Select all rows present in Table A OR Table B

2 Answers2