MySQL Multi Table Search

Searching records with attributes in multiple tables

[wdavis10]

First off, my setup:

Table 1 - Single Value Fields

Table 2 - Multiple Value Fields

When I fetch a record, I get the primary data from table 1 and then look through table 2 for any additional attributes.

How would I concat a single sql statement that would compare these two and return the ID from Table 1?

Would it look like "SELECT * FROM table WHERE field = value AND SELECT * FROM table2 WHERE field2 = value2"

I read up on Join and Union , but I can't tell how exactly to use it the way I need.


Thanks for the help!

[rocknbil]

Since no one has answered, I'll offer up "it depends." :-) It depends on what your select statement looks like when you:

look through table 2 for any additional attributes.

I don't know that you'll need concat, but you will likely need some kind of join. Simplest example:

products
id|product_id|sku|title|description
1|1234|WID01|Widget One|A widget which HAS options.
1|1235|WID02|Widget Two|A widget which DOES NOT have options.

options
id|option_id|product_id|option_name|options_value
1|431|1234|Color|Green
2|432|1234|Color|Red
3|433|1234|Size|Small
4|434|1234|Size|Large

Task: give me all the products and options for item 1234.

select * from products, options where products.product_id=1234 and products.product_id=options.product_id;

This will return four rows, like

1|1234|WID01|Widget One|A widget which HAS options.|1|431|1234|Color|Green

1|1234|WID01|Widget One|A widget which HAS options.|2|432|1234|Color|Red

1|1234|WID01|Widget One|A widget which HAS options.|3|433|1234|Size|Small

1|1234|WID01|Widget One|A widget which HAS options.|4|434|1234|Size|Large

Which "kinda" works, except you likely only need a single copy of the product data. But watch what happens here:

Task: give me all the products and options for item 1235.

select * from products, options where products.product_id=1235 and products.product_id=options.product_id;

--> 0 rows in set <--

Because you are joining the two tables on product id, and since there is no matching product id 1235 in options, there are no results.

So to get both, you want a left join. A left join will return results whether the joined rows are null, or not.

select * from products where products.product_id=1235 left join options on products.product_id=options.product_id;

1|1235|WID02|Widget Two|A widget which DOES NOT have options.|NULL|NULL|NULL|NULL|NULL

There are other ways to join, using explicit inner and outer joins, or you can nest a select inside a select, or more complicated methods testing fields for null in the joined table . . . but this is a good starting point.

I'll also add, if the four rows of the "base" product data are undesirable, it may very well be your nested select is the best choice, even if it means a second query to the db. There are some situations that warrant this.

select * from products;
while (results are being returned) {
-- > select * from options where product_id=$this_row_roduct_id;
-- > while ( option results are being returned ) {
---- > do something, like output an option select list
-- > }
}

So for item 1235, there are no results from the nested query, but for 1234, there are.