SQLite Forum

SQL lite irrespective query
Login

SQLite irrespective query

(1.1) Originally by anonymous with edits by Richard Hipp (drh) on 2021-08-12 13:54:26 from 1.0 [link] [source]

I am trying to retrieve the data from the table shoppers. I have to retrieve the shoppers who joined the company after 01-01-2020, and all woman irrespective of when they joined. how can I create the query to meet this 2 conditions .

SELECT shopper_first_name, shopper_surname, shopper_email_address,IFNULL(gender, 'Not known' ) AS 'Gender',STRFTIME('%d-%m-%Y', date_joined) AS 'date_joined', STRFTIME('%Y',date('now'))- STRFTIME('%Y',date(date_of_birth)) AS 'Age'
FROM shoppers
HAVING gender = 'M'
AND date_joined >= '2020-01-01' 
ORDER BY gender,Age DESC; 

In works for all shoppers but I don't know how to do woman irrespective.

Thank You in advance

(2) By Ryan Smith (cuz) on 2021-08-12 13:33:05 in reply to 1.0 [link] [source]

SELECT shopper_first_name, shopper_surname, shopper_email_address,
       IFNULL(gender, 'Not known' ) AS 'Gender',
       STRFTIME('%d-%m-%Y', date_joined) AS 'date_joined',
       STRFTIME('%Y',date('now'))- STRFTIME('%Y',date(date_of_birth)) AS 'Age'
  FROM shoppers
 WHERE (gender = 'F') OR (gender = 'M' AND date_joined >= '2020-01-01')
ORDER BY gender,Age DESC
; 

Should do it.

(3) By Stephan Beal (stephan) on 2021-08-12 13:37:50 in reply to 2 [link] [source]

WHERE (gender = 'F') OR (gender = 'M' AND date_joined >= '2020-01-01')

Since the db schema allows for NULL genders, this slight tweak seems appropriate:

WHERE (gender = 'F') OR (gender IS NOT 'F' AND date_joined >= '2020-01-01')

(Noting that IS NOT is required instead of <>.)

(7) By anonymous on 2021-08-12 13:46:23 in reply to 3 [link] [source]

Sorry, You just gave me the answer. Guys thank You so much for your help and quick reply. I will register on the forum because I can learn so much from You. Probably I will have more questions soon but for now, thank You so much.

(8) By Stephan Beal (stephan) on 2021-08-12 13:59:18 in reply to 7 [link] [source]

Sorry, You just gave me the answer.

Note that the formulations from David and Donald are simpler and should be preferred.

(5) By anonymous on 2021-08-12 13:43:17 in reply to 2 [link] [source]

Perfect that works ! , but i also have not null value, how should I display it ?

(4) By David Raymond (dvdraymond) on 2021-08-12 13:40:35 in reply to 1.0 [source]

HAVING is only for GROUP BY queries, where you're applying the filter to the resulting groups, and not to the rows before they're grouped.

In this case you can just write it like you have the restrictions in English.
the shoppers who joined the company after 01-01-02020 or are a woman
...

FROM shoppers               -- the shoppers
WHERE                       -- who
date_joined >= '2020-01-01' -- joined the company after 01-01-02020
OR                          -- or
gender = 'F'                -- are a woman
...


Also, aliases should not be in single quotes, that's for string literals. Either use no quotes or double quotes to show it's an identifier. SQLite will probably let you do it, but it's being kind.

... AS Age
or
... AS "Age"
but not
... AS 'Age'

(6) By Donald Griggs (dfgriggs) on 2021-08-12 13:44:04 in reply to 1.0 [link] [source]

Would the following simple WHERE clause do what's needed? (BTW, it will include those of unspecified gender who joined recently.)

SELECT shopper_first_name, shopper_surname,    shopper_email_address,IFNULL(gender, 'Not known' ) AS 'Gender',STRFTIME('%d- 
 %m-%Y', date_joined) AS 'date_joined', STRFTIME('%Y',date('now'))-
 STRFTIME('%Y',date(date_of_birth)) AS 'Age'
FROM shoppers
WHERE gender = 'F'
OR date_joined >= '2020-01-01' 
ORDER BY gender,Age DESC;