Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Add another join

Posted on 2011-02-26
5
Medium Priority
?
208 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 39

Expert Comment

by:Aaron Tomosky
ID: 34989136
0
 
LVL 4

Accepted Solution

by:
DanDauchy earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

721 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