Marco Marco - 5 months ago 9
SQL Question

use values of two fields of a first SELECT into a second one

Let's say we have a database with a table defined by the following fields:

id -- localCode -- localNumber -- status


Hoping to help you: there will be different records with the same combination (localCode + localNumber) but different status.

I would like to use the results from this query

SELECT localCode, localNumber FROM Table WHERE status = 'val_1'


to find the records with the combination (localCode + localNumber) found before but status = 'val_2'.

Thank you all

Answer

Make a self-join on the table.

SELECT 
  t2.id 
FROM Table t1
INNER JOIN Table t2
  ON t1.localCode = t2.localCode
  AND t1.localNumber = t2.localNumber
WHERE t1.status = 'val_1'
  AND t2.status = 'val_2'

This query will find all IDs of records that have status val_2 and where a corresponding entry with status val_1 exists.