Yeah I did.. just didn't have time when I posted that message. I was mis-remembering what cross-tab queries did. They're not really what the original poster was looking for. For anyone interested in crosstab queries (aka "pivot" queries or tables or reports), here's an example of how to do it in MySQL: http://dev.mysql.com/tech-resources/articles/wizard/index.html But again, not what the original poster was looking for. -TG = = = Original message = = = google 'cross tab queries'...there is a php/mysql example (sorry travelling and don't have the link) on who to create a cross table query which is what you are looking for Bastien >From: "Blanton, Bob" <BlantonB@xxxxxxxxxxxxx> >To: ><tg-php@xxxxxxxxxxxxxxxxxxxxxx>,<php-db@xxxxxxxxxxxxx>,<myainab@xxxxxxxxx> >Subject: RE: Select distinct field won't return distinct value >Date: Tue, 6 Jun 2006 23:44:22 -0400 > > >I'm just learning MySQL so don't know all the syntax. There is a "LIST" >function in Sybase Adaptive Server Anywhere which would do that. Is >there an equivalent function in MySQL? > >Query: >SELECT distinct niin, list(serial_number) FROM >fmds.maintenance_equipment >group by niin >order by niin > >Output: >niin~~list(serial_number) >000213909~B71-11649,B71-11657,B71-11650 >000473750~BAF-3750-0001,BAF-3750-0002,BAF-3750-0003 >000929062~2341 >001139768~2207 > > > > >-----Original Message----- >From: tg-php@xxxxxxxxxxxxxxxxxxxxxx >[mailto:tg-php@xxxxxxxxxxxxxxxxxxxxxx] >Sent: Tuesday, June 06, 2006 7:48 PM >To: php-db@xxxxxxxxxxxxx >Cc: myainab@xxxxxxxxx >Subject: Re: Select distinct field won't return distinct value > >Correct me if I'm wrong, but it sounds like you have something like >this: > >123 Joe >124 Joe >125 Sue >126 John >127 Joe >128 Frank >129 Sue > >And you want to output something like: > >Joe 123, 124, 127 >Sue 125, 129 >John 126 >Frank 128 > >But what you're getting is: > >Joe 123 >Joe 124 >..etc > >You have two ways you can solve this: > >1. Do two SQL queries: > >SELECT DISTINCT cus_name FROM customers > >while ($result) // forgive the pseudo-code > SELECT cus_id FROM customers WHERE cus_name = $result['cus_name'] > while ($result2) > echo $output; > > > >Or.. > >2. Collect data into an array and process 'distinctness' on output > >SELECT cus_name, cus_id FROM customers > >while ($result) > $cus_arr[$cus_name][] = $cus_id; > > >foreach ($cus_arr as $cus_name => $cus_idarr) > echo "$cus_name as ids:" . implode(", ", $cusidarr) . "<br>\n"; > > >There may be some tricky ways in SQL to get the data the way you want >it, but ultimately it's not worth the bending over backwards for (do I >remember right that you can do it with crosstab queries? don't even >know if MySQL will do those properly). Easier just to do it with one >of the methods above. > >Good luck! > >-TG > >= = = Original message = = = > >I want select distinct field and return value of that field, but I have >problem which is: select distinct returns duplicate value. eg, I wan >select >distinct customer name and id from the customer table. one customer may >have >different cus_ids since cus_ids are auto increment and depend on the >purchased items. so what I want is to select distinct customer name so >that >I can print customer name and customer id once. > >here is algorithm; > >select distinct cus_name, cus_id from customers order by cus_name asc >While row is not empty do >echo"<a href=\"page?cus=cus_id\">costomer name</a><br />"; ___________________________________________________________ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
