Marco Marco - 1 year ago 61
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 Source

Make a self-join on the table.

FROM Table t1
  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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download