Anybody? Bueller? Bueller?


<a> wrote in message news:TfWdnWlQENNvhr_ZnZ2dnUVZ_uudnZ2d@comcast.com. ..
>I have 3 tables, one main table, and two tables that reference the first
>table. the two reference tables may have more than one entry to the first
>one. I am able to use left joins to see all the data for the three tables,
>however, for a specific purpose, i only want to see the first instance of
>the 2 joined tables. Is there a way to do SELECT DISTINCT, but only have
>it distinct on the columns from the first table, or do a subquery to get
>the other two tables, using LIMIT 1. I'm using mysql 3.26. If this limits
>me in any way, let me know what i can do w/ 4.1 or 5.0. thank you.
>

"Bill Karwin" <bill@karwin.com> wrote in message
news:e04ro409hg@enews3.newsguy.com...
> <a> wrote in message news:jpadncqWGMmnR7jZRVn-sQ@comcast.com...
>>> But you must specify which one person and which one company do you want
>>> to display.
>>
>> It doesn't matter which one. I'm thinking if you didn't specify, it
>> would pick the first one entered.
>
> That is not an assumption you can make in relational databases. Unless
> you specify an order, the rows are returned in some
> implementation-dependant manner. This may vary by the storage engine
> (MyISAM vs. InnoDB), the version (4.0, 4.1, 5.0, etc.), the order in which
> the records were created or modified, or some other factors. Some query
> features (e.g. DISTINCT, GROUP BY, etc.) may have side-effects of applying
> an order to the rows.
>
> For the solution query I gave a few messages back, there must be some
> field in your people and locaiton tables by which you can determine which
> row is first, relative to the others. I.e. a field which you would
> ordinarily use in ORDER BY.
>
> Okay, so if it really doesn't matter which people and location rows you
> get in the query, here's
> a different solution that uses GROUP BY.
>
> SELECT c.company_name, p.person_name, l.location_name
> FROM company AS c
> LEFT OUTER JOIN person AS p on c.company_id = p.company_id
> LEFT OUTER JOIN location AS l on c.company_id = l.company_id
> GROUP BY c.company_id
>
> In MySQL, this will return one row for each distinct company, and with the
> company name, it will return one of the matching person and location
> entries, even if multiple persons and locations match the company. The
> values for person and location returned on that row will be unpredictable,
> dependant on some of the factors I described above.
>
> Note that this behavior of GROUP BY is particular to MySQL; other RDBMS
> brands may treat the query as illegal, since the query results are
> ambiguous.
>
Ah, it worked. the GROUP BY is exactly what i was looking for. thanks!