Solved

Add another join

Posted on 2011-02-26
5
202 Views
Last Modified: 2012-05-11
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
Comment
Question by:anonwig
  • 3
5 Comments
 
LVL 4

Expert Comment

by:DanDauchy
ID: 34988340
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
 

Author Comment

by:anonwig
ID: 34988599
feel free to add a second query - that's fine by me - and yes php/mysql is perfect
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 34989136
0
 
LVL 4

Accepted Solution

by:
DanDauchy earned 500 total points
ID: 34989222
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
 
LVL 4

Expert Comment

by:DanDauchy
ID: 35165078
is there anything else you needed for this?
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now