[OT} Any SQL Experts Here?

[William Muriithi]

Short,


One way and there is perhaps better, is to use a sub-select.  This is
> one way of doing it at least in T-SQL using MS SQL (what I'm more
> familiar with)
>
> SELECT  a.*,COUNT(b.*),(SELECT TOP 1 b2.FIELD FROM TABLEB b2 WHERE
> b2.KEY1 = a.KEY1 AND b2.KEY2 = a.KEY2)
> FROM    TABLEA a
> JOIN    TABLEB b
> ON      a.KEY1 = b.KEY1
> AND     a.KEY2 = b.KEY2
> GROUP BY a.KEY1,a.KEY2


This, as you mentioned above is a sub query - please correct me if I am
wrong - and sub queries perform badly especially on mysql. I have been
reading a couple of books in the subject in attempt to break into this field
and it looks like even oracle do try a lot to discourage sub queries.  Of
course if performance is not important, then this should not come into
consideration.



>
>
> -----Original Message-----
> From: owner-tlug-lxSQFCZeNF4 at public.gmane.org [mailto:owner-tlug-lxSQFCZeNF4 at public.gmane.org] On Behalf Of Stephen
> Sent: Thursday, May 28, 2009 9:32 AM
> To: TO Linux User Group
> Subject: [TLUG]: [OT} Any SQL Experts Here?
>
> 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.
>
> I will send the full detail to anyone willing to take a look.
>
> Thanks!
>
> Stephen
>
> --
> 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
> --
> 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
>
William
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://gtalug.org/pipermail/legacy/attachments/20090528/5999c21c/attachment.html>