I have a very strange database with a very strange problem.

Consider 4 tables:

Table1:
----------------
Table1ID INT PK
Table2ID INT FK
Table3ID INT FK
OrderNo VARCHAR(50)

Table2
----------------
Table2ID INT PK
Table4ID INT FK

Table3
----------------
Table3ID INT PK
Table2ID INT FK

Table4
----------------
Table4ID INT PK
OrderTotal VARCHAR(50)


With Data:

Table1:
------------
1 1 NULL 90001
2 2 NULL 90002
3 NULL 1 90003
4 NULL 2 90004

Table2:
------------
1 1
2 1

Table3:
------------
1 1
2 2

Table4:
------------
1 500
2 1000


Table1 can have either a Table2ID OR a Table3ID but not both.


This is the query I'm attempting:
---------------------------

SELECT dbo.Table1.OrderNo, dbo.Table4.OrderTotal
FROM dbo.Table1 LEFT OUTER JOIN
dbo.Table4 INNER JOIN
dbo.Table2 ON dbo.Table4.Table4ID =
dbo.Table2.Table4ID INNER JOIN
dbo.Table3 ON dbo.Table2.Table2ID =
dbo.Table3.Table3ID ON dbo.Table1.Table2ID = dbo.Table2.Table2ID AND
dbo.Table1.Table3ID = dbo.Table3.Table3ID


Which gives me:
---------------------------
90001 NULL
90002 NULL
90003 NULL
90004 NULL

When I really want:
----------------------------
90001 500
90002 500
90003 500
90003 1000 (NOT 500)


I don't know how to do this. Are any of you sql guru's up to the
challenge?

Thanks in advance

-Matt

p.s. sql to recreate tables included



CREATE TABLE [dbo].[Table1] (
[Table1ID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Table2ID] [int] NULL ,
[Table3ID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table2] (
[Table2ID] [int] IDENTITY (1, 1) NOT NULL ,
[Table4ID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table3] (
[Table3ID] [int] IDENTITY (1, 1) NOT NULL ,
[Table2ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table4] (
[Table4ID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderTotal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Table1ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[Table2ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table3] WITH NOCHECK ADD
CONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED
(
[Table3ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table4] WITH NOCHECK ADD
CONSTRAINT [PK_Table4] PRIMARY KEY CLUSTERED
(
[Table4ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table2] FOREIGN KEY
(
[Table2ID]
) REFERENCES [dbo].[Table2] (
[Table2ID]
),
CONSTRAINT [FK_Table1_Table3] FOREIGN KEY
(
[Table3ID]
) REFERENCES [dbo].[Table3] (
[Table3ID]
)
GO

ALTER TABLE [dbo].[Table3] ADD
CONSTRAINT [FK_Table3_Table2] FOREIGN KEY
(
[Table3ID]
) REFERENCES [dbo].[Table2] (
[Table2ID]
)
GO