-1

i am building a fleet management system in php and mysql and i am having problems with my select query from my tables since i want the system to be producing daily, weekly and monthly reports, here are my tables..

create table vehicles(veh_id tinyint not null auto_increment, Reg_number varchar(10)not null, primary key(veh_id));  

create table transport(trans_id int not null auto_increment,    veh_id tinyint not null,    source varchar(10)not  null,    destination varchar(10)not null    del_date date not null,    load varchar(10)not null,    customer varchar(10),    primary key(trans_id)); 

create table expenses(exp_id)int not null auto_increment,    veh_id tinyint not null,    del_date date not null,    trans_id int not null,    cess varchar(10),    labour varchar(10),    allowances varchar(10),   misc varchar(10),    primary key(exp_id));

now when i run this query i am only getting results from two rows yet i have many rows containing values across my tables...here is my query,,

  SELECT   vehicles.Reg_number
           ,  transport.trans_id
           ,  transport.del_num
           ,  transport.veh_id
           ,  transport.destination
           ,  transport.source
           ,  transport.load
           ,  expenses.cess
           ,  expenses.labour
           ,  expenses.exp_id
           ,  expenses.allowances
           ,   expenses.fuel   
 from vehicles  
 inner join transport using(veh_id) 
 inner join expenses using(trans_id) 
 where  expenses.trans_id=transport.trans_id  and  
        vehicles.veh_id=transport.veh_id and 
        transport.del_date between '2016-04-27' and '2016-05-06';
Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
muriuki josh
  • 1
  • 1
  • 3

1 Answers1

0

You are probably using the wrong type of join here. Here is a pretty good representation of JOINS in SQL Visual Representation of Joins and here is a good stack overflow topic on it Stack Overflow Joins

Community
  • 1
  • 1
Kurt Leadley
  • 513
  • 3
  • 20