mySQL group two INNER JOINs-CodePudding

I basically want to join the result of two INNER JOINs.

On this scheme I want to get the three arrows results combined.

I've tried INNER / LEFT combinations but it doesn't do the trick.

I think a nested request could be the solution but how ?

Thanks

CodePudding user response：

The answer was actually simple : UNION

SELECT t1.*
FROM 
(SELECT t1.*
FROM table1 t1 JOIN table2 t2 ON t2.id = i.client_id
UNION
SELECT t1.*
FROM t1 t1 JOIN table3 t3 ON t1.id = t3.client_id) as q1
;

CodePudding user response：

I'd use logic to express the condition T1.id exists in T2 or T3 more directly, and certainly avoid use of DISTINCT or UNION.

Options could be to use EXISTS directly (As this is immure to the possibility of duplication cause by 1:many joins)...

SELECT
  t1.*
FROM
  table1  t1
WHERE
  EXISTS (SELECT * FROM table2 t2 WHERE t2.t1_id = t1.id)
  OR
  EXISTS (SELECT * FROM table3 t3 WHERE t3.t1_id = t1.id)

Or to LEFT JOIN twice and then exclude unwanted rows. (This assumes that the joins are never 1:many, which would introduce duplication, and the unwanted need for a DISTINCT.)

SELECT
  t1.*
FROM
  table1  t1
LEFT JOIN
  table2  t2
    ON t1.id = t2.t1_id
LEFT JOIN
  table3  t3
    ON t1.id = t3.t1_id
WHERE
  t2.t1_id IS NOT NULL
  OR
  t3.t1_id IS NOT NULL

CodePudding user response：

try this one:

SELECT 
    t1.* 
  from table t1 
    JOIN table t2 ON t1.id = t2.t1_id
    JOIN table t3 ON t1.id = t3.t1_id