How to write a SQL

Question

How to write a SQL to select the TABLE_1 result which (TABLE_1.NUMBER_1 > TABLE_2.NUMBER_2) and (TABLE_1.DATE_1 > TABLE_2.DATE_2)

SQL result would be:

TABLES_1

NUMBER_1 , DATE_1
5          2015/4/1

Source Tables :

TABLE_1

NUMBER_1 , DATE_1
3          2015/3/1
4          2015/2/1
5          2015/4/1
6          2015/1/1

TABLE_2

NUMBER_2 , DATE_2
3          2015/2/1
3          2015/3/1

The answer is basically just a `JOIN` whose joining condition is exactly what you wrote in the first paragraph. — Barmar, Mar 31 '15 at 01:48

score 0 · Answer 1 · answered Mar 31 '15 at 01:50

0

Use Exists operator.

select * 
from TABLE_1 A
Where exists 
(
select 1 
from TABLE_2 B
Where A.NUMBER_1 > B.NUMBER_2 
and A.DATE_1 > B.DATE_2
)

answered Mar 31 '15 at 01:50

Pரதீப்

91,748
19
131
172

score 0 · Answer 2 · edited May 23 '17 at 12:06

0

It depends on data types of date column, is it a date column or string column?

Look the below post that has useful information for date format. mysql date comparison with date_format

edited May 23 '17 at 12:06

Community

1
1

answered Mar 31 '15 at 05:52

dvrnaidu

151
1
1
9

score 0 · Answer 3 · answered Mar 31 '15 at 06:00

0

There are many ways to achieve your result. Here is a simple way using join:

SELECT * 
FROM TABLE_1 JOIN TABLE_2 ON 
          (TABLE_1.NUMBER_1 > TABLE_2.NUMBER_2) AND 
          (TABLE_1.DATE_1 > TABLE_2.DATE_2)

answered Mar 31 '15 at 06:00

benscabbia

17,592
13
51
62

score 0 · Answer 4 · answered Mar 31 '15 at 06:30

Try this. This will take those rows from table1 for values of which there are no rows in table2 breaking your condition:

DECLARE @table1 TABLE
    (
      NUMBER_1 INT ,
      DATE_1 DATE
    )
DECLARE @table2 TABLE
    (
      NUMBER_2 INT ,
      DATE_2 DATE
    )

INSERT  INTO @table1
VALUES  ( 3, '20150301' ),
        ( 4, '20150201' ),
        ( 5, '20150401' ),
        ( 6, '20150101' )

INSERT  INTO @table2
VALUES  ( 3, '20150201' ),
        ( 3, '20150301' )


SELECT  *
FROM    @table1 t1
WHERE   NOT EXISTS ( SELECT *
                     FROM   @table2 t2
                     WHERE  t2.NUMBER_2 >= t1.NUMBER_1
                            OR t2.DATE_2 >= t1.DATE_1 )

Output:

NUMBER_1    DATE_1
5           2015-04-01

score 0 · Answer 5 · answered Mar 31 '15 at 07:16

0

How about this version?

SELECT DISTINCT T1.*
FROM TABLE_1 T1 
    INNER JOIN TABLE_2 T2 ON T1.NUMBER1 > T2.NUMBER2
        AND T1.DATE1 > T2.DATE2

Here is a SQLFiddle with how the code works on your sample data.

answered Mar 31 '15 at 07:16

Radu Gheorghiu

20,049
16
72
107

How to write a SQL

5 Answers5