1
select distinct 
   page0.MatterType, 
   page0.Name, 
   page0.MatterNo, 
   page0.security, 
   page0.serial,      
   page6.TribCaseNo, 
   contact0.Name as Cname
from 
   page0, page6, page14
left join 
   contact0 on page0.PrimaryContact = contact0.linkserial
where 
  page0.serial = page6.CaseSerial 
AND page0.serial = page14.CaseSerial 
AND (page14.staffmember = '100001^24' and page14.status != 'Inactive') 
AND page0.status != 'Closed'

I keep getting an error that

the multi-part identifier page0.PrimaryContact could not be bound.

I've checked the syntax and the spelling and both seem to be correct.

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
badD0g01
  • 37
  • 1
  • 3
  • 9
  • Is there actually a column `page0.PrimaryContact`? I guess I'd verify that before the spelling. – Michael Berkowski Feb 27 '12 at 17:16
  • Is there a PrimaryContact column on page0? – zmbq Feb 27 '12 at 17:16
  • Sorry, I should have added that I double-checked this column does exist. – badD0g01 Feb 27 '12 at 17:18
  • 3
    A note on your JOIN syntax. You're using a combination of implicit and explicit syntax which could get you into trouble. You should use explicit JOIN syntax only. See this post for why: http://stackoverflow.com/questions/3731952 – Paul Sasik Feb 27 '12 at 17:19
  • 1
    does the PrimaryContact columns exist on the the page0 table? – Diego Feb 27 '12 at 17:15
  • cant you remove the join and do from page0, page6, page14, contact0 and add "AND page0.PrimaryContact = contact0.linkserial" to your where clause? – Diego Feb 27 '12 at 17:21

2 Answers2

6

You'll need to make explicit joins. Currently you're using the old join syntax. Consider refactoring:

select P0.*, P6.TribCaseNo, C0.Name as Cname
FROM        page0 AS P0
INNER JOIN  page6 AS P6 on P0.serial = P6.CaseSerial 
INNER JOIN  page14 AS P14 ON P0.serial = P14.CaseSerial
LEFT JOIN   contact0 AS C0 on P0.PrimaryContact = C0.linkserial

WHERE (P14.staffmember = '100001^24' AND P14.status != 'Inactive') 
AND   P0.status != 'Closed'
p.campbell
  • 98,673
  • 67
  • 256
  • 322
  • When I do that, I get more "the multi-part identifier could not be bound" for fields page0.name, page0.matterno, page0.security, page0.serial, page6.tribcaseno, and contact0.name. – badD0g01 Feb 27 '12 at 17:26
  • @Terri Then your tables/views don't actually contain those columns. Edit your question to contain the CREATE scripts for all these tables, or perhaps a screenshot of those tables and all their column names. – p.campbell Feb 27 '12 at 17:28
  • SQL Server is telling you that they DON'T, as written. Perhaps change to this updated SELECT list. Edited my answer. – p.campbell Feb 27 '12 at 17:30
  • Is there some way to add a screen shot? Any idea why SQL Server would think they don't exist? – badD0g01 Feb 27 '12 at 17:30
  • Page0 CREATE TABLE [dbo].[PAGE0]( [SERIAL] [int] NOT NULL, [TYPELAW] [int] NULL, [SECURITY] [int] NULL, [Status] [varchar](10) NULL, [LINKSERIAL] [varchar](21) NULL, [division] [varchar](40) NULL, [MatterType] [varchar](40) NULL, [Name] [varchar](500) NULL, [DateClosed] [datetime] NULL, [MatterNo] [varchar](16) NULL, [PrimaryContact] [varchar](21) NULL, [DEFLD] [datetime] NULL, [abfld] [varchar](40) NULL, [lafld] [datetime] NULL, [OpenBy] [varchar](20) NULL, [TEXT2] [varchar](20) NULL, CONSTRAINT [PK__PAGE0__6B24EA82] PRIMARY KEY CLUSTERED – badD0g01 Feb 27 '12 at 17:35
  • Page 6 CREATE TABLE [dbo].[PAGE6]( [Serial] [int] NOT NULL, [CaseSerial] [int] NOT NULL, [LinkSerial] [varchar](21) NULL, [TribType] [varchar](40) NULL, [Trib] [varchar](40) NULL, [TribVenue] [varchar](40) NULL, [TribCaseNo] [varchar](40) NULL, [Judge] [varchar](40) NULL, [ShortCaption] [varchar](500) NULL, [Plaintiff] [text] NULL, [CurrentVenue] [varchar](1) NULL, [Trib_] AS ([Trib]), [NoShow] [varchar](40) NULL, [division] [varchar](40) NULL, [FilingDate] [datetime] NULL, [defendants] [varchar](500) NULL, CONSTRAINT [PK__PAGE6__1A00E0922BABE5F0] PRIMARY KEY CLUSTERED – badD0g01 Feb 27 '12 at 17:36
  • Sorry, I'm not allowed to add images since I am a new user. (spam prevention) – badD0g01 Feb 27 '12 at 17:43
  • @TerriDeweyTindle you can edit your question and post your create table scripts. – Taryn Feb 27 '12 at 17:45
1

You can make your query work by changing the order of tables in FROM :

select distinct   
page0.MatterType,   
page0.Name,   
page0.MatterNo,   
page0.security,   
page0.serial,        
page6.TribCaseNo,   
contact0.Name as Cname  
from   
 page6, page14, page0 --NOTE: page0 is now  closest to join!!!
left join   
contact0 on page0.PrimaryContact = contact0.linkserial  
where   
page0.serial = page6.CaseSerial   
AND page0.serial = page14.CaseSerial   
AND (page14.staffmember = '100001^24' and page14.status != 'Inactive')   
AND page0.status != 'Closed'

Update.
In general, I strongly encourage you not to mix old and new syntax (as it's done here),p.campbell's solution is the right way to write queries.

a1ex07
  • 36,826
  • 12
  • 90
  • 103