CODEWITHSUNDEEP
mysql
mannthedan
mannthedan

Reputation: 15

Mysql Query Join two tables

I have two simple tables about one about dog breeds and one with dog names.

Breeds:

DogId  DogBreed                         

   1    boxer                            
   2     Lab                              
   3    Sheppard

Names:

DogId   DogName
   1      Max
   1      duke
   2      Jack
   2      Socks
   3      Lassie

The results I am looking for is 

boxer - duke, max
Lab- jack, socks
Sheppard- Lassie

I have tried Inner joins, but I really can't figure this out, it seems so easy. Any help would be very welcome

Upvotes: 0

Views: 51

Answers (3)

Keval Pithva
Keval Pithva

Reputation: 610

Inner queries will slow down the performance (because of higher execution time) if you have large number of records.

Optimised SQL Query:

SELECT b.DogBreed,GROUP_CONCAT(n.DogName) FROM Breeds b 
LEFT JOIN `Names` n ON n.DogId=b.DogId
GROUP BY n.DogId

Upvotes: 2

Amit Gaud
Amit Gaud

Reputation: 766

Hope this will help you 

 SELECT b.dogbreed , (SELECT GROUP_CONCAT(DogName) 
FROM NAMES WHERE DogId = b.DogId ) AS DogNames FROM Breeds b

Upvotes: 0

Praveen
Praveen

Reputation: 9355

You can use GROUP_CONCAT

Try,

Select 
    b.dogbreed,
    GROUP_CONCAT(
        DISTINCT n.dogname
        ORDER BY n.dogname ASC SEPARATOR ', '
    ) dognames
from breeds b
join names n on n.dogid = b.dogid
group by b.dogbreed

Upvotes: 0

Related Questions