CODEWITHSUNDEEP
sql
Hakan
Hakan

Reputation: 141

Join multiple tables

I have 5 tables and trying to Join into 1 but I am getting 4 records for each. How can i use correct JOIN statement.

SELECT  tbl_meter.[cdate] AS 'CDate', 
tbl_meter_backup.[machine_no], 
tbl_machines.[accounting_denomination] AS 'Denom', 
(tbl_meter.[turnover]- tbl_meter_backup.[turnover])* tbl_machines.[accounting_denomination]  AS 'Turnover',
(tbl_meter.[total win]- tbl_meter_backup.[total win])* tbl_machines.[accounting_denomination]  AS 'Total win', 
(tbl_meter.[games played]-tbl_meter_backup.[games played]) AS 'Games Played', (tbl_meter.[credit in]-tbl_meter_backup.[credit in]) * tbl_machines.[accounting_denomination] AS 'Credit IN', 
(tbl_meter.[Bill in]-tbl_meter_backup.[Bill in])* tbl_machines.[accounting_denomination]  AS 'Bill In',  
(tbl_meter.[cancel credit]-tbl_meter_backup.[cancel credit])* tbl_machines.[accounting_denomination]  AS 'Cancel Credit', 
tbl_open_backup.[amount] AS 'cgOpen', 
tbl_cancel_backup.[amount] AS 'cgCancel' 
FROM 
tbl_machines, 
tbl_meter,
tbl_meter_backup,
tbl_open_backup,
tbl_cancel_backup  

INNER JOIN tbl_meter  ON 
tbl_meter_backup.[Machine_No] = tbl_meter.[Machine_No] AND tbl_machines.[local_no]=tbl_meter.[machine_no] AND tbl_open_backup.[machine_no]=tbl_meter.[machine_no] AND  tbl_cancel_backup.[machine_no]=tbl_meter.[machine_no] 
WHERE tbl_meter_backup.[cDate] = @StartDate AND tbl_meter.[cDate] = @EndDate  AND tbl_open_backup.[cdate]=@enddate AND tbl_cancel_backup.[cdate]=@enddate;

Upvotes: 1

Views: 241

Answers (2)

HLGEM
HLGEM

Reputation: 96650

First fix your synatx to explicit joins that are easier to read and maintain, there is no excuse for ever using an implicit join, they are error prone and 18 years outdated and especially hard to maintain when you mix them like this. I expect that the system could have been confused on this as well as you didn't do the joins correctly. In an implicit join, the join criteria goes in the where clause not the on clause of the one explicit join you used. You also joined to the same table twice.

So with the cleaned up joins do you still have a data issue?

SELECT  tbl_meter.[cdate] AS 'CDate',  
tbl_meter_backup.[machine_no],  
tbl_machines.[accounting_denomination] AS 'Denom',  
(tbl_meter.[turnover]- tbl_meter_backup.[turnover])* tbl_machines.[accounting_denomination]  AS 'Turnover', 
(tbl_meter.[total win]- tbl_meter_backup.[total win])* tbl_machines.[accounting_denomination]  AS 'Total win',  
(tbl_meter.[games played]-tbl_meter_backup.[games played]) AS 'Games Played', (tbl_meter.[credit in]-tbl_meter_backup.[credit in]) * tbl_machines.[accounting_denomination] AS 'Credit IN',  
(tbl_meter.[Bill in]-tbl_meter_backup.[Bill in])* tbl_machines.[accounting_denomination]  AS 'Bill In',   
(tbl_meter.[cancel credit]-tbl_meter_backup.[cancel credit])* tbl_machines.[accounting_denomination]  AS 'Cancel Credit',  
tbl_open_backup.[amount] AS 'cgOpen',  
tbl_cancel_backup.[amount] AS 'cgCancel'  
FROM  
tbl_machines
INNER JOIN tbl_meter  ON  tbl_machines.[local_no]=tbl_meter.[machine_no] 
Inner join tbl_meter_backup ON tbl_meter_backup.[Machine_No] = tbl_meter.[Machine_No]
Inner join tbl_open_backup ON tbl_open_backup.[machine_no]=tbl_meter.[machine_no]
Inner join tbl_cancel_backup on tbl_cancel_backup.[machine_no]=tbl_meter.[machine_no]
WHERE tbl_meter_backup.[cDate] = @StartDate AND tbl_meter.[cDate] = @EndDate  AND tbl_open_backup.[cdate]=@enddate AND tbl_cancel_backup.[cdate]=@enddate;

If so, one or more of your tables has more that one record for the items in the join. This will then need special handling as you need to determine how to know which record you want.

Upvotes: 2

Matt Mitchell
Matt Mitchell

Reputation: 41871

First, there is no need to include all those tables in the FROM if you are using INNER JOIN.

Second, one of the following is true:

  1. There are multiple meters per machine for the given @EndDate
  2. There are multiple meter_backups per machine for the given @StartDate
  3. There are multiple open_backups per machine for the given @EndDate
  4. There are multiple cancel_backups per machine for the given @EndDate

See if any of the following rows show more than "1" for count:

  1. SELECT count(*), met.[Machine_No], met.[cDate] as EndDate FROM tbl_meter met GROUP BY met.[Machine_No], met.[cDate] ORDER BY count(*) DESC

  2. SELECT count(*), metB.[Machine_No], metB.[cDate] as StartDate FROM tbl_meter_backup metB GROUP BY metB.[Machine_No], metB.[cDate] ORDER BY count(*) DESC

  3. SELECT count(*), openB.[Machine_No], openB.[cDate] as EndDate FROM tbl_open_backup openB GROUP BY openB.[Machine_No], openB.[cDate] ORDER BY count(*) DESC

  4. SELECT count(*), canB.[Machine_No], canB.[cDate] as EndDate FROM tbl_cancel_backup canB GROUP BY canB.[Machine_No], canB.[cDate] ORDER BY count(*) DESC

Upvotes: 2

Related Questions