Writing SQL statements for tables with 2 branches

I have got a set of tables like the following

A ---- B_has_A ---- B ---- C ---- D_has_C ---- D
                           |
                           | ---- E_has_C ---- E

I am trying to write a query that allows me to JOIN A, B, D together, and then from that whole dataset, select the ones that are contained in D and E.

I have written a query that can find all the things from the "E" branch:

SELECT DISTINCT B.module, B.controller, B.action, B.object_only, B.description, privileges.object_id, A.A_id
 开发者_开发知识库 FROM C JOIN(
    B
    LEFT JOIN (
      B_has_A JOIN A
      ON A.A_id = B_has_A.A_id)
    ON (B.B_id = B_has_A.B_id))
  ON (C.B_id = B.B_id)
JOIN (E JOIN E_has_C)
  ON (E.E_id = E_has_C.E_id AND C.C_id = E_has_C.C_id)
  WHERE E.E_id IN (2,3)

I can also write one where I can find things from the "D" branch:

SELECT DISTINCT B.module, B.controller, B.action, B.object_only, B.description, privileges.object_id, A.A_id
  FROM C JOIN(
    B
    LEFT JOIN (
      B_has_A JOIN A
      ON A.A_id = B_has_A.A_id)
    ON (B.B_id = B_has_A.B_id))
  ON (C.B_id = B.B_id)
JOIN (D JOIN D_has_C)
  ON (D.D_id = D_has_C.D_id AND C.C_id = D_has_C.C_id)
  WHERE D.D_id IN (1, 2)

How do I write a query that can get me everything from both the "D" and "E" branches? I have tried using a join, but it doesn't work, and MySQL says there's a syntax error:

SELECT DISTINCT B.module, B.controller, B.action, B.object_only, B.description, privileges.object_id, A.A_id
  FROM C JOIN(
    B
    LEFT JOIN (
      B_has_A JOIN A
      ON A.A_id = B_has_A.A_id)
    ON (B.B_id = B_has_A.B_id))
  ON (C.B_id = B.B_id)

JOIN (D JOIN D_has_C)
  ON (D.D_id = D_has_C.D_id AND C.C_id = D_has_C.C_id)
  WHERE D.D_id IN (1, 2)

JOIN (E JOIN E_has_C)
  ON (E.E_id = E_has_C.E_id AND C.C_id = E_has_C.C_id)
  WHERE E.E_id IN (2,3)

Any pointers appreciated :)

EDIT: Final solution.

Thanks everyone. Lots of good ideas. My final solution was similiar to ypercube's "joins without using parenthesis:

SELECT ... long list
   FROM
     C    
   JOIN      
     B        
      ON C.B_id = B.B_id

   LEFT JOIN
     B_has_A
      ON B.B_id = B_has_A.B_id

   LEFT JOIN
     A
       ON A.A_id = B_has_A.A_id

   LEFT JOIN
      D_has_C
         ON C.C_id = D_has_C.C_id

   LEFT JOIN
      D
         ON D.D_id = D_has_C.D_id

   LEFT JOIN 
      E_has_C
         ON E.E_id = E_has_C.E_id

   LEFT JOIN
      E
         ON AND C.C_id = E_has_C.C_id

    WHERE E.E_id IN (2,3) OR D.D_id IN (1,2)

There's quite a few joins in there, so if anyone could shed some light as to how the performance of this compares to the subquery, it would be great! :)

---

You can also try removing parenthesis:

  SELECT ... long list 
  FROM
      C
    JOIN
      B
        ON C.B_id = B.B_id
    LEFT JOIN 
      B_has_A
        ON B.B_id = B_has_A.B_id
    JOIN 
      A
        ON A.A_id = B_has_A.A_id
    JOIN 
      D_has_C
        ON C.C_id = D_has_C.C_id
    JOIN
      D
        ON D.D_id = D_has_C.D_id
    JOIN
      E_has_C
        ON E.E_id = E_has_C.E_id
    JOIN
      E 
        ON AND C.C_id = E_has_C.C_id 
  WHERE E.E_id IN (2,3)
    AND D.D_id IN (1,2)

It depends on what the 1-to-many relationships are between your tables but you'll probably need a GROUP BY C.C_id and then you can remove the DISTINCT .

---

UPDATE

The "either from D or E table" suggest something with EXISTS:

  SELECT B.module, B.controller, B.action
       , B.object_only, B.description, A.A_id 
  FROM
      C
    JOIN
      B
        ON C.B_id = B.B_id
    LEFT JOIN 
      B_has_A
        ON B.B_id = B_has_A.B_id
    JOIN 
      A
        ON A.A_id = B_has_A.A_id
WHERE EXISTS
  ( SELECT *
    FROM     
      D
    JOIN 
      D_has_C
        ON D.D_id = D_has_C.D_id
    WHERE C.C_id = D_has_C.C_id
      AND D.D_id IN (1,2)
  ) 
  OR EXISTS                        <--- Note the "OR"  
  ( SELECT *
    FROM
      E 
    JOIN
      E_has_C
        ON E.E_id = E_has_C.E_id
    WHERE C.C_id = E_has_C.C_id
      AND E.E_id IN (2,3)
  )

---

You can combine the results of both queries with a UNION. A join can only be used to combine certain rows of tables, not whole result sets.

SELECT DISTINCT B.module, B.controller, B.action, B.object_only, B.description, privileges.object_id, A.A_id
  FROM C JOIN(
    B
    LEFT JOIN (
      B_has_A JOIN A
      ON A.A_id = B_has_A.A_id)
    ON (B.B_id = B_has_A.B_id))
  ON (C.B_id = B.B_id)
JOIN (E JOIN E_has_C)
  ON (E.E_id = E_has_C.E_id AND C.C_id = E_has_C.C_id)
  WHERE E.E_id IN (2,3)

UNION ALL

SELECT DISTINCT B.module, B.controller, B.action, B.object_only, B.description, privileges.object_id, A.A_id
  FROM C JOIN(
    B
    LEFT JOIN (
      B_has_A JOIN A
      ON A.A_id = B_has_A.A_id)
    ON (B.B_id = B_has_A.B_id))
  ON (C.B_id = B.B_id)
JOIN (D JOIN D_has_C)
  ON (D.D_id = D_has_C.D_id AND C.C_id = D_has_C.C_id)
  WHERE D.D_id IN (1, 2)

---

have you tried instead of:

JOIN (D JOIN D_has_C)
  ON (D.D_id = D_has_C.D_id AND C.C_id = D_has_C.C_id)
  WHERE D.D_id IN (1, 2)

JOIN (E JOIN E_has_C)
  ON (E.E_id = E_has_C.E_id AND C.C_id = E_has_C.C_id)
  WHERE E.E_id IN (2,3)

this:

JOIN (D JOIN D_has_C)
  ON ((D.D_id = D_has_C.D_id AND C.C_id = D_has_C.C_id) AND D.D_id IN (1, 2))

JOIN (E JOIN E_has_C)
  ON ((E.E_id = E_has_C.E_id AND C.C_id = E_has_C.C_id) AND  E.E_id IN (2,3))