Nested queries with mysql

Anon

hi everyone!

i need to do this query from a php page:

SELECT prestazioni., Attivita. FROM prestazioni INNER JOIN (Attivita INNER JOIN FrameworkPrestazioni ON Attivita.ID_Attivita = FrameworkPrestazioni.ID_Attivita_Framework) ON prestazioni.id_prestazione = FrameworkPrestazioni.ID_Prestazione_Framework WHERE prestazioni.id_prestazione=2

but mysql doesnt support nested queries:
how can i do?

thnx anyway

Anon

You can't - not with MySQL.

Anon

You will only be able to do this by multiple queries and building temporary tables until you get to the end result.

Anon

But don't give up all hope--many times you can transform
an inner join into an equivalent selection just by linking
the two tables. You probably can't do this with your
entire query, but you might end up with fewer queries
than tables.

Anon

Yes, that is the solution!.
Make temp tables, with the columns you have in your query and built it manually.

Anon

hi,
it seems like i'd need to perform nested queries to delete rows in table A which have a column value equivalent to a table B column value... alas, temporary tables could help here! how do you guys handle this scenario?

antonio

smook

Temp tables are really messy... I know I've built them in the past to handle mysql. Its WAY easier to do it in php either by using arrays or just by a little ingenuity. Try this:

select id from tableb where data = criteria

then:

for each id in that query build a string:

$deletestring =(id1,id2,id3,id4)

then write your delete:

delete from tablea where id in $deletestring

Of course id is the foreign key in tablea that matches tableb.

thats pretty much what any query nesting in any database does anyway.