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

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).

From stackoverflow

• 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

  TheMachineCharmer : Yes, I want to query multiple tables.

• 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.

• 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.