Empty set while selecting data in MySQL relational database

Question

Hi!
I have some relational MySQL database with dozens of tables... My query is 1000 ft long and selects data from almost all the tables... I also have my 'main' table, and all other tables are referring to that one. When I enter one row in my main table and when I create all relations for that row in other tables my query works fine. However, if I enter one row in main table and nowhere else I get empty set. But I would like to get at least that information in my result. Or, of course, all information that is entered in other tables, regardless of the fact that some tables don't have information for that record (row)...

Thanks for help!

Update:

I don't use JOIN syntax and my query looks similar to this:

$query = "SELECT a.*, b.*, c.*, d.*, e.*, f.*";
$query .= " FROM a, b, c, d, e, f";
$query .= " WHERE a.aID = b.aID";
$query .= " AND b.bID = c.bID";
$query .= " AND b.bID = d.bID";
$query .= " AND b.bID = e.bID";
$query .= " AND b.bID = f.bID";

Answer 1

Your question is very vague for now, but most probably it is due to the fact you use INNER JOINs instead of the LEFT JOINs.

This query:

SELECT  *
FROM    main
JOIN    secondary
ON      secondary.main_id = main.id

will return nothing if secondary is empty.

This query:

SELECT  *
FROM    main
LEFT JOIN
        secondary
ON      secondary.main_id = main.id

will return at least one record for each record in main, replacing secondary fields with NULLs if there are no matching records in secondary.

Update:

Implicit JOIN syntax you use (enumerating the tables and providing the JOIN conditions in the WHERE clause) assumes INNER JOINs.

Assuming that a is the "main" table, rewrite your query as this:

SELECT  a.*, b.*, c.*, d.*, e.*, f.*
FROM    a
LEFT JOIN
        b
ON      b.aID = a.aID
LEFT JOIN
        с
ON      c.bID = b.bID
LEFT JOIN
        d
ON      d.bID = b.bID
LEFT JOIN
        e
ON      e.bID = b.bID
LEFT JOIN
        f
ON      f.bID = b.bID