Beniamino_Baggins - 11 months ago 61

MySQL Question

I am trying to combine two working sql statements into one working sql statement.

Here are the two sql statements that each work when done seperately:

This first one selects all products and the shops that the product is in, and the categories that the product has, for the shops that are in a specific area. It is for displaying the products on a website, then listing the shops and categories of the product under each product.

`SELECT p.p_id, p.`p_name`, p.brand,`

GROUP_CONCAT( DISTINCT CONCAT(c.c_id, ':', c.c_name) SEPARATOR ', ' ) as categories,

GROUP_CONCAT( DISTINCT CONCAT(s.s_id, ':', s.s_name) SEPARATOR ', ' ) as shops

FROM product p

INNER JOIN product_category pc on p.p_id = pc.p_id

INNER JOIN category c ON c.c_id = pc.c_id

INNER JOIN product_shop ps on p.p_id = ps.p_id

INNER JOIN shop s ON s.s_id = ps.s_id

WHERE s.street = 'college hill'

AND s.city = 'auckland'

AND s.province = 'auckland'

AND s.country = 'new zealand'

AND s.postalCode = '1011'

The results of my top sql statement look like this:

This second sql statement is taken from this link that finds the closest 20 shops to a latlong coordinate in a table of shops. I have adapted it to work with my database:

`SELECT s_id, ( 3959 * acos( cos( radians(-36.8473223`

) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(174.744227

) ) + sin( radians(-36.8473223

) ) * sin( radians( latitude ) ) ) ) AS distance FROM shop HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

It gives this result:

So by marrying these two sql statements, I can select the products that are in shops closest to a latlong coordinate, to display the products on the website, and then their shops and categories for each product.

All I am trying to do is add the distance of the shop from the given latitude and longitude coordinates (entered by the user with a google autocomplete place object), into the shop select results that I posted an image of for the top sql statement in this question. So in the shops results, for each shop just add a distance result which would be something like 5.897. And return the entries with the closest distance.

Here is my current attempt:

`SELECT p.p_id,`

p.`p_name`,

p.brand,

GROUP_CONCAT(DISTINCT CONCAT(c.c_id, ':', c.c_name) SEPARATOR ', '

) as categories,

GROUP_CONCAT(DISTINCT CONCAT(s.s_id, ':', s.s_name, ':',

( 3959 * acos( cos( radians(-36.8473223

) ) * cos( radians( s.latitude ) ) * cos( radians( s.longitude ) - radians(174.744227

) ) + sin( radians(-36.8473223

) ) * sin( radians( s.latitude ) ) ) ) AS distance) SEPARATOR ', '

) as shops

FROM

product p

INNER JOIN product_category pc on p.p_id = pc.p_id

INNER JOIN category c ON c.c_id = pc.c_id

INNER JOIN product_shop ps on p.p_id = ps.p_id

INNER JOIN shop s ON s.s_id = ps.s_id HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

I am getting this error:

## 1583 - Incorrect parameters in the call to native function 'CONCAT'

What can I do to fix the error?

result from answer below:

Answer

You have an "As Distance" within your concat, then finishing the gorup concat as "Shops".

I have rewritten your query. The PREQUERY from shops computes your distance and has your limit of 20 entries... THEN it joins to the rest of the chain. Since "distance" is the computed column, that can now be used in your concat statement.

```
SELECT
p.p_id,
p.p_name,
p.brand,
GROUP_CONCAT( DISTINCT CONCAT(c.c_id, ':', c.c_name)
SEPARATOR ', ' ) as categories,
GROUP_CONCAT(DISTINCT CONCAT(s.s_id, ':', s.s_name, ':',
s.distance ) SEPARATOR ', ' ) as shops
from
( SELECT
s_id,
s.s_name,
( 3959
* acos( cos( radians( -36.8473223 ) )
* cos( radians( latitude ) )
* cos( radians( longitude )
- radians( 174.744227 ) )
+ sin( radians( -36.8473223 ) )
* sin( radians( latitude ) ) ) ) AS distance
FROM
shop
HAVING
distance < 25
ORDER BY
distance
LIMIT
0, 20 ) s
INNER JOIN product_shop ps
ON s.s_id = ps.s_id
INNER JOIN product p
on ps.p_id = p.p_id
INNER JOIN product_category pc
on p.p_id = pc.p_id
INNER JOIN category c
ON pc.c_id = c.c_id
group by
p.p_id,
p.p_name,
p.brand
```