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

Reply via email to