SQL deduct query from table1 by another query from table1

Question

i have a query showing me the first bookings done by a client from table_1. In order to find the next booking i thought i use the same query for table_1 with the addition that it mustn't be included in my first query.

Each booking can be identified by an ID. I first thought i can do it like this: [STACKOVERFLOW HELP][1]. I searched this topic, but did not find an answer. Maybe because i am using the wrong phrases. Please see the example below for better understanding.

TABLE_1 (includes all data):

ID  BV  CLIENT  Booking_DATE
1   2   1       01.01.2017
2   35  2       02.01.2017
3   1   3       03.01.2017
4   24  4       04.01.2017
5   1   5       05.01.2017
6   3   3       06.01.2017
7   19  4       07.01.2017
8   523 5       08.01.2017
9   23  4       09.01.2017
10  12  5       10.01.2017
11  51  4       11.01.2017
12  12  5       12.01.2017
13  5   6       13.01.2017

Table 1a - includes only the 1st bookings (per client):

ID  BV  CLIENT  Booking_DATE
1   2   1       01.01.2017
2   35  2       02.01.2017
3   1   3       03.01.2017
4   24  4       04.01.2017
5   1   5       05.01.2017
13  5   6       13.01.2017

Table 1b - Table that includes all from table1 WITHOUT table1a.

ID  BV  CLIENT  Booking_DATE
6   3   3       06.01.2017
7   19  4       07.01.2017
8   523 5       08.01.2017
9   23  4       09.01.2017
10  12  5       10.01.2017
11  51  4       11.01.2017
12  12  5       12.01.2017

Any ideas? From Table 1b i then want to take the '1st booking' again, thus basically i am looking when people from table1a have made their first rebooking.

THANK YOU!

The full query for Table1a looks like this (above i have just provided a sample to understand the logic):

SELECT s_aufgaben.IDAS ID, s_aufgaben.AN_ID AS USER, s_aufgaben.BEN_ID AS BUSINESS, s_aufgaben.ERLEDIGT_DATUM, s_aufgaben.Gesamtbetragnetto, s_aufgaben.SET_DATE, s_benutzer_auftragnehmer.REGISTRATION_DATE FROM s_aufgaben RIGHT JOIN s_benutzer_auftragnehmerON (s_aufgaben.AN_ID = s_benutzer_auftragnehmer.BEN_ID) WHERE ((((s_aufgaben.AN_ID > 0) AND (s_aufgaben.status = '3')) AND (s_aufgaben.ERLEDIGT_DATUM > '0000-00-00 00:00:00')) AND (s_aufgaben.BEN_ID<> '41385.00')) Group by USER ORDER BY s_aufgaben.ERLEDIGT_DATUM asc

[1]: How to select all records from one table that do not exist in another table?enter code here

MySQL or sql-server (MS SQL) dont tag both databases. please fix the correct tag — Raymond Nijland, May 18 '17 at 14:12
IF I USE QUERY TABLE1 WHERE NOT EXISTS QUERY TABLE 1a, the result is 0. No Data found. I know though, that data exists. — , May 18 '17 at 15:21

score 0 · Answer 1 · answered May 18 '17 at 14:23

This may helps you

;WITH Table1a(ID,BV,CLIENT,Booking_DATE)
AS
(
SELECT 1 ,  2  , 1 ,      '01.01.2017' Union all
SELECT 2 ,  35 , 2 ,      '02.01.2017' Union all
SELECT 3 ,  1  , 3 ,      '03.01.2017' Union all
SELECT 4 ,  24 , 4 ,      '04.01.2017' Union all
SELECT 5 ,  1  , 5 ,      '05.01.2017' Union all
SELECT 13,  5  ,  6,      '13.01.2017' 
)
,Table1b (ID,  BV,  CLIENT,  Booking_DATE)
AS
(
SELECT 6 ,  3  , 3 ,'06.01.2017' union all
SELECT 7 ,  19 , 4 ,'07.01.2017' union all
SELECT 8 ,  523, 5 ,'08.01.2017' union all
SELECT 9 ,  23 , 4 ,'09.01.2017' union all
SELECT 10,  12 , 5 ,'10.01.2017' union all
SELECT 11,  51 , 4 ,'11.01.2017' union all
SELECT 12,  12 , 5 ,'12.01.2017' 
)
SELECT * from Table1a A
WHERE NOT EXISTS (SELECT 1 From Table1b B WHERE B.CLIENT=A.CLIENT)

The Table1a contains over 16k rows. Your solution might be a bit difficult to implement? — , May 18 '17 at 14:27

score 0 · Answer 2 · answered May 18 '17 at 14:49

0

Are you looking for except as below:

select * from table_1  --Better to provide exact column list in sequence
except
select * from table1a  --Follow the same sequence here as well

Output as below:

+----+-----+--------+--------------+
| id | bv  | client | booking_date |
+----+-----+--------+--------------+
|  6 |   3 |      3 | 2017-01-06   |
|  7 |  19 |      4 | 2017-01-07   |
|  8 | 523 |      5 | 2017-01-08   |
|  9 |  23 |      4 | 2017-01-09   |
| 10 |  12 |      5 | 2017-01-10   |
| 11 |  51 |      4 | 2017-01-11   |
| 12 |  12 |      5 | 2017-01-12   |
+----+-----+--------+--------------+

answered May 18 '17 at 14:49

Kannan Kandasamy

13,405
3
25
38

HI Kannan, MySQL does not support the EXCEPT operator :-/ – May 18 '17 at 15:09
No problems: When using 'NOT IN' it returns me this error: #1241 - Operand should contain 1 column(s). With LEFT JOIN i would have to name the table1a query as another 'table' (smth. like a virtual table). But i have no idea how that could work. – May 18 '17 at 15:15

SQL deduct query from table1 by another query from table1

2 Answers2