Solved

Add another join

Posted on 2011-02-26
5
204 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 39

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
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 count occurrences of each item in an array.

735 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