SQL query (and can RBDB do it?)
Hi,
I have a db with links "many to many". Let say I have machines made
with components. One machine is made with many components and one
component is used in many machines.
I did a db with 3 tables : machines (with id, name, ...), components
(with id, name, ...) and used (with machine_id, component_id).
Now how can I select all the components used by a machine (machine
#25) ?
I try "SELECT * FROM components, used WHERE used.machine_id=25 AND
used.component_id=components.id" but it don't work.
Actually I do it in 2 step :
1- "SELECT * FROM used WHERE used.machine_id=25", then with the
results (r1, r2, ...) I create a new query
2- "SELECT * FROM components WHERE components.id=r1 OR
components.id=r2 OR ..."
A better method, with one query, should exist. But I don't have enough
experience with SQL. Anyone has an idea ?
Perhaps a subquery (I never used one) :
"SELECT * FROM components WHERE id IN (SELECT used.component_id FROM
used WHERE used.machine_id=25)"
But I fear that RBDB can't use it.
--
Frdric Testuz