Menu
I am having trouble to create a select query that will retrieve all users id that isn't in the games and availability between dates.
Example: Retrieve all users that are not between dates 2017-08-10 and 2017-08-12.
Get [3 , 4, 5]
Users
| Id | Name |
| 1 | Jonh |
| 2 | Mark |
| 3 | Caroline |
| 4 | David |
| 5 | George |
Games
| Id | User_Id | Start_Date | End_Date |
| 1 | 1 | 2017-06-01 | 2017-06-01 |
| 2 | 1 | 2017-08-12 | 2017-08-13 |
| 3 | 4 | 2017-08-13 | 2017-08-14 |
Availability
| Id | User_Id | Start_Date | End_Date |
| 1 | 1 | 2017-05-01 | 2017-05-25 |
| 1 | 2 | 2017-08-10 | 2017-08-17 |
| 1 | 3 | 2017-06-20 | 2017-07-10 |
I am using Laravel 5.4 but I'll glad if the answer is on Raw or Eloquent.
318
MySQL LEFT JOIN with Group By ClauseThe Left Join can also be used with the GROUP BY clause. The following statement returns customer id, customer name, qualification, price, and date using the Left Join clause with the GROUP BY clause.
SQL Inner Join permits us to use Group by clause along with aggregate functions to group the result set by one or more columns. Group by works conventionally with Inner Join on the final result returned after joining two or more tables.
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records (if any) from the right table (table2).
MySQL LEFT JOIN 1 Introduction to MySQL LEFT JOIN. The LEFT JOIN allows you to query data from two or more tables. ... 2 MySQL LEFT JOIN examples. Let’s take some examples of using the LEFT JOIN clause. See the following tables customers and orders in the sample database. 3 Condition in WHERE clause vs. ON clause. See the following example. ...
MySQL LEFT JOIN with Group By Clause. The Left Join can also be used with the GROUP BY clause. The following statement returns customer id, customer name, qualification, price, and date using the Left Join clause with the GROUP BY clause. SELECT customers.customer_id, cust_name, qualification, price, date. FROM customers.
The following statement shows how to use the LEFT JOIN clause to join the two tables: When you use the LEFT JOIN clause, the concepts of the left table and the right table are introduced. In the above syntax, t1 is the left table and t2 is the right table. The LEFT JOIN clause selects data starting from the left table ( t1 ).
The following syntax explains the Left Join clause to join the two or more tables: 1 SELECT columns 2 FROM table1 3 LEFT [OUTER] JOIN table2 4 ON Join_Condition;
In SQL, you can use NOT EXISTS:
select *
from users u
where not exists (
select 1
from games g
where u.id = g.user_id
and (
g.start_date between '2017-08-10' and '2017-08-12'
or g.end_date between '2017-08-10' and '2017-08-12'
)
)
and not exists (
select 1
from Availability a
where u.id = a.user_id
and (
a.start_date between '2017-08-10' and '2017-08-12'
or a.end_date between '2017-08-10' and '2017-08-12'
)
);
Another way using LEFT JOIN would be:
select distinct u.*
from t_users u
left join games g on u.id = g.user_id
and (g.start_date between '2017-08-10' and '2017-08-12'
or g.end_date between '2017-08-10' and '2017-08-12')
left join availability a on u.id = a.user_id
and (a.start_date between '2017-08-10' and '2017-08-12'
or a.end_date between '2017-08-10' and '2017-08-12')
where g.user_id is null and a.user_id is null;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
