I'm attempting to get the information from the brews
and recipes
tables based off of how many followers there are in table follow, but I am getting an sql error because I am returning more than one result in my query. Should I split this up in to two queries? Or is there as work around that would allow me to check for more than one b.uid
in my outer WHERE
statement
SELECT
b.bid AS bid, b.name AS bname,
r.name AS recipe, b.status AS status,
r.rid, a.f_name, a.l_name, a.pic_loc, a.uid AS u开发者_如何学编程id
FROM
recipes r, brews b, account a
WHERE
b.uid = (SELECT followed FROM follow
WHERE follower = '#cookie.id#')
AND b.rid = r.rid AND b.uid = a.uid
GROUP BY b.bid
ORDER BY b.time DESC
Just use WHERE b.uid IN (SELECT followed...
In order to return multiple result sets you need to use cfstoredproc, but that is not your issue here. Either change your "=" sign to an IN, in your where clause or change up your select to be a join. Your issue is just a bad query, not multiple result sets.
SELECT
b.bid AS bid, b.name AS bname, r.name AS recipe,
b.status AS status, r.rid, a.f_name, a.l_name,
a.pic_loc, a.uid AS uid
FROM
recipes AS r
LEFT JOIN brews AS b
ON b.rid = r.rid
LEFT JOIN account AS a
ON b.uid = a.uid
LEFT JOIN follow AS f
ON f.followed = b.uid
WHERE f.follower = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#cookie.id#" />
GROUP BY b.bid
ORDER BY b.time DESC
The GROUP BY clause is probably causing you problems as well. You should only have a GROUP BY clause in your query if you are using aggregate functions (SUM(), AVG(), MIN(), MAX(), etc...) and need to group the records you're totaling, etc. into subgroups.
精彩评论