If you cross join your Week and Student tables to get all combinations, and then use not exists to determine where no TimeSheet record exists. select W.WeekID, S.StudentID from [Week] W cross join Student S where not exists (select 1 from TimeSheet T where T.StudentID = S.StudentID and T.WeekID = W.WeekID); 0 solved I have … Read more
You can use http://sqlfiddle.com/ to test your queries. There you can chose a database (mysql, Oracle, Sql Server or other), generate test tables, fill them with test data and generate test queries to this data 17 solved Where can beginners run their SQL code?If it’s a PL,then why it doesn’t have a compiler? [closed]
You can still use having, but test for each product — and for the absence of any other product: SELECT o.order_id FROM orders o GROUP BY o.order_id HAVING SUM( product_id = 222 ) > 0 AND SUM( product_id = 555 ) > 0 AND SUM( product_id NOT IN (222, 555) ) = 0 ; Note … Read more
I think there should be A.created_date instead of A.created_at select `plans`.`name`, `A`.`subscription_id`, `A`.`amount`, `A`.`created_date`, `A`.`end_date`, `A`.`subscription_status`, `users`.`email`, `A`.`plan_id`, `A`.`user_id`, `usage`.`created_at` as `usagedate`, COUNT(usage.id) as used_count from `subscriptions` A left join `users` on `users`.`id` = `A`.`user_id` left join `plans` on `A`.`plan_id` = `plans`.`Id` left join `usage` on `A`.`user_id` = `usage`.`user_id` where `usage`.`created_at` between A.created_date and A.end_date … Read more
How can I optimize a query that returns a lot of records, then order by a date field and return only the latest one? [duplicate] solved How can I optimize a query that returns a lot of records, then order by a date field and return only the latest one? [duplicate]