addressalign-toparrow-leftarrow-rightbackbellblockcalendarcameraccwcheckchevron-downchevron-leftchevron-rightchevron-small-downchevron-small-leftchevron-small-rightchevron-small-upchevron-upcircle-with-checkcircle-with-crosscircle-with-pluscontroller-playcrossdots-three-verticaleditemptyheartexporteye-with-lineeyefacebookfolderfullheartglobe--smallglobegmailgooglegroupshelp-with-circleimageimagesinstagramFill 1launch-new-window--smalllight-bulblinklocation-pinm-swarmSearchmailmessagesminusmoremuplabelShape 3 + Rectangle 1ShapeoutlookpersonJoin Group on CardStartprice-ribbonprintShapeShapeShapeShapeImported LayersImported LayersImported Layersshieldstartickettrashtriangle-downtriangle-uptwitteruserwarningyahoo

Re: [php-49] MariahDB Sub-selects, which is saner?

From: Jd D.
Sent on: Wednesday, April 3, 2013 1:05 PM

Thanks Mike

On Apr 2,[masked]:05 PM, "Mike Blaszczak" <[address removed]> wrote:
They're different.

The first statement is a subselect. For each row coming back from sm_accounts, you'll get business_email and sm_uid. You'll also execute the statement you provided in the select list named as admin_uid.  It's possible that statement returns no rows; in that case, the value of the computed admin_uid column is NULL. If there are multiple rows that would come back from the subselect statement, the statement fails.

The second statement is an inner join. If no row matches the predicate you supply, then no row is returned from either table. If multiple rows match, the product of all the matches is returned in the result set.

Does MariaDB not support ANSI join syntax? You want to use the JOIN operator to write the join, like this:

SELECT u.business_email, u.sm_uid AS admin_sm_uid, m.sm_uid
  FROM sm_accounts m
  JOIN sm_accounts u ON m.admin_id = u.id;

Since the statements are different, you'll need to decide which is the behavior you desire.

Hope that helps.

... Mike



On Tue, Apr 2, 2013 at 2:40 PM, Jd Daniel <[address removed]> wrote:
I got two ways to do pretty much the same thing, which one is saner to you guys, or maybe these are diff and I just don't realize it?

SELECT business_email, (SELECT m.sm_uid FROM sm_accounts m WHERE m.admin_id = id) AS admin_uid, sm_uid FROM sm_accounts u;

SELECT u.business_email, u.sm_uid AS admin_sm_uid, m.sm_uid FROM sm_accounts m, sm_accounts u WHERE m.admin_id = u.id;



--
Jd Daniel || ERADO
Senior Applications Architect
7901 Delridge Way SW #36D, Seattle
Professional Profile
C.    [masked]







--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Jd Daniel ([address removed]) from The Seattle PHP Meetup Group.
To learn more about Jd Daniel, visit his/her member profile
Set my mailing list to email me As they are sent | In one daily email | Don't send me mailing list messages

Meetup, POB 4668 #37895 NY NY USA 10163 | [address removed]





--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Mike Blaszczak ([address removed]) from The Seattle PHP Meetup Group.
To learn more about Mike Blaszczak, visit his/her member profile
Set my mailing list to email me As they are sent | In one daily email | Don't send me mailing list messages

Meetup, POB 4668 #37895 NY NY USA 10163 | [address removed]

Our Sponsors

  • PluralSight

    PluralSight subscriptions for developer training

  • O'Reilly

    Disc Code: PCBW is good for 40% off print and 50% off ebooks and videos

  • JetBrains PhpStorm

    Occasional free licenses to raffle off at meetups

  • DynaTrace

    Find yourself, promote yourself, stay true to yourself.

People in this
Meetup are also in:

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy