List:General Discussion« Previous MessageNext Message »
From:Andy Leung Date:March 26 2000 5:28am
Subject:Tricky SQL query dealing with multiple selections
View as plain text  
Hi,

I have a (somewhat simplified) table called Items:

 -----------------------------------------
|   item_id   | model_no  |   location   |
 -----------------------------------------
|    0001     |    AA     |   USA        |
 -----------------------------------------
|    0001     |    AA     |   UK         |
 -----------------------------------------
|    0001     |    AA     |   Japan      |
 -----------------------------------------
|    0002     |    AA     |   USA        |
 -----------------------------------------
|    0002     |    AA     |   China      |
 -----------------------------------------
|    0003     |    AA     |   Chile      |
 -----------------------------------------
|    0003     |    AA     |   France     |
 -----------------------------------------
|    0004     |    FS     |    USA       |
 -----------------------------------------
|    0004     |    FS     |     UK       |
 -----------------------------------------

As you can see, each item_id has one model_no but can have multiple locations.

I want to find the item_id that has a certain model_no and a certain
location. For example:

select item_id
from Items
where model_no = "AA"
and location = "USA"

would give me:

 --------------
|   item_id   |
 --------------
|   0001      |
 -------------- 
|   0002      |
 --------------

Now what I *really* want is a table that shows each selected item_id
together with its model_no and a *list* of its (possibly multiple)
locations. For the above example, I want a table that looks like:

 --------------------------------------------------
|   item_id   | model_no  |       location        |  
 --------------------------------------------------
|   0001      |    AA     |    USA, UK, Japan     |
 --------------------------------------------------
|   0002      |    AA     |      USA, China       |
 --------------------------------------------------

So basically I want to "group by" item_id and then "collapse" each item_id's
multiple locations into a single list of locations.

Does anyone know how to do it in SQL? Or is it impossible to do it in SQL
and I'd need to do it in (in my case) PHP? How?

Any help is greatly appreciated. Thanks!

Andy


Thread
Tricky SQL query dealing with multiple selectionsAndy Leung26 Mar
  • Re: Tricky SQL query dealing with multiple selectionssinisa26 Mar
  • Re: Tricky SQL query dealing with multiple selectionsPeter J. Schoenster26 Mar