Re: [PHP-DB] Select distinct field won't return distinct value
I have a friend called GROUP_CONCAT, he may know what you want but he's only available since MySQL 4.1 2006/6/7, Blanton, Bob [EMAIL PROTECTED]: It is a Sybase vendor function but I was wondering if mysql had something comparable. I don't see anything in the manual. Maybe the subquery is the only way to go. -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 8:50 AM To: Blanton, Bob Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Select distinct field won't return distinct value Blanton, Bob wrote: 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 Pretty sure that's a sybase specific function. Nothing like that exists in mysql or postgresql. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Select distinct field won't return distinct value
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 [EMAIL PROTECTED] To: [EMAIL PROTECTED],php-db@lists.php.net,[EMAIL PROTECTED] Subject: RE: [PHP-DB] 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: niinlist(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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 7:48 PM To: php-db@lists.php.net Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] 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 echoa href=\page?cus=cus_id\costomer name/abr /; ___ 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Select distinct field won't return distinct value
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{ echoa href=\page?cus=cus_id\costomer name/abr /; }
Re: [PHP-DB] Select distinct field won't return distinct value
Perhaps you should fix your data model... but with your current set up, try: select cus_name, cus_id from customers group by cus_name order by cus_name asc -Brad Mohamed Yusuf wrote: 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{ echoa href=\page?cus=cus_id\costomer name/abr /; } -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] 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 echoa href=\page?cus=cus_id\costomer name/abr /; ___ 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
Re: [PHP-DB] Select distinct field won't return distinct value
I thank you all. problem solved using two queries as TQ mentioned. On 6/6/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: 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 echoa href=\page?cus=cus_id\costomer name/abr /; ___ 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
RE: [PHP-DB] Select distinct field won't return distinct value
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: niinlist(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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 7:48 PM To: php-db@lists.php.net Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] 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 echoa href=\page?cus=cus_id\costomer name/abr /; ___ 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Select distinct field won't return distinct value
Blanton, Bob wrote: 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: niinlist(serial_number) 000213909 B71-11649,B71-11657,B71-11650 000473750 BAF-3750-0001,BAF-3750-0002,BAF-3750-0003 000929062 2341 001139768 2207 Pretty sure that's a sybase specific function. Nothing like that exists in mysql or postgresql. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Select distinct field won't return distinct value
It is a Sybase vendor function but I was wondering if mysql had something comparable. I don't see anything in the manual. Maybe the subquery is the only way to go. -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 8:50 AM To: Blanton, Bob Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Select distinct field won't return distinct value Blanton, Bob wrote: 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 Pretty sure that's a sybase specific function. Nothing like that exists in mysql or postgresql. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php