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
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?
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
;
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
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
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;