MySQL query JOIN not really joining

Question

Ok, I think I have missed a group or something, basically what I need to add but dont know how is how do I do I make offers add rows or join them.

here is my query

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

here is the output

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

What I want is to make the offers become an array or something so there is only one row.

NEW CODE

Found an error in the JOIN SQL CODE

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

OUTPUT

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

Please show an example of what you want the output to look like, and the column names of the current SQL output — dorsh, Jan 23 '12 at 21:48

score 0 · Accepted Answer · answered Jan 23 '12 at 21:56

Try 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

score 0 · Answer 2 · answered Jan 23 '12 at 22:00

0

You should use the GROUP_CONCAT() function to combine your results into a single row.

For example:

SELECT GROUP_CONCAT(Real_Cash_Offers.offer)
FROM ...

answered Jan 23 '12 at 22:00

Ike Walker

64,401
14
110
109

score 0 · Answer 3 · edited May 23 '17 at 11:48

You are trying to concatenate across resulting rows of a query. This isn't part of the SQL (ANSI) standard but you can use the DBMS specific function to accomplish this. For MySQL that would be GROUP_CONCAT - see Can I concatenate multiple MySQL rows into one field?

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

MySQL query JOIN not really joining

3 Answers3