How To Join Columns From Two Sqls Having Differnt Scenarios

[s4surbhi2218]

Hi,

i have two different scenarios for both of them i have framed different sqls now i want to show the columns from both of them together as :

if first sql outputs col A,col B, col C

and second sql outputs col D, col E

in the final output i want to show col A, col B,col C, col D, col E

(i do not need union here) following are the sqls

sql 1

SELECT COD.OrderID,COD.ItemCode AS Item,COD.MaxValue AS Line ,COD.CreatedOn AS DATE,COD.Quantity,

IF(COD.Price IS NULL OR COD.Price = '','0.00',COD.Price) AS Total,

SC.SourceCode AS SourceCode,

RR.Description AS CancellationReason

,CONCAT(CBI.FirstName,' ',CBI.LastName) AS CustomerName

FROM tblCancelOrderDetails COD LEFT JOIN tblOrderMaster OM ON COD.`OrderID` = OM.`OrderID`

LEFT JOIN tblSourceCode SC ON OM.`SourceCode` = SC.`SourceCodeID`

LEFT JOIN tblReturnReason RR ON COD.Reason = RR.`ReturnCode`

LEFT JOIN tblCustomerBasicInfo CBI ON OM.CustomerID = CBI.CustomerID ;

sql 2

SELECT COUNT(DISTINCT(OrderID)) AS NumberofAutodeliveryOrders ,CustomerID ,RecurrenceMasterID FROM tblOrderDetail

WHERE RecurrenceMasterID != '' AND RecurrenceMasterID IS NOT NULL AND RecurrenceMasterID != '-1'

GROUP BY CustomerID;

 

any suggestions would be helpful,thanks

[Barand]

Make the second query a "table subquery" in the first query and join on CustomerID.

 

eg

 

SELECT a,b,c,d

FROM table1

INNER JOIN (

SELECT c,d FROM anothertable

) as someAliasName ON table1.c = someAliasName.c

Edited November 23, 2012 by Barand

[s4surbhi2218]

i tried this

SELECT COD.OrderID,COD.ItemCode AS Item,COD.MaxValue AS Line ,COD.CreatedOn AS DATE,COD.Quantity,

IF(COD.Price IS NULL OR COD.Price = '','0.00',COD.Price) AS Total,

SC.SourceCode AS SourceCode,

RR.Description AS CancellationReason

,CONCAT(CBI.FirstName,' ',CBI.LastName) AS CustomerName,

COUNT(DISTINCT(OrderID)) AS NumberofAutodeliveryOrders,RecurrenceMasterID

FROM tblCancelOrderDetails COD LEFT JOIN tblOrderMaster OM ON COD.`OrderID` = OM.`OrderID`

LEFT JOIN tblSourceCode SC ON OM.`SourceCode` = SC.`SourceCodeID`

LEFT JOIN tblReturnReason RR ON COD.Reason = RR.`ReturnCode`

LEFT JOIN tblCustomerBasicInfo CBI ON OM.CustomerID = CBI.CustomerID

 

INNER JOIN (SELECT COUNT(DISTINCT(OrderID)) AS NumberofAutodeliveryOrders ,CustomerID AS cust ,RecurrenceMasterID FROM tblOrderDetail

WHERE RecurrenceMasterID != '' AND RecurrenceMasterID IS NOT NULL AND RecurrenceMasterID != '-1'

GROUP BY CustomerID) AS OD ON OM.`CompanyID` = OD.CustomerID ;

 

but getting the following error

Error Code: 1052

Column 'OrderID' in field list is ambiguous

 

and i also want this orderid .

[Muddy_Funster]

that's probably comming from here :

COUNT(DISTINCT(OrderID)) AS NumberofAutodeliveryOrders,RecurrenceMasterID
FROM tblCancelOrderDetails COD LEFT JOIN tblOrderMaster OM ON COD.`OrderID` = OM.`OrderID`

 

You are joining two tables that both have the OrderID and your not making it clear which one you are using.

 

Also, please please please use code tags!