Join Query for more than 2 tables giving wrong result

Last Reply on Jun 04, 2014 03:07 AM By Azim

Posted on Jun 03, 2014 11:27 PM

I have 5 tables in MySQL Database. In all the 5 tables, StationId column is common.

I had to use Join query to show necessary columns from 5 table.

I had used below query for it:

SELECT ST.StationId, ST.StationName, ST. SluiceW_Level , SGS.GateStatus, DT.WaterLevel, DT.DamLevel, PT.WaterLevel, PT.PumpLevel, FT.WaterLevel, FT.FlowLevel
FROM StationsTable AS ST INNER JOIN SluiceGateStatus AS SGS 
ON St.StationId=SGS.StationId INNER JOIN DamTable AS DT 
ON St.StationId=DT.StationId INNER JOIN PumpTable AS PT 
ON St.StationId=PT.StationId INNER JOIN FlowTable AS FT

But it is not working.

Database tables are as below:

SluiceGateStatus:

Id

StationId

GateStatus

1

St-01

open

2

St-02

close

3

St-10

open

DamTable:

Id

StationId

WaterLevel

DamLevel

1

St-03

34.7

56

2

St-04

45

34

3

St-05

2

8.8

 PumpTable:

Id

StationId

WaterLevel

PumpLevel

1

St-06

34.87

56.4

2

St-07

5.75

5.89

FlowTable:

Id

Station Id

Water level

Flow Level

1

St-08

34.6

2.0

2

St-09

3.5

34.7

 StationsTable:

Id

StationId

StationType

StationName

Latitude

Longitude

State

SluiceW_Level

1

St-01

Sluice Station

Sluice1

5.342611485

100.243359805

abc

3.5

2

St-02

Sluice Station

Sluice2

5.3442571

100.235514312

Pqr

14.65

3

St-10

Sluice Station

Sluice3

5.350829484

100.222044448

sss

23.5

4

St-03

Dam Station

Dam1

5.969411

100.741886

xyz

10

5

st-04

Dam Station

Dam2

6.115028

100.855558

aaa

4

6

St-05

Dam Station

Dam3

5.902542

100.645672

fgh

5.7

7

St-06

Pump Station

Pump1

6.052622

100.760558

pqr

0

8

St-07

Pump Station

Pump2

5.99925

100.729814

bbb

-0.2

9

St-08

Flow Station

Flow1

5.638289

100.810708

ddd

-4

10

St-09

Flow Station

Flow2

5.532133

100.570675

ttt

-0.3

Please reply what mistake i am doing.

You are viewing reply posted by: Azim on Jun 04, 2014 03:07 AM.
Posted on Jun 04, 2014 03:07 AM Modified on on Jun 04, 2014 03:08 AM

You should have atleast one matching StationId in all the tables otherwise it will not give you output

Run this in SQL Analyzer it will give you a single row output.

DECLARE @SluiceGateStatus TABLE (Id INT,StationId VARCHAR(20), GateStatus VARCHAR(20))
INSERT INTO @SluiceGateStatus VALUES(1,'St-01','open')
INSERT INTO @SluiceGateStatus VALUES(2,'St-02','close')
INSERT INTO @SluiceGateStatus VALUES(3,'St-10','open')
-------------------------------------------------------
DECLARE @DamTable TABLE (Id INT,StationId VARCHAR(20),WaterLevel DECIMAL,DamLevel DECIMAL)
INSERT INTO @DamTable VALUES(1,'St-01',34.7,56)
INSERT INTO @DamTable VALUES(2,'St-04',45,34)
INSERT INTO @DamTable VALUES(3,'St-05',2,8.8)
-------------------------------------------------------
DECLARE @PumpTable TABLE (Id INT,StationId VARCHAR(20),WaterLevel DECIMAL,PumpLevel DECIMAL)
INSERT INTO @PumpTable VALUES(1,'St-01',34.86,56.4)
INSERT INTO @PumpTable VALUES(2,'St-07',5.75,5.89)
--------------------------------------------------------
DECLARE @FlowTable TABLE (Id INT,StationId VARCHAR(20),WaterLevel DECIMAL,FlowLevel DECIMAL)
INSERT INTO @FlowTable VALUES(1,'St-08',34.86,2.0)
INSERT INTO @FlowTable VALUES(2,'St-01',3.5,34.7)
---------------------------------------------------------
DECLARE @StationsTable TABLE(Id INT,StationId VARCHAR(20),StationType VARCHAR(20),StationName VARCHAR(20),Latitude VARCHAR(50),Longitude VARCHAR(50),[State] VARCHAR(20),SluiceW_Level DECIMAL)

INSERT INTO @StationsTable VALUES(1,'St-01','Sluice Station','Sluice1','5.342611485','100.243359805','abc',3.5)

INSERT INTO @StationsTable VALUES(2,'St-02','Sluice Station','Sluice2','5.3442571','100.235514312','Pqr',14.65)
-----------------------------
SELECT ST.StationId, ST.StationName, ST. SluiceW_Level , SGS.GateStatus, DT.WaterLevel, DT.DamLevel, PT.WaterLevel, PT.PumpLevel, FT.WaterLevel, FT.FlowLevel
FROM @StationsTable AS ST INNER JOIN @SluiceGateStatus AS SGS
ON St.StationId = SGS.StationId INNER JOIN @DamTable AS DT
ON St.StationId = DT.StationId INNER JOIN @PumpTable AS PT
ON St.StationId = PT.StationId INNER JOIN @FlowTable AS FT
ON St.StationId = FT.StationId