How can I retrieve records from one table and related records from another table in one query?

Question

I have two tables, items and itemmeta. items has item_id which links it to itemmeta which has itemmeta_id, item_id, meta_key and meta_value. I did this so if i needed to add a special flag or value to an item i didn't have to keep changing the items table.

Now i am paying the price for flexibility. I want to know if i can combine a query and some php into a better designed query.

first i do something like this:

SELECT * FROM items;

then while looping through those i do this for each in php:

$sql = "SELECT * FROM itemmeta WHERE item_id='".$item['item_id']."' ";
$meta_result = mysql_query($sql, $conn) or die(mysql_error());
$item['meta'] = array();
while( $meta=mysql_fetch_assoc($meta_result) ) {
  $item['meta'][$meta['meta_key']] = $meta['meta_value'];
}

Is it possible to do the second part using a subquery or something like that? i think the hard part would be keeping the same output. ex: $item['meta']['my_key'] = 'value'. I assume it would be more $item['meta'][0][0] = 'my_key' and $item['meta'][0][1] = 'value'.

Answer 1

select * from items, itemmeta where items.id = itemmeta.item_id

Answer 2

You want to use a JOIN:

SELECT i.*, im.* -- note you should really specify exactly which columns you want
FROM items AS i
JOIN itemmeta AS im
  ON i.item_id = im.item_id

Resources:

• Join syntax in the MySQL manual

Answer 3

SELECT * 
FROM items it
INNER JOIN itemmeta im
ON it.item_id = im.item_id
WHERE im.meta_key = 'value' 

Answer 4

$sql = "SELECT * FROM items JOIN itemmeta ON items.id = itemmeta.item_id";
$rs = mysql_query($sql);
$item['meta'] = array();
while($row = mysql_fetch_assoc($rs)) {
     $item['meta'][$row['meta_key']] = $row['meta_value']
}

should work

Answer 5

You could combine it all into one query, but it does complicates your client code. If you used the following query:

SELECT * FROM items
LEFT JOIN itemmeta USING (item_id)

then the client code would have to do loop through checking for when item_id changes. For the first row and rows where the item has changed you will need to do the following:

1. Create a new item object (or whatever you are using to represent them in your code)
2. Process the fields from the items table
3. Check if the same row has a non-NULL entry for the itemmeta fields and process those if so
4. Loop through subsequent records processing itemmeta fields until there is a new item

Answer 6

In addition to the specific answers given above, I suggest a good introductory book on SQL. If you're just now learning about joins, there are many other important elements of SQL that you should be learning. You've just scratched the surface.

I've always liked Allen Taylor's SQL For Dummies. Despite being a Dummies book, it's about as good an introduction to these concepts so important to SQL as you'll find.