SQL multiple joins and replacing values from distinct queries-CodePudding

I'll preface this by saying I don't get involved in SQL at all really. We have an internal piece of hardware that stores tests in a Firebird SQL database. The organisation is somewhat confusing, but I've just about got it figured.

I'm assuming the best way to access the information I require is using joins, however, I have a feeling that it might be slightly complicated.

Results Table:

| testID | runID | resultID |
|--------|-------|----------|
| 1001   | 1     | 3        |
| 1002   | 7     | 4        |
| 1003   | 55    | 9        |

Records Table:

| testID | runID | resultID | recordedValue | startConnectionIndex | startPointID | endConnectionIndex | endPointID |
|--------|-------|----------|---------------|----------------------|--------------|--------------------|------------|
| 1001   | 1     | 3        | value1        | 0                    | p-5          | 1                  | p-1        |
| 1001   | 1     | 3        | value2        | 1                    | p-6          | 1                  | p-2        |

Connection Table:

| testID | connectionIndex | connectionID |
|--------|-----------------|--------------|
| 1001   | 0               | con-0        |
| 1001   | 1               | con-1        |

Point Table:

| testID | pointID | connectionID | pointName |
|--------|---------|--------------|-----------|
| 1001   | p-1     | con-1        | Point A   |
| 1001   | p-2     | con-1        | Point B   |
| 1001   | p-5     | con-0        | Point C   |
| 1001   | p-6     | con-1        | Point D   |

I want to get the records for a particular testID which I seemed to have worked out with:

SELECT *
FROM RESULTS r
JOIN RECORDS re on ((r.testID  = re.testID) 
AND (r.runID = re.runID)
AND (r.resultID = re.resultID))

At the same time, I would like to "replace" the startPointID and the endPointID with their associated pointName. To do so, I need to look up the startConnectionIndex/endConnectionIndex on the Connection table where the testID is equal to r.testID. This gives me connectionID which I then need to use, alongside r.startPointID/r.endPointID as well as r.restID (again) to find the pointName from the Point table.

So the final data should be similar to:

| testID | runID | resultID | recordedValue | ... | endPointID | endPointName |
|--------|-------|----------|---------------|-----|------------|--------------|
| 1001   | 1     | 3        | value1        | ... | p-1        | Point A      |
| 1001   | 1     | 3        | value2        | ... | p-2        | Point B      |

CodePudding user response：

You need to join with Connection and Point twice, once for startPointID and the second for endPointID.

select r.*, re.*, p1.pointName AS startPointName, p2.pointName AS endPointName
FROM RESULTS r
JOIN RECORDS re on ((r.testID  = re.testID) 
    AND (r.runID = re.runID)
    AND (r.resultID = re.resultID))
JOIN Connection AS c1 ON c1.testID = r.testID AND c1.connectionIndex = re.startConnectionIndex
JOIN Point AS p1 ON p1.connectionID = c1.connectionID AND p1.pointID = re.startPointID AND p1.testID = r.testID
JOIN Connection AS c2 ON c2.testID = r.testID AND c2.connectionIndex = re.endConnectionIndex
JOIN Point AS p2 ON p2.connectionID = c2.connectionID AND p2.pointID = re.endPointID AND p2.testID = r.testID

CodePudding user response：

I am not so good at SQL either but maybe this could work at least to replicate the table that you have put in the question:

SELECT rec.testID,rec.runID,rec.resultID, rec.recordedValue, rec.endPointID, p.pointName

FROM RECORDS_TABLE AS rec

JOIN Point_table AS p

ON rec.testID = p.testID

WHERE rec.endPointID = p.pointID