SQL Join from the same table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Join from the same table

Author

Topic

aaronbrett
Starting Member

1 Post

Posted - 2012-05-22 : 10:25:51

I am trying to join one table with another and then join that table with more fields from the second table from the first query:

SELECT U1.*, C1.*
FROM
(SELECT U.PolNo, U.RTMPolNo, U.PolStatus, U.Referral, U.Inception, U.Expiry, U.Cancellation, U.Name, U.Address1, U.Address2, U.Address3, U.PostCode, U.BrokerNo, U.Cover, U.PermDrivCode, U.VehUse, U.ABI, U.VehVal, U.Registration, U.NCDYr, U.NCD, U.PurchaseDate, C.PolNo, C.RTMPolNo, C.DriverNo, C.LicenceType, C.LicenceDate, C.Residency, C.Claim1, C.Claim2, C.Claim3, C.Claim4, C.Claim5, C.Claim6
FROM UWData U LEFT JOIN ClaimData C
ON U.RTMPolNo = C.RTMPolNo
WHERE C.DriverNo = '1') U1
LEFT JOIN
ClaimData C1
ON U1.RTMPolNo= C1.RTMPolNo
WHERE C1.DriverNo = '2'

This is the error:

The column 'PolNo' was specified multiple times for 'U1'.

Can someone help please?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-22 : 10:35:02

[code]
SELECT U.PolNo, U.RTMPolNo, U.PolStatus, U.Referral, U.Inception, U.Expiry, U.Cancellation,
U.Name, U.Address1, U.Address2, U.Address3, U.PostCode, U.BrokerNo, U.Cover, U.PermDrivCode, U.VehUse,
U.ABI, U.VehVal, U.Registration, U.NCDYr, U.NCD, U.PurchaseDate, ~~C.PolNo~~, C.RTMPolNo,
C.DriverNo, C.LicenceType, C.LicenceDate, C.Residency, C.Claim1, C.Claim2, C.Claim3, C.Claim4, C.Claim5, C.Claim6[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources