CODEWITHSUNDEEP
mysqlsql
user1052096
user1052096

Reputation: 883

MYSQL query/subquery merging

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

Upvotes: 0

Views: 47

Answers (1)

John Woo
John Woo

Reputation: 263723

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

Upvotes: 1

Related Questions