MySQL select all from 3 tables different amount of rows

Question

I want to retrieve a list of all members and all members boats. The way I'm doing it I get the members info for each boat a member has. So if a member John Smith has 3 boats, John Smith is displayed 3 times. I want the name and info to be displayed once, and empty columns instead of duplicated along with the boats.

SELECT m.memberId, m.fName, m.lName, m.ssn, b.boatId, b.length, bt.type
FROM member AS m
RIGHT JOIN boat AS b
ON m.memberId = b.memberId
INNER JOIN boatType AS bt
ON b.boatTypeId = bt.boatTypeId

How would you determine which boat to be displayed for John Smith? — Brian Hoover, Oct 18 '12 at 19:36
That's a display requirement that should be handled in your UI layer, not in your database result set. — Joe Stefanelli, Oct 18 '12 at 19:36
Did you try using the distinct keyword? Or Group by might work — Solid1Snake1, Oct 18 '12 at 19:38

score 1 · Answer 1 · answered Oct 18 '12 at 19:38

1

Not possible in Mysql to generate this type of query if I understand what your asking for:

 Owner 1 details, boat 1 details
                  boat 2 details
                  boat 3 details
 Owner 2 details, boat 4 details
                  boat 5 details

As note in a comment, this type of grouping is done in the display side, after you get back the data.

answered Oct 18 '12 at 19:38

Ray

40,256
21
101
138

Yes thats what I wanted. Ok.. I thought it might. I think I did it in MS SQL.. Thanks. – user1121487 Oct 18 '12 at 19:41

O. Jones · Accepted Answer · 2012-10-19T17:27:37.800

As Joe suggested, you can use the GROUP_CONCAT and CONCAT functions in MySQL, like so.

   SELECT m.memberId, m.fName, m.lName, m.ssn, 
          GROUP_CONCAT(CONCAT(b.boatId, '(', bt.type, ':',b.length,' ft.)')
                       ORDER BY bt.boatId SEPARATOR '; ')
     FROM member AS m
LEFT JOIN boat AS b ON m.memberId = b.memberId
     JOIN boatType AS bt ON b.boatTypeId = bt.boatTypeId
 GROUP BY m.memberId, m.fName, m.lName, m.ssn

This will give you rows like this.

1 Larry Ellison 123-45-6789 USA 17(Americas Cup:113 ft.); Rising Sun(Diesel:453 ft.)
2 Ollie Jones 987-65-4321 Scruffy Guppy(Sunfish:12 ft.)

oops. That's what I get for not testing stuff. fixed. – O. Jones Oct 19 '12 at 12:31 — O. Jones, Oct 19 '12 at 12:31

score 0 · Answer 3 · answered Oct 18 '12 at 19:38

0

Add a group by clause.

GROUP BY m.member id, m.fName, m.lName, m.ssn

As for the boatId, length and type fields, you'll have to decide which of the 3 boats' ID length and type (using your example) should be shown.

answered Oct 18 '12 at 19:38

Vinod Vishwanath

5,821
2
26
40

But the OP needs *all* of the boats returned in his result set. – Joe Stefanelli Oct 18 '12 at 19:42
All of the boats in ONE column? Something like: 1 John Smith boat1,boat2,boat3 That's a little harder to do, but this might help: http://stackoverflow.com/questions/10461874/sql-server-concatenate-group-by – Vinod Vishwanath Oct 18 '12 at 19:43
No. If that's what he wanted, he might be able to leverage [GROUP_CONCAT](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) since he's using MySQL. – Joe Stefanelli Oct 18 '12 at 19:47

MySQL select all from 3 tables different amount of rows

3 Answers3