query - returning 1 row

[Destramic]

SELECT i.item_id, 
		         i.title, 
		         i.price,
		         i.p_and_p,
		         SUM(i.price + i.p_and_p) AS `total_price`,
		         i.listing,
		         i.condition,
		         i.start_date_time,
		         i.listing_duration,
		         CONVERT_TZ(DATE_ADD(i.start_date_time, INTERVAL concat(i.listing_duration) DAY), '+00:00', u.time_zone) AS `end_date_time`
		  FROM items i
		  LEFT JOIN sub_categories sc ON sc.sub_category_id = i.sub_category_id
		  LEFT JOIN categories c ON c.name = 'test'
		  JOIN users u
		  WHERE u.username = 'Destramic'
		  AND i.start_date_time < NOW() 
		  AND DATE_ADD(i.start_date_time, INTERVAL concat(i.listing_duration) DAY) >= NOW()

I'm having a problem with my query returning more than 1 rows...I've even copied the row which is returning to see if that'll return 2 rows but it doesn't

 

can anyone explain why this is happening please?

 

 

[Barand]

You are using an aggregation function (viz SUM) without a GROUP BY clause. This will give you a single row with the SUM for the whole table with arbitrary values in the other columns.

[mac_gyver]

and as mentioned in a previous thread, you are not specifying join conditions fully, which is resulting in every row from the left-hand side of the join being joined to every row on the right-hand side of the join without regard to the relationship between those rows.

 

LEFT JOIN categories c ON ... needs to specify how you want to join the item/sub-category information to the category table.

 

JOIN users u ... needs to specify how you want to join the item/sub-category/category information to the users table.

 

lastly, concat(i.listing_duration) <- that's concatenating one value. why not just use the value itself?

[Destramic]

thanks @barand

 

oh ok so if I do

LEFT JOIN categories c ON c.category_id = sc.category_id

WHERE c.name = 'test'

and something similar for users?

 

regarding concat() it was the only way I could get the query working as the field list_duration is a enum just using the value of the field wasn't working

[phpPeter]

You have to define the join on the user-table with JOIN users u ON ...

 

Otherwise, the query will generate the cartesian product of the user-table and the other (joined) tables. This results in duplicates.