ok, you'd be right conculding that the reason you get multiple results would be because of multiple records it matched in the co table when doing a join on all the tables.

from what I gather, you really only care about the records in the co table that would actualy be bookmarked, and since those are the only ones you want to evalute how bout not even including the other records.

The idea here, is that you include all the records that would pertain to items that would be bookmarked, the items that don't match that end up getting NULL fields, which would end up evaluating to 0 in your if statement. But because we exclude all records that wouldn't be bookmarked, you don't get multiple records of unbookmarked records. Now this doesn't take into effect if you get multiple records that are bookmarked from the co table. I wasn't sure if you had to worry about that or not.

SELECT p.id AS player_id,
p.name,
p.hs,
c.id AS coach_id,
co.cid AS college,
IF(co.pid=31,1,0) AS is_bookmarked
FROM player p
INNER JOIN coach c
ON p.hs = c.id
LEFT OUTER JOIN co_bookmarks co
ON (c.id = co.cid AND (co.pid != 31,1,0))
WHERE p.id=31
AND co.cid=2

I wasn't quite sure what your relationship between the tables was for the co table, so I just asumed that it was c.id = co.cid

The LEFT OUTER join takes all the records from the left side of the join, and tries to match them up with records from the right. If it can't find a match for a record at the right it leaves NULL fields for the values that would have been represented from the table on the right.

I'm kind basign this on pure theory, never tried it before... but I thought I'd throw it your way to see if it got any ideas rolling, or maybe it'll help someone else with some ideas on how to better get your results.

I hope this helps some.

Matt Johnson

Yeah, that helpd a lot. I created a little test db to play with it, and see why it wasn't working.... the hands on approch helped me understand what was really going on.

I worked on the query I gave you a little and found out why it wouldn't return records. It was due to having co.cid=2 in the where clause, because if my query didn't find any matches in the co table, then it obviously wasn't going to match co.cid=2, so I put that part up in the join and I think this might work for you.

SELECT p.id AS player_id,
p.name,
p.hs,
c.id AS coach_id,
co.cid AS college,
IF(co.pid=31,1,0) AS is_bookmarked
FROM player p
INNER JOIN coach c ON p.hs = c.id
LEFT OUTER JOIN co_bookmarks co ON (p.id = co.pid AND co.cid=2)
WHERE p.id=31


What this does, is if it gets a values that is bookmarked it returns all the fields, but if there isn't a match in the co table it leaves the fields from the co table as NULL, because of the LEFT OUTER JOIN. Since the co.pid field would be NULL your IF will evaluate as false, setting the is_bookmarked field to 0, because there wasn't a p.id that matched a co.pid.

One thing is that the co.cid field comes up NULL, because no records were found, but I have a solution for that too. Since your result is always going to be 2, or whatever you put in the co.cid=? then you could just set that field to that value to begin with, instead of actualy trying to pull it out.


SELECT p.id AS player_id,
p.name,
p.hs,
c.id AS coach_id,
2 AS college,
IF(co.pid=31,1,0) AS is_bookmarked
FROM player p
INNER JOIN coach c ON p.hs = c.id
LEFT OUTER JOIN co_bookmarks co ON (p.id = co.pid AND co.cid=2)
WHERE p.id=31


The reason I did the tables joins so different, is so that I had more control on what was joined where, and why.



In Perl I'd probably write the query like this.

$college_id = 2;
$player_selected = 31;

$sth = $dbh->prepare( q^
SELECT p.id AS player_id,
p.name,
p.hs,
c.id AS coach_id,
? AS college,
IF(co.pid=?,1,0) AS is_bookmarked
FROM player p
INNER JOIN coach c ON p.hs = c.id
LEFT OUTER JOIN co_bookmarks co ON (p.id = co.pid AND co.cid=?)
WHERE p.id=?
^);

$sth->execute( $college_id, $player_selected, $college_id, $player_selected);

The variables in the execute() function are used to replace each ? in the query.



Let me know what you think.

Matt Johnson
CPUweb

Matt, you are absolutely AMAZING!!!!!

I plugged in your second query and it worked like a charm! Your explanation of how it works is great and I certainly learned from it.

Thank you again for all of your help and time with this query :D

Jason