multiple select in one statement

Question

This is hard to explain but i need to

- select id from customers
- foreach customer find the order with that id
-- foreach order (for that customer)
--- select product.name, product.max on order.productId=product.id

I dont know how to pull this data. I would need multiple separate selects? can i write a long single sql statement (';' counts as separate :P). How do i pull sub rows from a row......

Answer 1

Use joins:

SELECT c.id, o.id, p.name, p.max
  FROM Customers AS c
       JOIN Orders AS o ON c.id = o.customer
       JOIN Product AS p ON o.productID = p.id

More likely, you have an Orders table and an OrderItems table - just add an extra level of JOIN with appropriate conditions.

Note that I assume your table is 'Orders' to avoid conflict with the keyword 'ORDER'. I didn't assume that you disambiguated Product.Max from the MAX function. You might have to worry about what your DBMS allows (some are tolerant of the abuse of keywords; some are not).

---

A tolerant (over-tolerant) DBMS is IBM Informix Dynamic Server (IDS):

CREATE TABLE table(null INTEGER, integer CHAR(3), date DECIMAL(10,0), decimal DATE);

It works - it is horrid. But it avoids many problems with keywords added later.

Answer 2

What you'll get in a single, composite query, is repetitive field values for the single end of the one-to-many relationship. So if Foo:Bar :: 1:lots then as query that gets all the Bars and their Foos returns something like:

foo1 bar1
foo1 bar2
foo1 bar3
foo2 bar4
foo2 bar17
foo3 bar78

If you add in another table Baz that is Bar:Baz :: 1:lots, then you get something like this:

foo1 bar1 baz1
foo1 bar1 baz2
foo1 bar1 baz3
foo1 bar2 baz15
foo1 bar3 baz32
foo1 bar3 baz33
foo2 bar4 baz17
foo2 bar17 baz21
foo3 bar78 baz7

This is exactly what SQL is supposed to do. But it presents a bit of a problem when you're trying to output the answer in a pretty format. That's where your application code comes in. Set a $holder equal to the foo field and check to see if the new foo is different from the old one on each pass through your loop. Do the same with $bar. So:

$fooholder=NULL;
$barholder=NULL;
foreach ($result as $row_array) {
  if(empty($fooholder)) {
    $fooholder=$row_array['foo'];
    $barholder=$row_array['bar'];
  }

  if ($fooholder!=$row_array['foo']){ //new foo value, so next section
     //output $row_array['foo'] since it's new
     $fooholder=$row_array['foo'];
  }

  if ($barholder!=$row_array['bar']){ //new bar value, so next section
     //output $row_array['bar'] since it's new
     $barholder=$row_array['bar'];
  }

  //output $row_array['baz']

}