MySql select joined rows that don't exists in third table - mysql

I'm having difficulties with creating a sql query that selects a joining of 2 tables where the relationship doesn't exists in a third table.
To be more specific I try to explain it with an example.
I have a table member, form and member_form.
When a member inserts an answer in the form, it's saved in the member_form table.
The forms can be active, and have a deadline.
So what I want is to return a result for each member and the form he still didn't fill in. But the form must be active, and deadline must be for example 2018-03-15. I don't know the amount of members or forms. So I can't say from select * where member id is = x.
This is needed to send a reminder mail to all the users that didn't fill in the form yet for a specific date.
member
id | name | email
--------------------------
1 | Test | test#email.com
2 | test2 | test2#email.com
4 | test4 | test4#email.com
5 | test5 | test5#email.com
6 | test6 | test6#email.com
7 | test7 | test7#email.com
form
id | insert_date | deadline_date | active
---------------------------------------------------------------
1 | 2018-03-15 00:00:00 | 2018-03-15 00:00:00 | 1
2 | 2018-02-10 00:00:00 | 2018-05-15 00:00:00 | 0
3 | 2018-03-15 00:00:00 | 2018-03-15 00:00:00 | 1
5 | 2018-03-15 00:00:00 | 2018-06-15 00:00:00 | 1
6 | 2018-03-15 00:00:00 | 2018-05-15 00:00:00 | 1
7 | 2018-03-15 00:00:00 | 2018-04-15 00:00:00 | 0
member_form
member_id | form_id | answer
--------------------------------------
1 | 6 | 1
1 | 2 | 2
1 | 5 | 1
2 | 2 | 1
2 | 3 | 1
4 | 6 | 2
5 | 6 | 3
5 | 7 | 2
6 | 1 | 2
7 | 2 | 1
Result
member_id | name | email | form_id | insert_date | deadline_date | active
-------------------------------------------------------------------------------------------------------------------
2 | test2 | test2#email.com | 6 | 2018-03-15 00:00:00 | 2018-05-15 00:00:00 | 1
6 | test6 | test6#email.com | 6 | 2018-03-15 00:00:00 | 2018-05-15 00:00:00 | 1
7 | test7 | test7#email.com | 6 | 2018-03-15 00:00:00 | 2018-05-15 00:00:00 | 1

SELECT * FROM form AS f , member AS m WHERE f.deadline_date = '2018-05-15 00:00:00' AND f.active = 1 and !exists(select * FROM member_form AS amf WHERE amf.member_id = am.id And amf.form_id = af.id);

Try this
SELECT * FROM Member m, Form f
WHERE CONCAT(m.id,':',f.id) NOT IN
(SELECT CONCAT(mf.member_id, ':', mf.form_id) FROM Member_form mf)
AND f.deadline_date = '2018-05-15'
AND f.active = 1

Related

mysql query sum values group by month but divided in years

I have a table with 4 columns: ID, fieldDATE, fieldINT1, fieldINT2.
Table is like this:
ID| fieldDate | FieldINT1 | FiledINT2 |
===================================
1 | 2016-01-01 | 100 | 1 |
2 | 2016-01-08 | 200 | 1 |
3 | 2016-02-01 | 150 | 1 |
4 | 2016-02-05 | 400 | 2 |
5 | 2017-01-01 | 120 | 1 |
6 | 2017-01-21 | 123 | 1 |
7 | 2017-02-03 | 30 | 1 |
8 | 2018-01-01 | 123 | 1 |
9 | 2018-01-03 | 30 | 1 |
I'd like to create a table with 12 rows, with the first column is the month name, and the other columns are sum of fieldINT1 and fieldINT2, group by month in a specific YEAR. So in my example, there will be 4 columns ( MONTH NAME, 2016, 2017, 2018)
How can I do it?

SQL: Searching X table and returning rows from Y based on X data

Search goals for rows where userid = 1.
Using returned rows, search checkin where biometricid's match, along with the userid, and filter out rows that are older than the goal date.
Note: Both the userid and biometricid are foreign tables.
How may I do this with one query?
checkin
id | userid | date | biometricid | value
1 | 1 | 2015-01-10 00:00:00 | 1 | 9
2 | 1 | 2000-05-11 00:00:00 | 1 | 7
3 | 2 | 2015-01-10 00:00:00 | 1 | 9
4 | 1 | 2015-01-10 00:00:00 | 2 | 1
5 | 1 | 2017-01-11 00:00:00 | 1 | 4
goals
id | userid | date | biometricid | value
1 | 1 | 2000-01-05 00:00:00 | 1 | 3
2 | 1 | 2015-01-01 00:00:00 | 2 | 2
3 | 2 | 2015-01-01 00:00:00 | 1 | 2
desired result
id | date | biometricid | value
1 | 2015-01-10 00:00:00 | 1 | 9
2 | 2017-01-11 00:00:00 | 1 | 4
Due to the way you worded the question, and the fact that it seems many other questions I've answer recently used them, I was tempted to show a subselect in a WHERE; but I realize your question can be answered much more simply:
SELECT c.*
FROM goals AS g
INNER JOIN checkin AS c ON g.userid = c.userid
AND g.biometricid = c.biometricid
AND c.`date` >= g.`date`
WHERE g.user_id = 1
;

MySQL filter by GROUP BY result

