0

hello guys i have a problem to my sql syntax i have two table which selected one is Products and second one is Reviews. problem product that not have review is not retriving data from Database only retrieving is have a review details here is my sql QUERY SYNTAX 

SELECT * FROM products 
INNER JOIN reviews ON products.p_id = reviews.p_id  products.p_id 
WHERE products.service_prom = '{$value}' 
GROUP BY products.p_id  
ORDER BY rand() DESC 
LIMIT 16

if i Used this one it works but a product does not have a reviews

SELECT * FROM products  
WHERE products.service_prom = '{$value}' 
GROUP BY products.p_id  ORDER BY rand() DESC 
LIMIT 16

how can i retrive data from two table with separated query or else single query

here is my table Structures

Here is table or Reviews

here is the table of Products

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
lambutan
  • 11
  • 4

2 Answers2

3

You can use a LEFT JOIN like this:

SELECT * FROM products 
LEFT JOIN reviews ON products.p_id = reviews.p_id
WHERE products.service_prom = '{$value}' 
GROUP BY products.p_id  
ORDER BY rand() DESC 
LIMIT 16

Also have a look at this StackOverflow answer to get the difference of the different joins.

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
1

Try this:

SELECT * FROM products 
LEFT JOIN reviews ON products.p_id = reviews.p_id
WHERE products.service_prom = '{$value}' 
GROUP BY products.p_id  
ORDER BY rand() DESC 
LIMIT 16
Manash Kumar
  • 995
  • 6
  • 13
  • #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'products.p_id WHERE products.service_prom = 'freshipping' GROUP BY products.' at line 2 – lambutan Mar 05 '20 at 12:21
  • Thanks for letting me know, there were a typo. Please try now – Manash Kumar Mar 05 '20 at 12:28