How to: MySQL How to retrieve record based on three properties?

Question

I am new to SQL and relational DBMS. I want to retrieve records from a relational database if they satisfy 3 given properties.

For example, if there is a table containing info about solids:

table_solid : |s_id|s_name|description|
table_width : |w_id|w_name|
table_height: |h_id|h_name|
table_length: |l_id|l_name|

where *_id are all primary keys.

I have 3 more tables for relating these tables with Foreign Keys.

table_s_h: |s_id|h_id|
table_s_w: |s_id|w_id|
table_s_l: |s_id|l_id|

Would you please explain how do I join these.

Thanks in advance.

                 --------- table_length
table_SOLID ----
                ----------- table_width

                ------------table_height

table_length contains valid lengths that solid can take (and similarly with other tables).

Answer 1

From a single table:

Select * 
FROM TABLE_NAME
WHERE table_width = SOME_VALUE3
AND table_height= SOME_VALUE2
AND table_length = SOME_VALUE3

Is that what you are looking for? Or are you trying to query multiple tables? If so try this:

Select * 
FROM TABLE_SOLID solid
Inner join table_width width on solid.w_id = width.w_id
inner join table_height height on solid.h_id = height.h_id
inner join table_length length on solid.l_id = length.l_id

This link may be of use to you http://dev.mysql.com/doc/refman/5.0/en/join.html

Answer 2

Your DB schema is not clear to me.

Are these four different tables? If yes, how are they linked up, ie how do you retrieve width, height, length for a given solid?

Or are those four columns in 1 table, identified by s_id?

Please clarify.

Answer 3

I hope I've understood your schema.

SELECT
    s.*
FROM
    table_solid AS s
WHERE
    s.s_id IN
(
(SELECT s_id FROM table_s_h INNER JOIN table_height USING (h_id) WHERE h_name = H)
INTERSECT
(SELECT s_id FROM table_s_w INNER JOIN table_width USING (w_id) WHERE w_name = W)
INTERSECT
(SELECT s_id FROM table_s_l INNER JOIN table_length USING (l_id) WHERE l_name = L)
);

OT: I don't know if that will work in MySQL and I don't care - I've added "mysql" tag to the question and you've removed it.