Link to home
Start Free TrialLog in
Avatar of movieprodw
movieprodw

asked on

SQL Query

Hello,

I have the following query and it results in 8 rows of the top votes.

Now I need to join the query by 'the_id' to the table 'image_data' column 'id' so I can get the corresponding values out of the image_data


SELECT
	SUM(c.vote_val) AS the_value,
	c.elementid AS the_id
FROM
	mini_smlm_vote c
GROUP BY
	c.elementid
ORDER BY
	the_value DESC
LIMIT 8;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of movieprodw
movieprodw

ASKER

I tried this but am getting an error

Unknown column 'c.the_id' in 'on clause'


SELECT  SUM(c.vote_val) AS the_value
       ,c.elementid AS the_id
       ,i.original_name
FROM    mini_smlm_vote c
INNER JOIN image_data i ON i.id = c.the_id
GROUP BY c.elementid
       ,i.id
ORDER BY the_value DESC

Open in new window

got it!

thank you

SELECT
      SUM(c.vote_val) AS the_value,
      c.elementid AS the_id,
      i.original_name
FROM
      mini_smlm_vote c
INNER JOIN image_data i ON i.id = c.elementid
GROUP BY
      c.elementid,
      i.id
ORDER BY
      the_value DESC
how do I add a WHERE?

I need to add

WHERE i.category_id = '2'

Open in new window


to

SELECT
	SUM(c.vote_val) AS the_value,
	c.elementid AS the_id,
	i.*
FROM
	mini_smlm_vote c
INNER JOIN image_data i ON i.id = c.elementid 
GROUP BY
	c.elementid,
	i.id
ORDER BY
	the_value DESC

Open in new window

Got it
SELECT
	SUM(c.vote_val) AS the_value,
	c.elementid AS the_id,
	i.*
FROM
	mini_smlm_vote c
INNER JOIN image_data i ON i.id = c.elementid AND image_category = '1'
GROUP BY
	c.elementid,
	i.id
ORDER BY
	the_value DESC
LIMIT
8

Open in new window

Thank you!