[OT} Any SQL Experts Here?

[Christopher Browne]

On Thu, May 28, 2009 at 9:31 AM, Stephen <stephen-d-bJEeYj9oJeDQT0dZR+AlfA at public.gmane.org> wrote:
> Any SQL experts here?
>
> I am trying to enhance my web site and I can’t come up with the query that I
> need.
>
> Briefly, I have table A and table B
>
> The unique primary keys in table A, are present in a column in table B. It
> is a one to many relationship.
>
> Currently, I select all of table A, plus the row count of table B,
> indicating how many table B rows have the primary key of the table A row.
>
> Now, I want to add an additional field from column B, for the first matching
> row. But I need to something equivalent to a LIMIT 1 on the table B rows, so
> the final result set has the same number of entries as rows in table A.
>
> I am using the PDO class in PHP against a MySQL database.

PDO doesn't matter here; what version of MySQL(an Oracle Trademark)
very well might.

This seems like a pretty ordinary JOIN + GROUP BY.

something like:

select a.*, count(*) as num_rows, min(b.some_column) as first_match
from a, b
where a.pk = b.pk
group by a.pk, a.this, a.that, a.other   -- need to group by EVERY column in A

Depending on version, you might need to use an explicit join instead.

It's conceivable that the last line might be replaced by "group by
a.*", though that seems somewhat unlikely.
-- 
http://linuxfinances.info/info/linuxdistributions.html
Bette Davis  - "Brought up to respect the conventions, love had to end
in marriage. I'm afraid it did." -
http://www.brainyquote.com/quotes/authors/b/bette_davis.html
--
The Toronto Linux Users Group.      Meetings: http://gtalug.org/
TLUG requests: Linux topics, No HTML, wrap text below 80 columns
How to UNSUBSCRIBE: http://gtalug.org/wiki/Mailing_lists