mysql select where in select as variable

Question

i have the following select statement

SELECT
    a.firstname name,
    m.date time
FROM
    account a
    LEFT JOIN memberships m ON a.id = m.account_id
WHERE
    a.is_active = 1

i am trying check and see if the user has an active membership (there could be more than one, but one is all i need as proof) and if so then set the variable true or the id or something, if not then false (or null or whatever)

here is what i have so far

SELECT
    a.firstname name,
    p.date time
    m.id active_membership
FROM
    account a
    LEFT JOIN profile p ON a.id = p.account_id
WHERE
    a.id IN (SELECT DISTINCT account_id FROM memberships WHERE active = 1)
AND
    a.is_active = 1

so the results i am trying to get would be name, time, active_membership foo, 10:00, null bar, 14:00, 223(id or anything)

i got everything working accept for the where in part...

You can always use a RIGHT JOIN to force only records that have an associated account to load. — tadman
– tadman, Commented Jun 20, 2014 at 0:14
@tadman that won't work, that will prevent the whole selection altogether — seesoe
– seesoe, Commented Jun 20, 2014 at 0:17

Barmar · Accepted Answer · 2014-06-20 00:24:55Z

2

You need another LEFT JOIN.

SELECT
    a.firstname name,
    p.date time,
    IF(m.account_id IS NULL, "No", "Yes") active_membership
FROM account a
LEFT JOIN profile p ON a.id = p.account_id
LEFT JOIN (SELECT DISTINCT account_id
           FROM memberships
           WHERE active = 1) m ON a.id = m.account_id
WHERE a.is_active = 1

answered Jun 20, 2014 at 0:24

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

tadman Over a year ago

Why can't you just LEFT JOIN memberships m ON a.id=m.account_id AND m.active=1?

Barmar Over a year ago

Because if someone has multiple memberships, that will create multiple rows in the result.

Barmar Over a year ago

You could use that, but then you need to add GROUP BY a.id at the end.

tadman Over a year ago

Nothing GROUP BY couldn't fix, but that's good observation.

Barmar Over a year ago

I prefer to use DISTINCT in the subquery, because it makes the cross product in the JOIN smaller.

Collectives™ on Stack Overflow

mysql select where in select as variable

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related