Have you try GROUP BY?
----- Original Message -----
From: Marc Swanson <[EMAIL PROTECTED]>
To: mysql list <[EMAIL PROTECTED]>
Sent: Monday, February 05, 2001 9:32 AM
Subject: usage of "distinct" in a table join query?
> Hello,
>
> I am trying to craft a query that will allow me to retrieve truly unique
> result elements. Here's my setup:
>
>
> table_1
> --------------------
> link_field varchar(20)
> field1 varchar(20)
> field2 varchar(20)
>
>
>
>
> table_2
> ----------------------
> link_field varchar(20)
> field3 varchar(20)
>
>
> Here's what I've been trying to do (unsuccessfully):
>
> "SELECT DISTINCT t1.*,t2.field3 FROM table_1 AS t1, table_2 AS t2 WHERE
> t1.link_field = t2.link_field ORDER BY t2.field3"
>
>
> imagine these contents of the two tables:
> table_1
> ---------------
> record 1:
> link_foo1
> foo1
> foo2
>
> record 2:
> link_foo2
> foo3
> foo4
>
> table_2
> ---------------
> record 1:
> link_foo1
> bar1
>
> record 2:
> link_foo1
> bar2
>
> record 3:
> link_foo2
> bar1
>
>
> What I want is a query that will return only 1 entry for each "link_foo"
> encountered in table #2 but order the result based on the contents of
> "field3" from table #2 and also return the corresponding data from table
#1.
> The query I'm running shows a number of records returned equal to the
number
> of records in table #2... although they ARE ordered correctly.
>
> What am I doing wrong? If there's no way to do what I want I suppose I
> could keep a list of each item encountered and skip over the next element
if
> it has already been encountered but that seems like a lot of extra
> unneccessary code.
>
> Thanks
>
> -Marc-
>
> #/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#
> #Marc Swanson | \|||/ #
> #MSwanson Consulting | /o o\ #
> #Phone: (603)868-1721 |-ooo----O----ooo-#
> #Fax: (603)868-1730 | Solutions in: #
> #[EMAIL PROTECTED] | 'PHP 'Perl #
> # | 'SQL 'C++ #
> # | 'HTML 'Sh/Csh#
> # http://www.mswanson.com | 'Javascript #
> #/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
