Join a 3rd table, in which multiple records with the same id exist, but I just need 1 of them
-
So I have 3 Tables OrderInfo - Unique records ShipRates - Unique records OrderInfo_Emails - A list of non-unique records There are 3 records in orderinfo, but I get 4 results back, because orderinfo_email has 2 records with the same orderinfo id. So I get that, why I get 4 back and not 3 Should I just drop the OrderInfo_Email Join and do something else, some other way, or is there a way to limit the results from the join?
SELECT
oi.OrderInfoID
, oi.OrderDate
, oi.OrderNum
, oi.LoginID
, oi.OrderStatus
, oi.AuthorizationID
, oi.ApprovalCode
, oi.Shipping
, oi.PaymentInfo
, oi.SubTotal
, oi.GrandTotal
, oi.RateAPICode
, oi.TotalNetCharge
, srr.RateName
, oie.MessageID
, oie.DateSent
, oie.SentBy
FROM OrderInfo oi
LEFT JOIN ORDERINFO_EMAIL oie
ON (oie.OrderID = oi.OrderInfoID )
LEFT JOIN Shipping_RealTime_Rates srr
ON (srr.RateAPICode = oi.RateAPICode)
ORDER BY oi.OrderDate DESC -
So I have 3 Tables OrderInfo - Unique records ShipRates - Unique records OrderInfo_Emails - A list of non-unique records There are 3 records in orderinfo, but I get 4 results back, because orderinfo_email has 2 records with the same orderinfo id. So I get that, why I get 4 back and not 3 Should I just drop the OrderInfo_Email Join and do something else, some other way, or is there a way to limit the results from the join?
SELECT
oi.OrderInfoID
, oi.OrderDate
, oi.OrderNum
, oi.LoginID
, oi.OrderStatus
, oi.AuthorizationID
, oi.ApprovalCode
, oi.Shipping
, oi.PaymentInfo
, oi.SubTotal
, oi.GrandTotal
, oi.RateAPICode
, oi.TotalNetCharge
, srr.RateName
, oie.MessageID
, oie.DateSent
, oie.SentBy
FROM OrderInfo oi
LEFT JOIN ORDERINFO_EMAIL oie
ON (oie.OrderID = oi.OrderInfoID )
LEFT JOIN Shipping_RealTime_Rates srr
ON (srr.RateAPICode = oi.RateAPICode)
ORDER BY oi.OrderDate DESCHow do you pick the one you want? What the criteria?
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
-
How do you pick the one you want? What the criteria?
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
Oh, the first one is fine, It's for Abandoned Orders, in which a email is sent to the customer as a reminder. You can send multiple emails to the customer, in which the emails are logged in ORDERINFO_EMAIL, So When you load the Abandoned Orders, if a email record record exist, a little envelope icon appears.
-
Oh, the first one is fine, It's for Abandoned Orders, in which a email is sent to the customer as a reminder. You can send multiple emails to the customer, in which the emails are logged in ORDERINFO_EMAIL, So When you load the Abandoned Orders, if a email record record exist, a little envelope icon appears.
You may try this:
SELECT
oi.OrderInfoID
, oi.OrderDate
, oi.OrderNum
, oi.LoginID
, oi.OrderStatus
, oi.AuthorizationID
, oi.ApprovalCode
, oi.Shipping
, oi.PaymentInfo
, oi.SubTotal
, oi.GrandTotal
, oi.RateAPICode
, oi.TotalNetCharge
, srr.RateName
, (select count(*) from ORDERINFO_EMAIL oie where oie.OrderID = oi.OrderInfoID) AS email_sent
FROM OrderInfo oi
LEFT JOIN Shipping_RealTime_Rates srr
ON (srr.RateAPICode = oi.RateAPICode)
ORDER BY oi.OrderDate DESCI'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
-
You may try this:
SELECT
oi.OrderInfoID
, oi.OrderDate
, oi.OrderNum
, oi.LoginID
, oi.OrderStatus
, oi.AuthorizationID
, oi.ApprovalCode
, oi.Shipping
, oi.PaymentInfo
, oi.SubTotal
, oi.GrandTotal
, oi.RateAPICode
, oi.TotalNetCharge
, srr.RateName
, (select count(*) from ORDERINFO_EMAIL oie where oie.OrderID = oi.OrderInfoID) AS email_sent
FROM OrderInfo oi
LEFT JOIN Shipping_RealTime_Rates srr
ON (srr.RateAPICode = oi.RateAPICode)
ORDER BY oi.OrderDate DESCI'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
