SQL query (and can RBDB do it?) 
Author Message
 SQL query (and can RBDB do it?)


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

Sun, 19 Sep 2004 18:07:06 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Using CGI module with 'canned queries'

2. clipper query conver to sql query

3. Changing date format when doing MySQL query?

4. PIL+Tkinter query (I must be doing something _really_ stupid)

5. Query: What am I doing wrong?

6. SQL-DMO: anybody has done this?

7. Clarion Browse using SQL doing a SELECT ASC and then SELECT DESC

8. who is doing smalltalk/sql development?

9. MySQL query in PHP: Not query zero amounts also removes null amounts from query

10. extracting sql query result from ordered collection

11. re SQL Server Database queries

12. Case sensitivity in SQL query


Powered by phpBB® Forum Software