Search code examples
sqlsybasesap-ase

Double Join to get data from a table based on other two tables

I have three tables with the following key fields: 

CONTRACTS
    reference
    package

EVENTS
    reference
    condition1
    condition2

TRADES
    reference
    event_reference

Basically, what I would like to do is the following:

  1. Get all the reference of the table EVENTS where the two conditions (condition1 and condition2) are met;
  2. Hence, getting all the reference of the table TRADES where TRADES.event_reference = EVENTS.reference
  3. Finally, getting the CONTRACTS.package where the CONTRACTS.reference = TRADES.reference (after having filtered the data at the point 2).

In order to do this, I have tried a JOIN statement:

SELECT CONTRACTS.package
FROM CONTRACTS
JOIN TRADES ON CONTRACTS.reference = TRADES.reference
JOIN EVENTS ON TRADES.event_reference = EVENTS.reference
WHERE EVENTS.condition1 = '1.511' AND EVENTS.condition2 IN (1,2)

However, the above (which is executed without errors) does not issue any result, and I would actually expect to see some. I hence understand that I'm being wrong in the logic that I follow: could anyone please help?

EDIT: this is an example of how the data look like (in yellow, I have highlighted the data that would be touched in the query if it was working as I had it in mind:

enter image description here

...here is the expected result: 

1 (package of 4, related to 11 which satisfies condition 1 and 2)
2 (package of 6, related to 13 which satisfies condition 1 and 2)
4 (package of 10, related to 16 which satisfies condition 1 and 2)

and here are the data to copy-paste them:

CONTRACTS   
reference   package
1   1
2   1
3   1
4   1
5   2
6   2
7   3
8   3
9   4
10  4

EVENTS      
reference   condition1  condition2
10  1.511   0
11  1.511   1
12  1.202   0
13  1.511   2
14  1.511   0
15  1.202   0
16  1.511   1

TRADES  
reference   event_reference
2   10
4   11
5   12
6   13
7   14
9   15
10  16
Solution

  • Your query looks OK

    SQL Fiddle Demo

    SELECT CONTRACTS.package
FROM CONTRACTS
JOIN TRADES ON CONTRACTS.reference = TRADES.reference
JOIN EVENTS ON TRADES.event_reference = EVENTS.reference
WHERE EVENTS.condition1 = 'true' AND EVENTS.condition2 = 'true'

    OUTPUT

    | package |
|---------|
|       1 |
|       2 |
|       4 |