Complex MySQL Query (inner joins and concats)

[jarvis]

Hi All,

 

Thanks everyone for the help so far, it's been great. I've one final query I can't seem to work out. Here's my tabel structure:

 

wp_bp_groups

id | name

1 | Aerosols

2 | Boats

3 | Boilers

 

wp_bp_groups_members

id | groupd_id | user_id

1 | 1 | 1

2 | 2 | 2

 

wp_bp_xprofile_data

id | field_id | user_id | value

1 | 1 | 1 | john smith

2 | 2 | 1 | john@smith.com

3 | 3 | 1 | 123456

4 | 1 | 2 | joe bloggs

5 | 2 | 2 | joe@bloggs.com

6 | 3 | 2 | 987564

 

What I want to get is:

 

Aerosols

John Smith, john@smith.com, 123456

 

Boats

Joe Bloggs, joe@bloggs.com, 987654

 

I'm at a complete loss with this one.  :'(

 

TIA if you can help in any way!

[Muddy_Funster]

Working purely from the information you have posted, you are grossly over complicating things.  All you should need is

SELECT wp_bp_groups.name, wp_xprofile_data.value FROM wp_bp_groups.name RIGHT JOIN wp_xprofile_data ON (wp_bp_groups.id = wp_xprofile_data.user_id)

 

[jarvis]

Thanks Muddy_Funster  unfortunately, that query doesn't work.

 

It returns:

SELECT command denied to user 'xxx'@'localhost' for table 'name'

 

[Muddy_Funster]

bah! my bad, remove the .name from "wb_bp_groups.name" after the FROM

 

>_<

[jarvis]

Thanks again, this time I get the error:

Table 'xxx.wp_xprofile_data' doesn't exist

 

Sorry!

[jarvis]

I've tried this:

SELECT wp_bp_groups.name,  GROUP_CONCAT( wp_bp_xprofile_data.value SEPARATOR ',' )

FROM wp_bp_xprofile_data, wp_bp_groups_members, wp_bp_groups

WHERE wp_bp_groups.id = wp_bp_groups_members.id

AND wp_bp_groups_members.id = wp_bp_xprofile_data.id

Although it returns a result, it only returns a single result and not:

Aerosols

John Smith, john@smith.com, 123456

 

More like:

Aerosols John

[Muddy_Funster]

That's because there is only the one record that matches on the wp_xprofile table under the id field.  You will need to link to the user_id field, and I think that's the problem with the statement I gave you - user_id is what mySQL calls it's own user ID field, and it may be getting mixed up :/  can you rename the field to uid or something similar?

[jarvis]

No sadly not. The db structure is in place and is from a 3rd party. Sadly I can't do anything with the structure or naming conventions - annoyingly!

[Muddy_Funster]

how about declaring the database in the query.....give this a try

SELECT wp_bp_groups.name, wp_xprofile_data.value FROM wp_bp_groups RIGHT JOIN wp_xprofile_data ON ([your databse name].wp_bp_groups.id = [your database name].wp_xprofile_data.user_id)

 

Without the square brackets

[jarvis]

Sadly not:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[xxx].wp_bp_groups.id = [xxx].wp_xprofile_data.user_id) ' at line 1

 

Good thought though! :-)

[Muddy_Funster]

just out of curiosity, what happens when you run your

SELECT wp_bp_groups.name,  GROUP_CONCAT( wp_bp_xprofile_data.value SEPARATOR ',' )
FROM wp_bp_xprofile_data, wp_bp_groups_members, wp_bp_groups
WHERE wp_bp_groups.id = wp_bp_groups_members.id
AND wp_bp_groups_members.id = wp_bp_xprofile_data.id

with the last = wp_bp_xprofile_data.id changed to = wp_bp_xprofile_data.user_id instead?

[jarvis]

Thanks for the perservering! I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wp_bp_groups_members.id = wp_bp_xprofile_data.id LIMIT 0, 30' at line 1

 

Was that what you were expecting?

[jarvis]

Actually, that was my error, I noticed there wasn't a space, having corrected it, it shows the same as the above I had:

Aerosols John

[Muddy_Funster]

not that lol. 

but I have noticed something. Try this:

SELECT wp_bp_groups.name, wp_bp_xprofile_data.value 
FROM wp_bp_groups.name RIGHT JOIN wp_bp_xprofile_data 
ON (wp_bp_groups.id = wp_bp_xprofile_data.user_id)

 

