Sub Query Woes

[pengu]

Hello.

 

I'll start with the table structures.

 

Rez_Desc - Table

 

Rez_ID

Rez_Number

Client_Name

Arriving_ID

Pickup_ID

1001

201000123

Mr. Pengu

1

2

1002

201000124

Mr. Cool

2

1

 

Arrival_Desc - Table

 

Arriving_ID

Label

1

AUS

2

USA

 

OffSite_Desc - Table

 

OffSite_ID

Label

1

AUS

2

USA

 

Here is my query (simplified), where I am receiving the error.

 

SELECT Rez_Desc.Rez_Number AS RezNum,

(SELECT OD.Label FROM Offsite_Desc OD
LEFT JOIN Rez_Desc AS RD ON OD.Offsite_ID = RD.Pickup_ID
AND RD.Rez_ID=Rez_Desc.Rez_ID) AS Pickup,

(SELECT AR.Label FROM Arrival_Desc AR
LEFT JOIN Rez_Desc AS RD ON AR.Arriving_ID = RD.Arriving_ID
AND RD.Rez_ID=Rez_Desc.Rez_ID) AS DropOff

FROM Rez_Desc

 

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

 

And finally this is the error message I am receiving.  To my knowledge and understanding it is being caused because the sub query is returning more than 1 result which it should not be doing.  Seeing as how in my example table above I have 2 entries, both with relevant ID's etc.

 

Need some help!

[pengu]

*bump* I guess

 

The way I want it too read is.

 

RezNum

PickUp

DropOff

201000123

USA

AUS

201000124

AUS

USA

[pengu]

Got this solved on Microsoft Forums.  But also noticed I did my query wrong.

 

SELECT Rez_Desc.Rez_Number AS RezNum,

(SELECT OD.Label FROM Offsite_Desc OD
LEFT JOIN Rez_Desc AS RD ON OD.Offsite_ID = RD.Pickup_ID
AND RD.Rez_ID=Rez_Desc.Rez_ID) AS Pickup,

(SELECT AR.Label FROM Arrival_Desc AR
LEFT JOIN Rez_Desc AS RD ON AR.Arriving_ID = RD.Arriving_ID
AND RD.Rez_ID=Rez_Desc.Rez_ID) AS DropOff

FROM Rez_Desc

 

In the sub queries I forgot to put "WHERE". I did a join then just said "AND" lol my bad.