[RESOLVED] Need help with complex query

rprincejr

I have 2 tables; members and teams

members table
memberID, firstName, lastName

(firstName and lastName are fulltext indexes)

teams table
team_id, member1ID, member2ID

Here's my query

$sql = "SELECT a.* ";		
$sql .= "FROM teams a WHERE ";
$sql .= "a.member1ID IN (SELECT b.memberID FROM members b ";
$sql .= "WHERE MATCH(b.firstName, b.lastName) AGAINST('$q' IN BOOLEAN MODE)) ";
$sql .= "OR a.member2ID IN (SELECT b.memberID FROM members b ";
$sql .= "WHERE MATCH(b.firstName, b.lastName) AGAINST('$q' IN BOOLEAN MODE)) ";

if($year)
	$sql .= "AND a.team_y = $year ";

$sql .= "ORDER BY a.team_num ASC "; 

if($limit)		
	$sql .= "$limit";

Im trying to build a query that will let me show me all of the teams a member is on. I have to be able to search by partial name.

Ex. Show me all teams that doe is on.

This query has to output the teams.

johanafm

You should have 3 tables.

member: id, first_name, last_name
team: id, team_name
team_member: team_id, member_id

And then

SELECT first_name, last_name, team_name
FROM member m
INNER JOIN team_member tm ON m.id = tm.member_id
INNER JOIN team ON tm.team_id = t.id
WHERE ...

Although it will not show you any member that doesn't belong to any team yet. If you want those as well, you'd use a LEFT JOIN instead

rprincejr

ok, so if I entertain the idea of adding a 3rd table named teams_member, I have a few questions about its use.

Obviously, when a new team is created, 2 entries will be added to the teams_member table. One for each member of the team. No problem there.

What happens when a team gets edited and one of the team members is changed?

Dealing with the new teams_member table, there has to be code in place to

a.) delete the entry that has the removed team member, then add a new entry with the new memberID.

b.) the entry that has the removed team member has to be updated to the new memberID.

I dont really know how the existing teams_member record can be updated because the memberID has changed in the team record.

Unless Im missing something, it seems like a big hassle to use a 3rd table.

rprincejr

Normalization is good in some cases. But I chose a different route. Here is my final query that was used.

$sql = "SELECT a.* ";		
$sql .= "FROM teams a ";
$sql .= "INNER JOIN members b1 ON b1.memberID = a.member1ID ";
$sql .= "INNER JOIN members b2 ON b2.memberID = a.member2ID ";
$sql .= "WHERE MATCH(b1.firstName, b1.lastName, b2.firstName, b2.lastName) AGAINST('$q' IN BOOLEAN MODE) ";

if($year)
	$sql .= "AND a.team_y = $year ";

$sql .= "ORDER BY a.team_num ASC "; 

if($limit)		
	$sql .= "$limit";

I inner joined the members table twice but on different columns and with different aliases. This worked perfectly for my problem. And without the hassle of trying to normalize the data.

Lastly, Im not happy with johanafm as he gave an effort to answer but never came back to respond to my questions that arose from his solution. Essentially he was a drive-by poster. If you cant devote the time to see a problem through to resolution, dont post.

cretaceous

woaah - can I defend johanfm
he posts a lot - and they are accurate and informed comments

look at his post count

ok so he missed replying - no need to get aggressive about it

normalisation would allow you to easily show (as an example) who had been a member of a team in the past (once you add start and end dates)
in case that ever became a useful addition..
so ultimately it might save big hassle in the future

dagon

rprincejr;10975775 wrote:
Lastly, Im not happy with johanafm as he gave an effort to answer but never came back to respond to my questions that arose from his solution. Essentially he was a drive-by poster. If you cant devote the time to see a problem through to resolution, dont post.

no one is under any obligation to post twice, or once for that matter, being a prick is not going to increase your chances of getting (free)help

rprincejr

dagon;10975783 wrote:
no one is under any obligation to post twice, or once for that matter, being a prick is not going to increase your chances of getting (free)help

Your right, no one is obligated to post. But I believe if your going to offer a solution, see it through until its resolved. Or dont post. I dont mean to be rude.

Johanfm posted a solution to my problem, and never checked back. My problem is time-sensitive. And with him posting, it deters others that may have solutions.

Im not saying his solution was incorrect. But I had questions concerning his solution, and I couldnt wait any longer for him to show up. If youll notice I gave him 2 days to respond.

Lastly, its interesting how both of you quickly respond on his behalf, yet you didnt respond with a solution. Essentially your trolling. Ive been a member of this site since 2005. I dont have alot of posts, because I dont have the time to look at problems often. And post count doesnt carry much weight. It just means your active, not right.

cretaceous

I did have a solution: normalise

trolling is writing to annoy someone - so clearly we must learn from your case "don't feed the trolls"