MYSQL query/subquery merging

Question

I have two queries below that i'm sure can be combined in to one but i'm not sure how.

I want to get the first_name and email columns for every user, but I also want to get the associated pet_type and pet_breed from the first advert record if they have one. The advert record only contains a breed_id which is then linked to pet_breeds table to get the pet_type and pet_breed. I dont need to return the breed_id in the results, just the pet_type and pet_breed. If no associated advert record exists for a user then I want the pet_breed and pet_type to return the text 'None'.

SELECT users.first_name, users.email,
      (SELECT adverts.breed_id AS breed 
      FROM adverts 
      WHERE adverts.user_id = users.user_id 
      LIMIT 1)
FROM users

Second Query

SELECT pet_type, pet_breed
FROM pet_breeds
WHERE breed_id = breed

John Woo · Accepted Answer · 2012-11-22 15:07:43Z

1

use LEFT JOIN and COALESCE (to show NONE instead of NULL)

SELECT  a.first_name, a.email,
        COALESCE(c.pet_type, 'NONE') pet_type,
        COALESCE(c.pet_breed, 'NONE') pet_breed
FROM    users a
        LEFT JOIN adverts b
            ON a.user_ID = b.user_ID
        LEFT JOIN pet_breeds c
            ON b.breed_ID = c.breed_ID

UPDATE 1

SELECT  a.first_name, a.email,
        COALESCE(c.pet_type, 'NONE') pet_type,
        COALESCE(c.pet_breed, 'NONE') pet_breed
FROM    users a
        LEFT JOIN 
        (
            SELECT user_ID, max(breed_ID) breed_ID
            FROM adverts
            GROUP BY user_ID
        ) b ON a.user_ID = b.user_ID
        LEFT JOIN pet_breeds c
            ON b.breed_ID = c.breed_ID

edited Nov 22, 2012 at 15:07

answered Nov 22, 2012 at 14:50

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

2 Comments

user1052096 Over a year ago

..except it is returning multiple records where more than one adverts record exists. I only wanted it to return one record per user.

John Woo Over a year ago

@user1052096 so you mean any of the records to be shown as long as you have to show atleast 1 pettype and petbreed?

Collectives™ on Stack Overflow

MYSQL query/subquery merging

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related