• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

Add another join

Current code:

SELECT m.id, m.first_name, m.last_name, MAX(
CASE WHEN t.ttype = 'hometown'
THEN t.tag
END ) AS hometown
FROM members m
LEFT JOIN (

SELECT uid, ttype, GROUP_CONCAT( tag ) tag
FROM tags
GROUP BY uid, ttype
)t ON m.id = t.uid
WHERE m.id
IN ( 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 40, 41, 42, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 64, 66, 68, 69, 70, 71, 73, 74, 75, 77, 79, 81, 82, 83, 90, 91 )
GROUP BY m.id, m.first_name, m.last_name

Open in new window


members: id, first_name, last_name, alumni_mentor, member_mentor
tags: tid, uid, ttype, tag

- The alumni_mentor and member_mentor fields each have a member id in them, which corresponds to an id field in the members table

I would like to create an extra temp field in the sql results - called mentee - which displays the concat first name and last name of the mentees for that member.

E.g. If member 1 has 4 in the member_mentor field
and member 2 has 4 in the member_mentor field
and member 3 has 4 in the alumni_mentor field
Then member 4 will have "member 1, member 2, member 3" in the temp mentees field

Thanks in advance - if you need clarification please ask.
0
anonwig
Asked:
anonwig
  • 3
1 Solution
 
DanDauchyCommented:
does this need to be all in one SQL statement?  Does it have to be joined w/ the provided sql statements?

As far as I know, I wouldn't be able to do this w/in 1 SQL statement.  Now if i could query the DB, write a php script, THEN I could insert a mentee field into a DB as you described above.
0
 
anonwigAuthor Commented:
feel free to add a second query - that's fine by me - and yes php/mysql is perfect
0
 
Aaron TomoskyTechnology ConsultantCommented:
0
 
DanDauchyCommented:
ok so i tested this on my home server to make sure it worked and i belive it's doinw hat you want.

let me know if you have any questions about what it's doing.
//This query will return a sorted, unique list of all the users that have mentors
$result =  mysql_query("SELECT id, alumni_mentor AS mentor FROM members WHERE alumni_mentor != '' UNION SELECT id, member_mentor AS mentor FROM members WHERE member_mentor != '' ORDER BY mentor") or die("didn't work");

$previous_value =0; //ensure 2nd if is entered on the first pass
$string_to_insert = "";
$x = 0;
while($pair = mysql_fetch_array($result))
{
	if ($x = 0) //first time ensure you enter the 2nd if
	$pair['mentor'] = $previous_value;
	
	if($pair['mentor'] == $previous_value)
	{
		//grab the first/last name of the mentee and construct a string to insert later
		$id = $pair['id'];
		$names = mysql_fetch_array(mysql_query("SELECT first_name, last_name FROM members WHERE id=$id"));
		$string_to_insert .= $names['first_name'] . " " . $names['last_name'] . ", ";
	}
	else
	{
		//once as we've hit a differnt mentor in the resuts, insert constructed string
		$id = $pair['id'];
		mysql_query("UPDATE members SET mentees='$string_to_insert' WHERE id='$previous_value'");
		$previous_value = $pair['mentor'];
		$string_to_insert = "";
		$names = mysql_fetch_array(mysql_query("SELECT first_name, last_name FROM members WHERE id=$id"));
		$string_to_insert .= $names['first_name'] . " " . $names['last_name'] . ", ";
	}
	$x++;
}

//update table w/ the last entry
mysql_query("UPDATE members SET mentees='$string_to_insert' WHERE id='$previous_value'");

Open in new window

0
 
DanDauchyCommented:
is there anything else you needed for this?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now