Mysql query with joined subqueries gives no result

Question

i have a huge query:

SELECT stories.* 
  FROM stories stories 
   JOIN (SELECT tstories.uid 
           FROM stories tstories 
            JOIN tours_stories_mm mmt 
                ON tstories.uid = mmt.uid_foreign
            JOIN tours tours 
                ON tours.uid = mmt.uid_local
            JOIN tours_countries_rel tourcountryrel 
                ON tours.uid = tourcountryrel.tourid 
            JOIN countries tcountries 
                ON tcountries.uid = tourcountryrel.countryid
           WHERE tcountries.uid = ? 
                 AND tours.deleted = 0 
                 AND tours.hidden = 0 
                 AND tstories.deleted = 0 
                 AND tstories.hidden = 0) as tourstories 
   JOIN (SELECT cstories.uid 
           FROM stories cstories 
            JOIN individualtourcomponents_stories_mm mmc 
                ON (cstories.uid = mmc.uid_foreign) 
            JOIN individualtourscomponents components 
                ON (components.uid = mmc.uid_local) 
            JOIN individualtourcomponents_countries_rel componentcountryrel 
                ON (components.uid = componentcountryrel.componentid) 
            JOIN countries ccountries 
                ON (ccountries.uid = componentcountryrel.countryid) 
           WHERE ccountries.uid = ? 
                AND components.deleted = 0 
                AND components.hidden = 0 
                AND cstories.deleted = 0
                AND cstories.hidden = 0) as componentstories 
 WHERE stories.uid = componentstories.uid 
    OR stories.uid = tourstories.uid 
 GROUP BY stories.uid`

each of the subqueries work for themselves and if both subqueries have the same result, i get an result on the whole query. If only one subquery has a result i get nothing. Simplyfied it could be written like that:

SELECT stories.* 
  FROM stories 
   JOIN (SELECT * FROM table_a WHERE x=5) as table_a 
   JOIN (SELECT * form table_b where x=5) as table_b 
  WHERE stories.uid = table_a.uid OR stories.uid = table_b.uid 
 GROUP BY stories.uid

What am i doing wrong?

Answer 1

It seems like you should be using an OUTER JOIN instead of INNER JOIN to join your source tables.

Using the simplified query example, it might look like this:

SELECT stories.* 
  FROM stories 
   LEFT OUTER JOIN (SELECT * FROM table_a WHERE x=5) as table_a 
                ON stories.uid = table_a.uid
   LEFT OUTER JOIN (SELECT * form table_b where x=5) as table_b 
                ON stories.uid = table_b.uid 
 GROUP BY stories.uid

UPDATE

Based on your comment it sounds like you actually want to get the distinct union of the the two sub queries.

If that's the case, you could modify your original query like this:

  (SELECT tstories.*
     FROM stories tstories 
      JOIN tours_stories_mm mmt 
          ON tstories.uid = mmt.uid_foreign
      JOIN tours tours 
          ON tours.uid = mmt.uid_local
      JOIN tours_countries_rel tourcountryrel 
          ON tours.uid = tourcountryrel.tourid 
      JOIN countries tcountries 
          ON tcountries.uid = tourcountryrel.countryid
      WHERE tcountries.uid = ? 
            AND tours.deleted = 0 
            AND tours.hidden = 0 
            AND tstories.deleted = 0 
            AND tstories.hidden = 0
   )
UNION DISTINCT
  (SELECT cstories.*
     FROM stories cstories 
      JOIN individualtourcomponents_stories_mm mmc 
          ON (cstories.uid = mmc.uid_foreign) 
      JOIN individualtourscomponents components 
          ON (components.uid = mmc.uid_local) 
      JOIN individualtourcomponents_countries_rel componentcountryrel 
          ON (components.uid = componentcountryrel.componentid) 
      JOIN countries ccountries 
          ON (ccountries.uid = componentcountryrel.countryid) 
     WHERE ccountries.uid = ? 
           AND components.deleted = 0 
           AND components.hidden = 0 
           AND cstories.deleted = 0
           AND cstories.hidden = 0
  )