Solved

Add another join

Posted on 2011-02-26
5
200 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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 4

Accepted Solution

by:
DanDauchy earned 500 total points
Comment Utility
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
Comment Utility
is there anything else you needed for this?
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

771 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