Select distinct field won"t return distinct value

------=_Part_20981_26182256.1149605779670
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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"
";
}

------=_Part_20981_26182256.1149605779670--

Re: 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{
> echo"
";
> }
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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) . "
\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"
";



___________________________________________________________
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: Select distinct field won"t return distinct value

------=_Part_22638_15812086.1149610869523
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I thank you all. problem solved using two queries as TQ mentioned.

On 6/6/06, tg-php@gryffyndevelopment.com
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) . "
\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"
";
>
>
>
> ___________________________________________________________
> 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
>
>

------=_Part_22638_15812086.1149610869523--

RE: 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:
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@gryffyndevelopment.com
[mailto:tg-php@gryffyndevelopment.com]=20
Sent: Tuesday, June 06, 2006 7:48 PM
To: php-db@lists.php.net
Cc: myainab@gmail.com
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 =3D $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][] =3D $cus_id;
}

foreach ($cus_arr as $cus_name =3D> $cus_idarr) {
echo "$cus_name as ids:" . implode(", ", $cusidarr) . "
\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

=3D =3D =3D Original message =3D =3D =3D

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"
";



___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

--=20
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: 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: 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:dmagick@gmail.com]=20
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?
>=20
> Query:
> SELECT distinct niin, list(serial_number) FROM
> fmds.maintenance_equipment
> group by niin
> order by niin
>=20
> 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=20
in mysql or postgresql.

--=20
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: Select distinct field won"t return distinct value

------=_Part_5282_28509287.1149696140880
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 :
>
> 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:dmagick@gmail.com]
> 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
>
>

------=_Part_5282_28509287.1149696140880--

RE: 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"
>To:
>,,
>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:
>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@gryffyndevelopment.com
>[mailto:tg-php@gryffyndevelopment.com]
>Sent: Tuesday, June 06, 2006 7:48 PM
>To: php-db@lists.php.net
>Cc: myainab@gmail.com
>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) . "
\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"
";
>
>
>
>___________________________________________________________
>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

RE: Select distinct field won"t return distinct value

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.ht ml

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"
>To:
>,,
>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:
>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@gryffyndevelopment.com
>[mailto:tg-php@gryffyndevelopment.com]
>Sent: Tuesday, June 06, 2006 7:48 PM
>To: php-db@lists.php.net
>Cc: myainab@gmail.com
>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) . "
\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"
";


___________________________________________________________
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