complex mysql searches

NZ_Kiwis

Is it possible to do a search filtering variables from several tables? I know you can left and right join but how about 4-5 tables?

I have a complex project where I'll have critical information in table1 but to find the most suitable result I need to filter all the results through data from other tables.

One example is, in table1 I have startdate and enddate, my initial result will search for results which are between these dates, but I have another table (table2) with a range of "blacklisted" dates, how can I filter my results so I don't end up with a row from table1 if table2 has my date range blacklisted.

Stand alone the above would be a left join but there is potential to have table3 and table4 also.

Can you load table1 results into a dataset or something similar and run that though another search? I have no idea if such a procedure exists but am looking for a solution.

NogDog

Possibly something like this?

SELECT <column list> FROM table_1
WHERE date_col BETWEEN :start_date AND :end_date
AND date_col NOT IN (
    (SELECT date_col FROM table_2 WHERE <whatever the conditions are>)
    UNION
    (SELECT date_col FROM table_3 WHERE <whatever the con
    UNION
    (SELECT date_col FROM table_4 WHERE <whatever the conditions are>)
)

NZ_Kiwis

Can you use results from the first select to use in the where clause in the subsequent selects?

NogDog

I believe you can -- at least I've done that in PostgreSQL, anyway.

traq

You can also run multiple queries (my understanding is that PDO can only run multi queries via [man]pdo.exec[/man] (and therefore, only when you don't need to return a result), but I may be wrong - of course, [man]MySQLi.multi-query[/man] works just fine), set variables, and/or use temporary tables.