4 Replies - 834 Views - Last Post: 18 April 2009 - 10:47 AM Rate Topic: -----

#1 fran101  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 91
  • Joined: 01-December 08

Query help with Multiple joins

Posted 18 April 2009 - 10:03 AM

I'm trying to run a query across multiple tables but getting and error
my query is

use CleaningRosters

print '	  List of Cleaners Who Cleaned ABC1234  (By Date)'
print ''
select bs.RegNo 'Bus Reg', t.TypeDesc 'Bus Type' , d.DepotName 'Depot Where Cleaned', c.CleanerName 'Cleaned By', r.RosterDate 'Date was Cleaned'
from BusStatus bs inner join Roster r, Roster r inner join Depot d, Roster r inner join Cleaner c,Roster r inner join Bus b, Bus b inner join BusType t;
on bs.RosterNo = r.RosterNo, r.DepotNo = d.DepotNo, r.CleanerNo = c.CleanerNo, r.RegNo = b.RegNo, b.TypeNo = t.TypeNo
where bs.BusStatus = 1 and bs.RegNo = 'ABC1234'
order by r.RosterDate
go


The error i'm getting is
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ','.


Any help or pointers would be great

Is This A Good Question/Topic? 0
  • +

Replies To: Query help with Multiple joins

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Query help with Multiple joins

Posted 18 April 2009 - 10:21 AM

When you are assigning an alias to a column in your query you have to use the AS keyword

use CleaningRosters

print '	  List of Cleaners Who Cleaned ABC1234  (By Date)'
print ''
select bs.RegNo AS 'Bus Reg', t.TypeDesc AS  'Bus Type' , d.DepotName AS 'Depot Where Cleaned', c.CleanerName  AS 'Cleaned By', r.RosterDate AS 'Date was Cleaned'
from BusStatus bs inner join Roster r, Roster r inner join Depot d, Roster r inner join Cleaner c,Roster r inner join Bus b, Bus b inner join BusType t;
on bs.RosterNo = r.RosterNo, r.DepotNo = d.DepotNo, r.CleanerNo = c.CleanerNo, r.RegNo = b.RegNo, b.TypeNo = t.TypeNo
where bs.BusStatus = 1 and bs.RegNo = 'ABC1234'
order by r.RosterDate
go



Hope that helps :)
Was This Post Helpful? 0
  • +
  • -

#3 fran101  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 91
  • Joined: 01-December 08

Re: Query help with Multiple joins

Posted 18 April 2009 - 10:30 AM

View PostPsychoCoder, on 18 Apr, 2009 - 09:21 AM, said:

When you are assigning an alias to a column in your query you have to use the AS keyword

use CleaningRosters

print '	  List of Cleaners Who Cleaned ABC1234  (By Date)'
print ''
select bs.RegNo AS 'Bus Reg', t.TypeDesc AS  'Bus Type' , d.DepotName AS 'Depot Where Cleaned', c.CleanerName  AS 'Cleaned By', r.RosterDate AS 'Date was Cleaned'
from BusStatus bs inner join Roster r, Roster r inner join Depot d, Roster r inner join Cleaner c,Roster r inner join Bus b, Bus b inner join BusType t;
on bs.RosterNo = r.RosterNo, r.DepotNo = d.DepotNo, r.CleanerNo = c.CleanerNo, r.RegNo = b.RegNo, b.TypeNo = t.TypeNo
where bs.BusStatus = 1 and bs.RegNo = 'ABC1234'
order by r.RosterDate
go



Hope that helps :)


Even with that I am still getting the same error :crazy:
Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Query help with Multiple joins

Posted 18 April 2009 - 10:38 AM

Now looking again you're getting that error because your joins are all wrong. You cannot do it like you currently have it. Each inner join has to be completed with the ON value before introducing a new join. Try this

use CleaningRosters

print '	  List of Cleaners Who Cleaned ABC1234  (By Date)'
print ''
select 
	bs.RegNo AS 'Bus Reg', 
	t.TypeDesc AS  'Bus Type' , 
	d.DepotName AS 'Depot Where Cleaned', 
	c.CleanerName  AS 'Cleaned By', 
	r.RosterDate AS 'Date was Cleaned'
from 
	BusStatus bs inner join Roster r ON bs.RosterNo = r.RosterNo
	INNER JOIN Depot d ON  r.DepotNo = d.DepotNo
	INNER JOIN Cleaner c ON r.CleanerNo = c.CleanerNo
	INNER JOIN Bus b ON r.RegNo = b.RegNo
	INNER JOIN BusType t ON b.TypeNo = t.TypeNo
where 
	bs.BusStatus = 1 
	and bs.RegNo = 'ABC1234'
order by 
	r.RosterDate
go


Was This Post Helpful? 0
  • +
  • -

#5 fran101  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 91
  • Joined: 01-December 08

Re: Query help with Multiple joins

Posted 18 April 2009 - 10:47 AM

Thanks for your help, I've managed to solve as below

use CleaningRosters

print '      List of Cleaners Who Cleaned ABC1234  (By Date)'
print ''
select bs.RegNo AS 'Bus Reg', t.TypeDesc AS 'Bus Type' , d.DepotName AS 'Depot Where Cleaned', c.CleanerName  AS 'Cleaned By', r.RosterDate AS 'Date was Cleaned'
from BusStatus bs, Roster r, Depot d, Cleaner c, Bus b, BusType t
Where bs.RosterNo = r.RosterNo and r.DepotNo = d.DepotNo and r.CleanerNo = c.CleanerNo and bs.RegNo = b.RegNo and b.TypeNo = t.TypeNo and bs.BusStatus = 1 and bs.RegNo = 'ABC1234'
order by r.RosterDate
go


:D B)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1