Get date field by subquery

Question

Get date field by subquery

Navigation

#1 by (1 votes)

0

Dear, I am working in the construction of a stored procedure, I need to obtain a date field from a Table B depending on a particular state, by means of a subquery in a table A, I explain:
Table A contains a product and table B stores the historical movements of some record of table A, by means of a query I need to bring the data from table A and a date of a specific movement from table B.

Table A = lead

Table B = lead_history

Query (Contains a where for an id to work only for 1 case, the state to look for in table B, is 14):

select l.*
,(SELECT lh.execution_date FROM lead_history lh join lead l ON lh.lead_id = l.id WHERE lh.lead_state_id=l.lead_state_id ) AS FechaPropuestaGanada
 from lead l where (l.lead_state_id=14) and l.id=22142

Result of the executed query:

Msg 512, Level 16, State 1, Line 4
La subconsulta ha devuelto más de un valor, lo que no es correcto cuando va a continuación de =, !=, <, <=, >, >= o cuando se utiliza como expresión.

Note: Table B stores all state changes for each Product in table A, but no state is repeated (ex: there are no 2 records in the "Won" status for a product in table A, only 1)

If I separate the subquery by an ID from table A in specific, it would be like this:

 SELECT lh.execution_date FROM lead_history lh join lead l ON lh.lead_id = l.id WHERE lh.lead_state_id=14 and lh.lead_id = 22142

and my result is this:

2017-06-21 11:41:22.303

That's what I need, but I can not add it to the main query (this result is saved in an additional field of the main Query, with the alias'GuidedPropDate ')

sql sql-server query

asked by Ariel Ignacio 21.06.2017 в 18:17

source

1 answer

Fill table in nodejs (jade) of an sql query Visual Basic Equivalence to C #

score 1 · Accepted Answer

As explained in your question, analyzing your sentence and having no structure or data as a reference, I can say that the solution would be to make a INNER JOIN like this:

SELECT l.* 
FROM lead AS l
INNER JOIN lead_history AS lh
    ON(l.id = lh.lead_id AND l.lead_state_id = lh.lead_state_id)
WHERE l.lead_state_id = 14 AND l.id = 22142

I do not see the need for the subquery, since from what I see in your sentence, you compare lead_state_id in the same way as id . Greetings.