CODEWITHSUNDEEP
sql
owleeee
owleeee

Reputation: 11

SQL query multiple tables to find a corresponding/related value

I have 4 tables that all store different info about a transaction

TABLE 1 has 1 column of interest - 'profileID' (unique ID for this transaction)

TABLE 2 has the same 'profileID' and a 'typeID' (unique ID for this transaction type)

TABLE 3 has the same 'typeID' and a 'timestamp' + 'referenceID'

TABLE 4 has 3 columns, 'timestamp'&'referenceID' from TABLE3 and a 'serialNum' column

Given the 'profileID' from TABLE1 I need to retrieve the 'serialNum' from TABLE4 (for use in a stored proc) without any direct route there, so the chain looks something like:

TABLE1<-'profileID'->TABLE2<-'typeID'->TABLE3<-'timestamp','reference'->TABLE4<-'serialNum'

How would i get from TABLE1 to TABLE4 via the profileID, TypeID, Timestamp/reference, to SELECT the serialNum column?

Upvotes: 1

Views: 560

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

I don't know what database engine you're using, which may affect things such as how you deal with mixed-case identifiers and the like, but in general you'd do something like

SELECT t4.serialNum
  FROM TABLE1 t1
  INNER JOIN TABLE2 t2
    ON t2.profileID= t1.profileID
  INNER JOIN TABLE3 t3
    ON t3.typeID = t2.typeID
  INNER JOIN TABLE4 t4
    ON t4.timestamp = t3.timestamp AND
       t4.reference = t3.reference
  WHERE t1.profileID = <something>

EDIT

And given that you know you've got a profileID value you don't really need to go after TABLE1, and can abbreviate the query to:

SELECT t4.serialNum
  FROM TABLE2 t2
  INNER JOIN TABLE3 t3
    ON t3.typeID = t2.typeID
  INNER JOIN TABLE4 t4
    ON t4.timestamp = t3.timestamp AND
       t4.reference = t3.reference
  WHERE t2.profileID = <something>

Upvotes: 3

Related Questions