I am trying to use left join in subquery in SQL Server. My query looks fine to me but it gives syntax error.
This is my query:
( SELECT FK_OrderNo AS LHNo, VendorName AS LHVendor FROM tbl_ShipmentAPAR LEFT JOIN tbl_vendors ON FK_VendorID = VendorID WHERE FK_ServiceID = 'LH' ) LHBase ON PK_OrderNo = LHNo LEFT JOIN (SELECT FK_OrderNo AS DANo, VendorName AS DAVendor FROM tbl_ShipmentAPAR LEFT JOIN tbl_vendors ON FK_VendorId = VendorId WHERE FK_ServiceId = 'DA') DABase ON PK_OrderNo = DANo
This is the error I’m getting:
This is my table structure:
CREATE TABLE tbl_ShipmentAPAR ( VendorID int PRIMARY KEY, VendorName varchar(200), FK_OrderNo int ) CREATE TABLE tbl_vendors ( FK_VendorID int, FOREIGN KEY (FK_VendorID) REFERENCES tbl_ShipmentAPAR(VendorID), FK_ServiceID varchar(200) ) INSERT INTO tbl_ShipmentAPAR VALUES (1, 'John',123) INSERT INTO tbl_vendors VALUES (1,'LH')
Advertisement
Answer
As @Chris mentioned, the query is bit incomplete. I guess you are trying to do something like this:
SELECT * FROM /*--> Added new */ ( SELECT FK_OrderNo AS LHNo, VendorName AS LHVendor FROM tbl_ShipmentAPAR LEFT JOIN tbl_vendors ON FK_VendorID = VendorID WHERE FK_ServiceID = 'LH' ) LHBase LEFT JOIN (SELECT FK_OrderNo AS DANo, VendorName AS DAVendor FROM tbl_ShipmentAPAR LEFT JOIN tbl_vendors ON FK_VendorId = VendorId WHERE FK_ServiceId = 'DA') DABase ON LHBase.LHNo = DABase.DANo /* -->Modified PKOrder no to LHNo because PKOrder no doesn't exist in either of the sub-queries */
This query worked for me. Comment to this answer if something must be changed.