[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Need the correct Syntax Thanks!

Hi Experts,

I need a correct syntax for the statement above.
I know this is completely wrong.
But I think you'll understand the sense, so that you can help me to put it in a correct staement.

SELECT    History.Date, History.Action, Customer.Name, Customer.Number, Vendor.Name, Vendor.Number
FROM         History where (History.Membercode = Customer.Code) or (History.Membercode = Vendor.Code)
 or History.Membercode =0

Thanks a lot!!
0
arthrex
Asked:
arthrex
  • 3
  • 2
  • 2
  • +2
4 Solutions
 
SQL_SERVER_DBACommented:
SELECT    
History.Date,
History.Action,
Customer.Name,
Customer.Number,
Vendor.Name,
Vendor.Number
FROM INNER JOIN HISTORY ON History.Membercode = Vendor.Code
INNER JOIN CUSTOMER ON History.Membercode = Customer.Code
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:

SELECT    h.Date, h.Action, c.Name customer_name , c.Number customer_number, v.Name vendor_name, v.Number vendor_number
FROM         History h
LEFT JOIN Customer c
  ON h.Membercode = c.Code
LEFT JOIN Vendor v
  ON h.Membercode = v.Code

0
 
Patrick MatthewsCommented:
SELECT h.[Date], h.Action, 'Customer' AS Source, c.[Name], c.Number
FROM History h INNER JOIN
    Customer c ON h.Membercode = c.Code
UNION ALL
SELECT h.[Date], h.Action, 'Vendor' AS Source, v.[Name], v.Number
FROM History h INNER JOIN
    Vendor v ON h.Membercode = v.Code
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
SQL_SERVER_DBACommented:
sorry, I missed your tablename

SELECT    
History.Date,
History.Action,
Customer.Name,
Customer.Number,
Vendor.Name,
Vendor.Number
FROM HISTORY INNER JOIN VENDOR ON History.Membercode = Vendor.Code
INNER JOIN CUSTOMER ON History.Membercode = Customer.Code
0
 
jindalankushCommented:
SELECT    History.Date,
 History.Action,
Customer.Name,
Customer.Number,
 Vendor.Name,
Vendor.Number
FROM         History,

 where (History.Membercode = Customer.Code) or (History.Membercode = Vendor.Code)
 or History.Membercode =0
0
 
jindalankushCommented:
sorry .now check it
SELECT    History.Date,
       History.Action,
      Customer.Name,
      Customer.Number,
       Vendor.Name,
      Vendor.Number
FROM         History,
          Customer,
            Vendor
 where (History.Membercode = Customer.Code) or (History.Membercode = Vendor.Code)
 or History.Membercode =0
0
 
Patrick MatthewsCommented:
Sorry, I missed the Membercode = 0 condition.  Assuming that when Membercode = 0
there will be no matches on Customer or Vendor...



SELECT h.[Date], h.Action, 'Customer' AS Source, c.[Name], c.Number
FROM History h INNER JOIN
    Customer c ON h.Membercode = c.Code
UNION ALL
SELECT h.[Date], h.Action, 'Vendor' AS Source, v.[Name], v.Number
FROM History h INNER JOIN
    Vendor v ON h.Membercode = v.Code
UNION ALL
SELECT h.[Date], h.Action, NULL, NULL, NULL
FROM History h
WHERE h.Membercode = 0
0
 
arthrexAuthor Commented:
wow!! thanks to all of you!
0
 
Patrick MatthewsCommented:
arthrex,

You're welcome.  BTW, several different aproaches were presented here, each of which would
lead to different results, but based on the vagueness of the question, any of them could well
have been the exact one you were looking for.  Out of curiosity, which one was it?

Regards,

Patrick
0
 
arthrexAuthor Commented:
hi matthewspatrick,

this one was the one I was exactly  looking for.

SELECT    h.Date, h.Action, c.Name customer_name , c.Number customer_number, v.Name vendor_name, v.Number vendor_number
FROM         History h
LEFT JOIN Customer c
  ON h.Membercode = c.Code
LEFT JOIN Vendor v
  ON h.Membercode = v.Code

although is it ok to split the points?

thanks a lot for your help

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
glad I could help... I am fine with the split..
however, to be honest, I have to disagree with mathewspatrick about the ambiguity... for me it was not ambiguous at all what was "requested" :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now