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!!
arthrexAsked:
Who is Participating?
 
SQL_SERVER_DBAConnect With a Mentor Commented:
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]Connect With a Mentor 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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
 
jindalankushConnect With a Mentor Commented:
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 MatthewsConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.