I don't know if this is something I can achieve effectively with sub queries, or how to even build a query for this. I have to extract some knowledge about people no longer using our system. Imagine we have 3 users with user id 1024, 1234, and 5678; and User 1024 and 1234 are using theme A, and 5678 is using theme B:
$ SELECT * FROM user; | $ SELECT * FROM user_theme;
+------+------+ | +------+-------+
| id | name | | | user | theme |
+------+------+ | +------+-------+
| 1024 | John | | | 1024 | A |
| 1234 | Jane | | | 1234 | A |
| 5678 | Jeff | | | 5678 | B |
+------+------+ | +------+-------+
The usage tracking table appears as this:
$ SELECT * FROM user_usage;
+----+------+---------------------+------+
| id | user | date | uses |
+----+------+---------------------+------+
| 1 | 1234 | 2014-08-02 00:00:00 | 5 |
| 2 | 1234 | 2014-08-03 00:00:00 | 5 |
| 3 | 1234 | 2014-08-04 00:00:00 | 3 |
| 4 | 1234 | 2014-08-05 00:00:00 | 6 |
| 5 | 1024 | 2014-08-02 00:00:00 | 8 |
| 6 | 1024 | 2014-08-03 00:00:00 | 7 |
| 7 | 1024 | 2014-08-04 00:00:00 | 4 |
| 8 | 1024 | 2014-08-05 00:00:00 | 6 |
| 9 | 1024 | 2014-09-02 00:00:00 | 1 |
| 10 | 1024 | 2014-09-03 00:00:00 | 2 |
| 11 | 1024 | 2014-09-04 00:00:00 | 3 |
| 12 | 1024 | 2014-09-05 00:00:00 | 4 |
| 13 | 5678 | 2014-08-02 00:00:00 | 8 |
| 14 | 5678 | 2014-08-03 00:00:00 | 7 |
| 15 | 5678 | 2014-08-04 00:00:00 | 4 |
| 16 | 5678 | 2014-08-05 00:00:00 | 6 |
| 17 | 5678 | 2014-09-02 00:00:00 | 1 |
| 18 | 5678 | 2014-09-03 00:00:00 | 2 |
| 19 | 5678 | 2014-09-04 00:00:00 | 3 |
| 20 | 5678 | 2014-09-05 00:00:00 | 4 |
+----+------+---------------------+------+
I want to find out the break down to see how many usage have dropped from our system in 2014-09 (aka: have usage data in 2014-08, but no longer in 2014-09), grouped by the theme. So I want to write something like:
SELECT
user_theme.theme,
SUM(user_usage.users) 'uses lost'
FROM
user_theme
LEFT JOIN user_usage
ON user_theme.user = user_usage.user
WHERE
...
GROUP BY
user_theme.theme
# HAVING ...?
And get result such as:
+-------+-----------+
| theme | uses lost |
+-------+-----------+
| A | 19 |
| B | 0 |
+-------+-----------+
Where the 19 comes from SUM(uses) for WHERE user = 1234 AND YEAR(date) = 2014 AND MONTH(date) = 8.
I don't know I care about the SUM(uses) from user = 1234 in advance, because I only know I need to include user 1234 in the SUM(uses)'s WHERE clause because SUM(uses) for WHERE user = 1234 AND YEAR(date) = 2014 AND MONTH(date) = 9 is 0.
There's actually a lot of users, and a handful of themes (around 20K users, and about 10 themes), so ideally, I think I'd like to avoid doing the filtering in code as opposed to directly in the database. Is there a way to do this effectively in MySQL using raw SQL queries?
Here is query which compares current month with previous one:
set #current_month = now();
set #previous_month = date_sub(#current_month, interval 1 month);
set #current_month = concat(year(#current_month), month(#current_month));
set #previous_month = concat(year(#previous_month), month(#previous_month));
select a.`theme`, sum(ifnull(b.uses_lost,0)) as uses_lost
from
`user_theme` as a
left outer join
(
select `user`, sum(uses) as uses_lost
from `user_usage`
where concat(year(`date`), month(`date`)) = #previous_month
and `user` not in (
select `user`
from `user_usage`
where concat(year(`date`), month(`date`)) = #current_month)
group by `user`
) as b
on (a.`user`=b.`user`)
group by a.`theme`;
fiddle for play
main idea is to find all users who used system during last month and has no rows during current month

MySQL query grouped by contigious foreign key values

I have data like this:
+----+-------------------------+----------+----------+
| ID | DateReceived | Quantity | VendorID |
+----+-------------------------+----------+----------+
| 1 | 2010-08-09 06:53:44.783 | 2 | 1 |
| 2 | 2010-08-01 13:31:26.893 | 1 | 1 |
| 3 | 2010-07-26 07:52:29.403 | 2 | 1 |
| 4 | 2011-03-22 13:31:11.000 | 1 | 2 |
| 5 | 2011-03-22 13:31:11.000 | 1 | 2 |
| 6 | 2011-03-22 11:27:01.000 | 1 | 2 |
| 7 | 2011-03-18 09:04:58.000 | 1 | 1 |
| 8 | 2011-12-17 08:21:29.000 | 1 | 3 |
| 9 | 2012-08-10 10:55:20.000 | 9 | 3 |
| 10 | 2012-08-02 20:18:10.000 | 5 | 1 |
| 11 | 2012-07-12 20:44:36.000 | 3 | 1 |
| 12 | 2012-07-05 20:45:29.000 | 1 | 1 |
| 13 | 2013-03-22 13:31:11.000 | 1 | 2 |
| 14 | 2013-03-22 13:31:11.000 | 1 | 2 |
+----+-------------------------+----------+----------+
I want to sort the data by the DateReceived and sum the Quantity. BUT, I want to sum the Quantity grouped by the VendorID as long as they are adjacent (when sorted by DateReceived) like the example output below.
+----------+----------+
| VendorID | Quantity |
+----------+----------+
| 1 | 5 |
| 2 | 3 |
| 1 | 1 |
| 3 | 10 |
| 1 | 9 |
| 2 | 2 |
+----------+----------+
I think the answer has something to do with variables, but I can't think through how to do it.
What is a MySQL query to generate the desired output?
note: I asked the exact same thing here but for MS Sql, I now need this for MySQL.
select
VendorID,
SUM(Quantity)
from (
select
t.*,
#grn := if(#prev != VendorID, #grn + 1, #grn) as grn,
#prev := VendorID
from
t
, (select #grn := 0, #prev := null) var_init
order by DateReceived
) sq
GROUP BY grn
But your expected output is wrong. You can see this by executing just this:
select
t.*,
#grn := if(#prev != VendorID, #grn + 1, #grn) as grn,
#prev := VendorID
from
t
, (select #grn := 0, #prev := null) var_init
order by DateReceived
Which results in:
| ID | DATERECEIVED | QUANTITY | VENDORID | GRN | #PREV := VENDORID |
|----|---------------------------------|----------|----------|-----|-------------------|
| 3 | July, 26 2010 07:52:29+0000 | 2 | 1 | 0 | 1 |
| 2 | August, 01 2010 13:31:26+0000 | 1 | 1 | 0 | 1 |
| 1 | August, 09 2010 06:53:44+0000 | 2 | 1 | 0 | 1 |
| 7 | March, 18 2011 09:04:58+0000 | 1 | 1 | 0 | 1 |
| 6 | March, 22 2011 11:27:01+0000 | 1 | 2 | 1 | 2 |
| 4 | March, 22 2011 13:31:11+0000 | 1 | 2 | 1 | 2 |
| 5 | March, 22 2011 13:31:11+0000 | 1 | 2 | 1 | 2 |
| 8 | December, 17 2011 08:21:29+0000 | 1 | 3 | 2 | 3 |
| 12 | July, 05 2012 20:45:29+0000 | 1 | 1 | 3 | 1 |
| 11 | July, 12 2012 20:44:36+0000 | 3 | 1 | 3 | 1 |
| 10 | August, 02 2012 20:18:10+0000 | 5 | 1 | 3 | 1 |
| 9 | August, 10 2012 10:55:20+0000 | 9 | 3 | 4 | 3 |
| 13 | March, 22 2013 13:31:11+0000 | 1 | 2 | 5 | 2 |
| 14 | March, 22 2013 13:31:11+0000 | 1 | 2 | 5 | 2 |
Result of the whole query:
| VENDORID | SUM(QUANTITY) |
|----------|---------------|
| 1 | 6 |
| 2 | 3 |
| 3 | 1 |
| 1 | 9 |
| 3 | 9 |
| 2 | 2 |
sqlfiddle

Multiple Conditional Where Clause SQL

I have two tables, 1 of which has different values for different days, and the other determines which data I should be looking at from the first table. Here's an example:
mysql> select * from test_table;
+----+---------+---------------------+-------+
| id | test_id | ymd | value |
+----+---------+---------------------+-------+
| 1 | 1 | 2013-01-01 00:00:00 | 5 |
| 2 | 1 | 2013-01-02 00:00:00 | 5 |
| 3 | 1 | 2013-01-03 00:00:00 | 5 |
| 4 | 2 | 2013-01-01 00:00:00 | 5 |
| 5 | 2 | 2013-01-02 00:00:00 | 2 |
| 6 | 2 | 2013-01-03 00:00:00 | 3 |
| 7 | 2 | 2013-01-04 00:00:00 | 4 |
| 8 | 2 | 2013-01-05 00:00:00 | 5 |
| 9 | 3 | 2013-01-06 00:00:00 | 6 |
+----+---------+---------------------+-------+
and
mysql> select * from test_ymd;
+----+---------+---------------------+
| id | test_id | ymd |
+----+---------+---------------------+
| 1 | 1 | 2013-01-02 00:00:00 |
| 2 | 2 | 2013-01-03 00:00:00 |
+----+---------+---------------------+
I want to write a query like this:
mysql-local> select * from test_table where (test_id=1 and ymd>'2013-01-02') or (test_id=2 and ymd>'2013-01-03');
+----+---------+---------------------+-------+
| id | test_id | ymd | value |
+----+---------+---------------------+-------+
| 3 | 1 | 2013-01-03 00:00:00 | 5 |
| 7 | 2 | 2013-01-04 00:00:00 | 4 |
| 8 | 2 | 2013-01-05 00:00:00 | 5 |
+----+---------+---------------------+-------+
However, for a large # of test_ids, this obviously become gross. Is there a quick and easy way to do this in mysql?
UPDATE
A join is a good way to do this (Thanks Gordon)
mysql-local> select tt.* from test_table tt join test_ymd tymd on tt.test_id = tymd.test_id and tt.ymd > tymd.ymd;
+----+---------+---------------------+-------+
| id | test_id | ymd | value |
+----+---------+---------------------+-------+
| 3 | 1 | 2013-01-03 00:00:00 | 5 |
| 7 | 2 | 2013-01-04 00:00:00 | 4 |
| 8 | 2 | 2013-01-05 00:00:00 | 5 |
+----+---------+---------------------+-------+
I'm also curious though about whether or not there is a way to do it in the where clause.
You want a join:
select tt.*
from test_table tt join
test_ymd tymd
on tt.test_id = tymd.test_id and tt.ymd > tymd.ymd;
EDIT:
You can do this with an explicit join. A typical way would be to use exists:
select tt.*
from test_table tt
where exists (select 1
from test_ymd tymd
where tt.test_id = tymd.test_id and tt.ymd > tymd.ymd
);
If you have an index on test_ymd(test_id, ymd), then the exists has an advantage. If you have duplicate rows in the test_ymd table for one id, there is no danger of getting duplicates in the results.
Join two tables like
select temp.* from test_table temp join test_ymd temptymd
on temp.test_id = temptymd.test_id and temp.ymd > temptymd.ymd;

Resources