case help

[Destramic]

hey guys im having a few problems with my query if anyone can please help...the two problems im having is

 

1.the total price returning as 00106. price = 100.00 and p_and_p = 6.00 in the database...im after a figure that looks like 106.00

 

2. @category will be a parameter like :category which will be binded to a value like 'al'l, 'videos', 'dvd's', but if the parameter is ALL i dont want to join my categories table allowing it to select all rows regardless of the category

 

any help, pointer would be truly appreciated...thank you

SELECT @category := 'Videos',
		         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,
		         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
  		  CASE @category != 'All'
          THEN 
		  LEFT JOIN categories c ON c.name = 'Videos'
          END CASE		
		  JOIN users u
		  WHERE u.user_id = '7'
		  AND MATCH (i.title, i.description) AGAINST ('bla' IN BOOLEAN MODE)
		  AND i.start_date_time < NOW() 
		  AND DATE_ADD(i.start_date_time, INTERVAL concat(i.listing_duration) DAY) >= NOW()
		  GROUP BY i.i

[CroNiX]

format(SUM(i.price + i.p_and_p), 2) as AS `total_price`

[Destramic]

that worked great thank you...any ideas with the case?...i could use a php if statement but surly there must be a way with sql?

[CroNiX]

Sorry, I've never attempted that. I haven't been able to find an example of a conditional JOIN and from what I read it wasn't possible. Just about all the answers said to use a IF/CASE in the select and a OUTER JOIN on the table.

[Psycho]

I'm not understanding what you are trying to do with that CASE condition. If I am reading the query correctly you are hard-coding @category to be "Videos". So what is the purpose of the CASE statement to test the value of @category? But, you could just add another condition to the JOIN (if I understand the intent correctly).

 

LEFT JOIN categories c ON c.name = 'Videos' AND @category != 'All'

[Destramic]

 LEFT JOIN sub_categories sc ON sc.sub_category_id = i.sub_category_id
JOIN categories c ON c.category_id = sc.category_id AND (c.name = ':category' OR ':category' != 'All')

that has seemed to work...so if category is ALL then it will show all regardless of the category...but if category is VIDEOS for instance it will load all VIDEOS...thank you for your help guys

[Destramic]

ummm actually it isn't working right...when :category = 'All' it doesn't load all items like i want but just causes a error...probably due to the fact there isn't a category name called All

 

if category ALL i want to load all items regardless of category...but if category = a category then load items within that category =/

 

help anyone please

[Destramic]

just having problem with syntax in the case now...im hoping this will work

SELECT @category = 'Videos',
				         i.item_id, 
		                 i.title, 
		                 i.price,
		                 i.p_and_p,
		                 (i.price + i.p_and_p) AS `total_price`,
		                 i.listing,
		                 i.condition,
		                 CONVERT_TZ(DATE_ADD(i.start_date_time, INTERVAL concat(i.listing_duration) DAY), '+00:00', u.time_zone) AS `end_date_time`,
                         cu.code AS `seller_currency_code`,
				         cu2.code AS `buyer_currency_code`,
				         cu2.symbol AS `buyer_currency_symbol`
		         FROM items i
		         LEFT JOIN sub_categories sc ON sc.sub_category_id = i.sub_category_id
				 LEFT JOIN categories c ON c.category_id = sc.category_id
				 CASE @category
				 WHEN @category != 'ALL' THEN
				 AND c.name = @category
				 END
				 LEFT JOIN users u ON u.user_id = i.user_id 
				 LEFT JOIN currencies cu ON cu.currency_id = u.currency_id
				 LEFT JOIN users u2 ON u2.user_id = '7'
				 LEFT JOIN currencies cu2 ON cu2.currency_id = u2.currency_id
		         AND i.start_date_time < NOW() 
		         AND DATE_ADD(i.start_date_time, INTERVAL concat(i.listing_duration) DAY) >= NOW()
		         GROUP BY i.item_id

[Destramic]

	CASE WHEN @category = 'All' 
                                       THEN 
				                       NULL 
                                       ELSE
				                       LEFT JOIN sub_categories sc ON sc.sub_category_id = i.sub_category_id
				                       JOIN categories c ON c.category_id = sc.category_id AND c.name  = :category
                                   END

i think i've tried them all if statements , case, but keeps returning error in those lines....please has anyone any ideas or advise?

 

the only way possible i can see at the moment is to alter the query via a php if statements...thank you