Need help building sql query

debugcn Published at Dev

DVB Support

select max(case when field_number = 7 then value end) value from wp_rg_lead_detail group by lead_id having max(case when field_number = 21 then value end) > curdate()I already tried my best reading documentation and searching on Google but i can't figure it out..

I need to build a query where it selects the value of field_number 7 if field_number 19 has the value 1 and the date of field_number 21 is past the current date.

This is what I currently have:

SELECT * FROM wp_rg_lead_detail WHERE field_number IN (7, 19) AND value = 1

But that only gives me the row where field_number is 19

EDIT: I need to get the value of field_number 7 where field_number 19 is past the current date. There can be multiple inctances where field_number is 7 (each lead_id is a different "person" of which i need the field_number 7 value) Where field_number 22 has the value 1 is no longer applicable

EDIT2: Hey guys thanks! With your help the problem is now solved. The code that i'm using now is (thank you sgeddes):

SELECT value FROM wp_rg_lead_detail WHERE field_number = 7 AND EXISTS (SELECT field_number, value FROM wp_rg_lead_detail WHERE field_number = 21 AND value > CURDATE())

sgeddes

You could use group by with max and case. I'm assuming the unique group is lead_id and form_id:

select max(case when field_number = 7 then value end) value
from wp_rg_lead_detail
group by lead_id, form_id
having max(case when field_number = 19 then value end) = 1 
    and max(case when field_number = 21 then value end) > curdate()

The last part depends a little on which database you are using (curdate is mysql, getdate() is sql server), but this should give you the general idea. You may need to cast the value to a date as well.

SQL Fiddle Demo

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-2

Comments

0 comments

From Dev

Related Related

Article

Need help building sql query

Need help building sql query

SQL need help building a query

Need help building the best performing SQL query

Need help on a SQL query

Need help for SQL query?

Need help building MySQL Query - Dynamic Data

Need help building query laravel 5.2

Need help in understanding this SQL query

Need help in making SQL query

Need Help In Sql like query

Need SQL Query Help Please

Need help to understand SQL query

Need help in understanding this SQL query

Need Help Optimizing SQL query

SQL - need help creating query

Need help for Oracle SQL query

need help to generate SQL query

need help building one mysql query comparing a variable with two tables

Need help on SQL query (self join)

T-SQL need help to complete a query

Need Help Speeding Up a SQL Server Query

Need help for SQL Query SQLserver or Access

Need help writing an SQL query for a select statement

Need help for writing a complex SQL query

Need help building JSONObject java

Need help building tokenizer/tokenfield

Need help on a query

Need help in fetch query

need help Mysql query

Need Help in Writing a Query