From: <sinisa
Date: March 26 2000 11:13am
Subject: Re: Tricky SQL query dealing with multiple selections
List-Archive: http://lists.mysql.com/mysql/32266
Message-Id: <14557.61679.391400.439957@localhost.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Andy Leung writes:
 > 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
 > 

Hi!

Any operation over a relation produces a new relation as it's
output. Relation is an entity that can contain only scalar values, so
you will need to make a program to produce the above output.

Regards,

Sinisa

+----------------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___     ==  mysql@stripped            |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sinisa Milivojevic          |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|     mailto:sinisa@stripped     |
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Larnaka, Cyprus             |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                             |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                            |
|             /*/             \*\                Developers Team       |
+----------------------------------------------------------------------+