QUERYING columns from 3 tables using SQL

Question

I have 3 tables, TABLE1, TABLE2 and TABLE3.

TABLE1 have ID and TYPE as columns

TABLE2 have ID, DATE and VALUE as columns

TABLE3 have ID and DESC

Basically TABLE1 is just used as a mapping table. It just tells what type of thing a certain ID is with the TYPE column. We have 1-3 as TYPE.

TABLE2 contains transaction dates per ID. So it's possible to have multiple same ID in TABLE2 but with different DATE. VALUE is just how much the transaction was that DATE for that ID

TABLE3 is like TABLE1 but with a different column DESC.

So my question is how can I query all IDs that are TYPE = 1 (TABLE1) that happened on DATE = 5/27/2018 (TABLE2). The query would contain the columns ID, DATE, VALUE and DESC (TABLE3)

My approach in this one is to use JOIN

SELECT TABLE1.ID, TABLE2.DATE, TALBE2.VALUE
FROM TALBE2
INNER JOIN TABLE1 ON TABLE1.ID = TABLE2.ID
WHERE TABLE1.TYPE = 1 AND TABLE2.DATE = '5/27/2018';

My problem here is that it doesn't work and it doesn't include TABLE3.DESC yet.

MySQL <> SQL Server. Neither of these use PLSQL, and you have stated Oracle in the Title. I'd removed all your tags, and reference to a RDBMS in the title. Please correct the tags appropriately. — Larnu

Sjoerd Sjoerd · Accepted Answer · 2018-07-20T09:25:59

I hope you are not using the exact SQL statement as given. It has 2 TALBE-TABLE mix ups.

The query should be something like:

SELECT TABLE1.ID, TABLE2.DATE, TABLE2.VALUE, TABLE3.DESC
FROM TABLE2
LEFT JOIN TABLE1 ON TABLE2.ID = TABLE1.ID
LEFT JOIN TABLE3 ON TABLE2.ID = TABLE3.ID
WHERE TABLE1.TYPE = 1 AND TABLE2.DATE = '5/27/2018'

QUERYING columns from 3 tables using SQL

1 Answers