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 | +----------------------------------------------------------------------+