好吧,我想我错过了一个小组或其他什么,基本上我需要添加什么,但不知道该怎么做,我该如何让offer添加行或加入它们。
这是我的查询
SELECT * FROM approved_business, business_stores, Real_Cash_Offers
WHERE approved_business.id = business_stores.business_id
AND Real_Cash_Offers.storeid IN (business_stores.storeid)
ORDER BY `approved_business`.`id` ASC
这是输出
1249 Jaggers Hair and Beauty 2012-01-22 19:11:05 1249 1 6139646071 112 Bridge Street Eltham 1 3095 Let Jagger Hair and Beauty set you up with the hot... 1372 1 50|5 2012-01-22 19:11:05
1249 Jaggers Hair and Beauty 2012-01-22 19:11:05 1249 1 6139646071 112 Bridge Street Eltham 1 3095 Let Jagger Hair and Beauty set you up with the hot... 1372 1 100|10 2012-01-22 19:11:05
1249 Jaggers Hair and Beauty 2012-01-22 19:11:05 1249 1 6139646071 112 Bridge Street Eltham 1 3095 Let Jagger Hair and Beauty set you up with the hot... 1372 1 250|30 2012-01-22 19:11:05
我想要的是让报价变成一个数组或其他东西,这样就只有一行。
新代码
在JOIN中发现错误SQL代码
SELECT * FROM approved_business, business_stores, Real_Cash_Offers
WHERE approved_business.id = business_stores.business_id
AND Real_Cash_Offers.business_id = approved_business.id
AND Real_Cash_Offers.storeid = business_stores.storeid
ORDER BY `approved_business`.`id` DESC
输出
id tradingname listed business_id storeid phone street suburb state postcode discription business_id storeid offer tstamp
2582 Deeply Skin Medi Spa 2012-01-22 19:11:05 2582 1 0388224001 Suite 3 , 616 Park Rd Park Orchard 1 3114 2582 1 370|5 2012-01-22 19:11:05
2582 Deeply Skin Medi Spa 2012-01-22 19:11:05 2582 1 0388224001 Suite 3 , 616 Park Rd Park Orchard 1 3114 2582 1 570|10 2012-01-22 19:11:05
2582 Deeply Skin Medi Spa 2012-01-22 19:11:05 2582 1 0388224001 Suite 3 , 616 Park Rd Park Orchard 1 3114 2582 1 1570|15 2012-01-22 19:11:05
尝试group_concat
SELECT id, group_concat(offer) FROM approved_business, business_stores, Real_Cash_Offers
WHERE approved_business.id = business_stores.business_id
AND Real_Cash_Offers.storeid IN (business_stores.storeid)
GROUP BY id
ORDER BY `approved_business`.`id` ASC
您应该使用GROUP_CONCAT()
函数将结果组合成一行。
例如:
SELECT GROUP_CONCAT(Real_Cash_Offers.offer)
FROM ...
您正在尝试跨查询的结果行进行连接。这不是SQL(ANSI)标准的一部分,但您可以使用特定于DBMS的函数来实现这一点。对于将是GROUP_CONNCT的MySQL,请参阅我可以将多个MySQL行连接到一个字段中吗?
SELECT business_stores.business_id, business_stores.store_id, GROUP_CONCAT(Real_Cash_Offers.offer) as offers
FROM approved_business
INNER JOIN business_stores
ON approved_business.id = business_stores.business_id
INNER JOIN Real_Cash_Offers
ON Real_Cash_Offers.storeid = business_stores.storeid
GROUP BY business_stores.business_id, business_stores.store_id
ORDER BY `approved_business`.`id` ASC