Joining three tables and GROUP_CONCAT-ing 1

[chunkII123]

I currently have a 'view' setup so it polls a couple tables for a java desktop program. The problem is, I need to add another table column from another table, and have the values concatenated separated by ', '. heres is what the current view looks like -

select cp.membernumber AS MemberNumber, mcp.membernumber AS MatchesMemberNumber, cp.FirstName AS FirstName, concat(cp.FirstName, ' ', cp.LastName) AS FullName, cp.CallFirst AS callfirst, cp.HomePhone AS HomePhone, cp.StreetAddress AS StreetAddress, cp.City AS City, cp.State AS State, cp.Zip AS Zip, cp.Email AS email, cp.HideLastName AS HideLastName, cp.HideAddress AS HideAddress, cp.HideAge AS HideAge, mcp.CallFirst AS matchescallFirst, mcp.FirstName AS MatchesFirstName, concat(mcp.FirstName, ' ', mcp.LastName) AS MatchesFullName, mcp.HideLastName AS HideMatchesLastName, mcp.HideAge AS HideMatchesAge, mcp.HomePhone AS MatchesHomePhone, mcp.StreetAddress AS MatchesStreetAddress, mcp.City AS MatchesCity, mcp.State AS MatchesState, mcp.Zip AS MatchesZip, mcp.Occupation AS Occupation, mcp.dateofbirth AS DateOfBirth, mcp.HairColor AS HairColor, mcp.EyeColor AS EyeColor, mcp.Education AS Education, mcp.HeightFeet AS HeightFeet, mcp.HeightInches AS HeightInches, mcp.Race AS Race, mcp.Religion AS Religion, mcp.InfoForOthers AS InfoForOthers, mcp.Preferences AS Preferences, mcp.Gender AS Gender, mcp.MaritalStatus AS MaritalStatus, mcp.dateofbirth AS MatchesDateOfBirth, mcp.NumberOfChildren AS numberofchildren, mcp.HideAddress AS HideMatchesAddress, matches.DateMatched AS DateMatched, matches.DateSent AS DateSent, matches.DateScheduled AS DateScheduled, matches.DateApproved AS DateApproved, (timestampdiff(YEAR, mcp.dateofbirth, now()) - if((mcp.dateofbirth > now()), 1, 0)) AS Age, cp.receiveEmail AS receiveemail, cp.receiveMail AS receivemail h.hobbyID
from ((matches join membersinformation cp on((cp.membernumber = matches.membernumber))) 
join membersinformation mcp on((mcp.membernumber = matches.matchesmembernumber))) 
join hobbies h on h.membernumber = cp.membernumber
where isnull(matches.DateSent)

(Sorry for the length of it)

Now the column I need to add is each member's hobbies, from the 'hobbies' table which is LEFT JOIN(ed) to the 'possiblehobbies' table to change the 'hobbies' numberical value to the 'possiblehobbies' name value. the hobbies table can be joined to the original view on 'membernumber' as you will see.

Example of the Table(s)

'Hobbies'

---------------------------
membernumber | HobbyID

980000 | 10
980000 | 23
980002 | 67
---------------------------

PossibleHobbies'

---------------------------
ID | Name

10 | Bowling
23 | Car Racing
67 | Dining
---------------------------

Anyhow, you get the idea. I have tried using GROUP_CONCAT(DISTINCT.....) within the current view, and joining the tables, however, this causes retuned rows to add the additional column, and become one row. I there a less ambiguous call than GROUP_CONCAT, or a way to add a statement similar to WHERE within the GROUP_CONCAT context?

Any help is much appreciated. Thank you all ahead of time!

Beware of hackers bearing executables. Happy Hunting. 'irc.2600.net'

 

[r937]

(Sorry for the length of it)

you should be more sorry for the unreadableness of it -- please, learn indentation and line breaks

SELECT cp.membernumber 
     , mcp.membernumber AS MatchesMemberNumber
     , cp.FirstName 
     , CONCAT(cp.FirstName,' ',cp.LastName) AS FullName
     , cp.CallFirst 
     , cp.HomePhone 
     , cp.StreetAddress 
     , cp.City 
     , cp.State 
     , cp.Zip 
     , cp.Email 
     , cp.HideLastName 
     , cp.HideAddress 
     , cp.HideAge 
     , mcp.CallFirst AS matchescallFirst
     , mcp.FirstName AS MatchesFirstName
     , concat(mcp.FirstName,' ',mcp.LastName) AS MatchesFullName
     , mcp.HideLastName AS HideMatchesLastName
     , mcp.HideAge AS HideMatchesAge
     , mcp.HomePhone AS MatchesHomePhone
     , mcp.StreetAddress AS MatchesStreetAddress
     , mcp.City AS MatchesCity
     , mcp.State AS MatchesState
     , mcp.Zip AS MatchesZip
     , mcp.Occupation 
     , mcp.dateofbirth 
     , mcp.HairColor 
     , mcp.EyeColor
     , mcp.Education 
     , mcp.HeightFeet 
     , mcp.HeightInches 
     , mcp.Race 
     , mcp.Religion 
     , mcp.InfoForOthers 
     , mcp.Preferences 
     , mcp.Gender 
     , mcp.MaritalStatus 
     , mcp.dateofbirth AS MatchesDateOfBirth
     , mcp.NumberOfChildren 
     , mcp.HideAddress AS HideMatchesAddress
     , matches.DateMatched 
     , matches.DateSent
     , matches.DateScheduled 
     , matches.DateApproved 
     , TIMESTAMPDIFF(YEAR,mcp.dateofbirth,NOW()) - 
       CASE WHEN mcp.dateofbirth > NOW() THEN 1 ELSE 0 END AS Age
     , cp.receiveEmail 
     , cp.receiveMail 
     , h.hobbyID
  FROM matches 
INNER
  JOIN membersinformation cp 
    ON cp.membernumber = matches.membernumber
INNER
  JOIN membersinformation mcp 
    ON mcp.membernumber = matches.matchesmembernumber
LEFT OUTER
  JOIN ( SELECT h.membernumber
              , GROUP_CONCAT(ph.name) AS hobbies 
           FROM hobbies AS h
         INNER
           JOIN possiblehobbies AS ph
             ON ph.id = h.hobbyid
         GROUP
             BY h.membernumber ) AS hh
    ON hh.membernumber = cp.membernumber
 WHERE matches.DateSent IS NULL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[chunkII123]

Rudy -

Will do in the future. Regardless, thank you for your help with this, however, the hobbies column does not show up when the query is executed.

Beware of hackers bearing executables. Happy Hunting. 'irc.2600.net'

 

[chunkII123]

Evidently you cannot edit a post once it has been entered on this forum.

Anyhow, I meant to add the error code it throws when executing the wuery you gave me

Error 1260 - 1 line(s) were cut by GROUP_CONCAT()

Beware of hackers bearing executables. Happy Hunting. 'irc.2600.net'

 

[r937]

omg, how could i forget to add [blue]hh.hobbies[/blue] to the SELECT clause

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[chunkII123]

You didn't forget it, it was mis-spelled, after about 10 minutes, I finally notices the 'h.hobbies' at the bottom, when it was meant to be 'hh.hobbies'. Blame the keyboard. Thanks again!

Beware of hackers bearing executables. Happy Hunting. 'irc.2600.net'