CODEWITHSUNDEEP
mysqlvariablessubquery
andy
andy

Reputation: 2399

Saving a query in a variable then adding a where clause?

I have a bit of a problem with this query. It works great, but I'm a fan of DRY and this looks like it could easily be improved:

SELECT
    users.*,
    (
        SELECT user_information.value
        FROM users
        LEFT JOIN user_information
        ON user_information.userid = users.id
        WHERE user_information.name = 'account_name'
    ),
    (
        SELECT user_information.value
        FROM users
        LEFT JOIN user_information
        ON user_information.userid = users.id
        WHERE user_information.name = 'account_code'
    ),
    (
        SELECT user_information.value
        FROM users
        LEFT JOIN user_information
        ON user_information.userid = users.id
        WHERE user_information.name = 'account_id'
    ),
WHERE ...

The part:

SELECT user_information.value
FROM users
LEFT JOIN user_information
ON user_information.userid = users.id

Is exactly the same in each subquery (there will be about 10 sub queries in future) but the where clause changes each time. Would it be possible to save that in a variable, and then use it while appending that different where clause it each one in mysql?

Upvotes: 0

Views: 72

Answers (1)

eggyal
eggyal

Reputation: 125865

You can join the user_information table to your query and group the results:

SELECT   users.*,
         GROUP_CONCAT(IF(i.name = 'account_name', i.value, NULL)),
         GROUP_CONCAT(IF(i.name = 'account_code', i.value, NULL)),
         GROUP_CONCAT(IF(i.name = 'account_id'  , i.value, NULL))
FROM     users LEFT JOIN user_information AS i ON i.userid = users.id
WHERE    ...
GROUP BY users.id

Upvotes: 1

Related Questions