[jarvis]

Sorry, lol, got this one:

SELECT command denied to user 'xxx'@'localhost' for table 'name' :-(

[Muddy_Funster]

that's what I get for copying and pasting 

 

this should work better

SELECT wp_bp_groups.name, wp_bp_xprofile_data.value
FROM wp_bp_groups RIGHT JOIN wp_bp_xprofile_data
ON (wp_bp_groups.id = wp_bp_xprofile_data.user_id)

[jarvis]

Oooooh that seems to be getting somewhere. I now get:

name | value

aerosols | joe bloggs

null | joe@bloggs.com

null | 123456

 

Oddly only shows one category? And the values list down. Changing the query to:

SELECT wp_bp_groups.name, GROUP_CONCAT( wp_bp_xprofile_data.value SEPARATOR ',' )

FROM wp_bp_groups

RIGHT JOIN wp_bp_xprofile_data

ON (wp_bp_groups.id = wp_bp_xprofile_data.user_id)

That sorts the list values. Will give that some more testing. I think it's only returning one person and one category. However, I've several test people assigned to several cats. Will test more!! Thanks

[jarvis]

Hmmm, seems to only return 1 result. Will give this one some more thought - damn thing!

[mikosiko]

this a working/tested select based on your data.

 

SELECT wp_bp_groups.name,
      GROUP_CONCAT( wp_bp_xprofile_data.value ORDER BY wp_bp_xprofile_data.field_id )
FROM (wp_bp_xprofile_data
  JOIN wb_bp_groups_members ON wp_bp_xprofile_data.user_id = wb_bp_groups_members.user_id)
  JOIN wp_bp_groups ON wp_bp_groups.id = wb_bp_groups_members.group_id
GROUP BY wp_bp_groups.name;

 

 

[jarvis]

Thanks mikosiko, unfortunately, that gives the following error:

Table 'xxx.wb_bp_groups_members' doesn't exist

 

[mikosiko]

@jarvis:

 

This is the query that I gave to you:

SELECT wp_bp_groups.name,
      GROUP_CONCAT( wp_bp_xprofile_data.value ORDER BY wp_bp_xprofile_data.field_id )
FROM (wp_bp_xprofile_data
  JOIN wb_bp_groups_members ON wp_bp_xprofile_data.user_id = wb_bp_groups_members.user_id)
  JOIN wp_bp_groups ON wp_bp_groups.id = wb_bp_groups_members.group_id
GROUP BY wp_bp_groups.name;

 

and your answer:

Table 'xxx.wb_bp_groups_members' doesn't exist

 

now.. tell me... where in the code that I gave to you is something like this xxx.wb_bp_groups_members ? 

 

and worst... what you did to find the problem and fix YOUR error? ... go back to your code and check it again.

 

 

[jarvis]

Hi Mikosiko,

 

Thanks for the reply. I tried running your code direct in phpmyadmin as a sql query. The error it gave back I displayed but edited out the table database name. I ran the query exactly as you had it I assure you.

 

Apologies as never meant to cause offence, just posting my results/findings :-(

[mikosiko]

@jarvis:

 

Don't worry... is not offense (or anything similar)at all... I'm just trying to push you to debug your code by yourself... that is the best way for you to learn.

 

The problem is simple and you should have be able to catch it... your table name is wp_bp_groups_members  and in the code that I gave to you:

SELECT wp_bp_groups.name,
      GROUP_CONCAT( wp_bp_xprofile_data.value ORDER BY wp_bp_xprofile_data.field_id )
FROM (wp_bp_xprofile_data
  JOIN wb_bp_groups_members ON wp_bp_xprofile_data.user_id = wb_bp_groups_members.user_id)
  JOIN wp_bp_groups ON wp_bp_groups.id = wb_bp_groups_members.group_id
GROUP BY wp_bp_groups.name;

 

is named  as wb_bp_groups_members  you see the problem now?...

[jarvis]

At mikosiko. No that's fine. It was one of those couldn't see for looking. Like how many f's in this sentence:

FINISHED FILES ARE THE RE-SULT OF YEARS OF SCIENTIFIC STUDY COMBINED WITH THE EXPERIENCE OF YEARS

 

That code worked like a charm & I'm incredibly thankful. Everyday is a learning curve, some more than others & each day I learn more tricks & tips to try before pestering the forums.

 

Thanks again

 

p.s There are 6