• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

SQL Syntax

Each key has three types.
I would only like to display the keys where the sDate or eDate IS NOT NULL and its a type 2.
 If the key's sDate or eDate IS NOT NULL and its a type 3 then skip all records pertaining to that key.

key      type      sDate      eDate
12      1      12/01/09      12/02/2009
12      2      12/01/09      12/02/2009
12      3      12/01/09      12/02/2009

15      1      12/01/09      12/02/2009
15      2      NULL      NULL
15      3      NULL      NULL      

20      1      12/01/09      12/02/2009
20      2      12/01/09      12/02/2009
20      3      NULL      NULL      

Desired output
key      type      sDate      eDate
20      1      12/01/09      12/02/2009
20      2      12/01/09      12/02/2009
0
Say55ow
Asked:
Say55ow
1 Solution
 
chapmandewCommented:
select *
from tablename
where
(sdate is not null or edate is not null) and
type = 2
0
 
tigin44Commented:
try this
SELECT A.[key], A.[type], A.sDate,  A.eDate
FROM youTable A
WHERE A.[type] = 2
  AND (A.sDate IS NOT NULL OR A.eDate IS NOT NULL)
  AND NOT EXISTS (SELECT NULL FROM yourTable B WHERE A.[key] = B.[key] AND B.[type] = 3 AND (B.sDate IS NOT NULL OR B.eDate IS NOT NULL))

Open in new window

0
 
Ram4020Commented:
SELECT * FROM Table1
WHERE
      [key] in
                  (
                  SELECT [key] FROM Table1 WHERE sDate is not null AND eDate is not null AND [type] = 2
                  EXCEPT
                  SELECT [key] FROM Table1 WHERE sDate is not null AND eDate is not null AND [type] = 3
                  )
0
 
Ram4020Commented:
Added the last line to match your result.
SELECT * FROM Table1
WHERE
      [key] in
                  (
                  SELECT [key] FROM Table1 WHERE sDate is not null AND eDate is not null AND [type] = 2
                  EXCEPT
                  SELECT [key] FROM Table1 WHERE sDate is not null AND eDate is not null AND [type] = 3
                  )
AND [type] <> 3
0
 
Say55owAuthor Commented:
thanks for your help!
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now