homepage Welcome to WebmasterWorld Guest from 54.166.122.65
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MySQL Multi Table Search
Searching records with attributes in multiple tables
wdavis10

5+ Year Member



 
Msg#: 4075916 posted 8:24 pm on Feb 7, 2010 (gmt 0)

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

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4075916 posted 12:13 am on Feb 9, 2010 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved