Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jason Carlton Aguilar
Starting Member
5 Posts |
 Posted - 2012-02-15 : 15:59:36
|
| Could someone tell me what I am doing wrong.I put my statement on the bottom..I am supposed to be using a self-join stmt with these guidlines:"Write a SELECT statement (that includes a self join) that returns 3 columns: VendorID from the Vendors table, VendorName from the Vendors table, Name from a concatenation of VendorContactFname and VendorContactLName with a space in between.The result set should have one row for each vendor whose contact has the same first name as another vendor's contact. Sort the final result by AccountNo.---------------------------------------------------------------------SELECT DISTINCT Vendors1.VendorName, Vendors1.VendorID, (Vendors1.VendorContactFName + ' ' + VendorContactLName) = NameFROM Vendors AS Vendors1 JOIN Vendors AS Vendors2 ON (Vendors1.VendorID = Vendors2.VendorID) AND (Vendors1.VendorName = Vendors2.VendorName) AND (Vendors1.VendorContactFName = Vendors2.VendorContactFName) AND (Vendors1.VendorContactLName = Vendors2.VendorContactLName)ORDER BY VendorNameJason Aguilar |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-15 : 16:09:44
|
| The join is what's wrong. (Vendors1.VendorID = Vendors2.VendorID) means that you are joining a particular vendor to itself (the row from Vendor1 will be exactly the same as the row from Vendor2), which from the description is not what you want.From the description of the problem, the join should be the VendorName from one table to the ContactName from another.Homework?--Gail ShawSQL Server MVP |
 |
|
|
Jason Carlton Aguilar
Starting Member
5 Posts |
Posted - 2012-02-15 : 21:07:34
|
| So I need to change line 4? I am very new to this so please overlook my ignorance. Is it my ON statements? Is that where I am messing up?Thank you so much...Jason Aguilar |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-16 : 04:00:54
|
| No, you need to change a lot more than Line 4. Your joins (all of them) are wrong for what you want.What column in the first table should match to what column in the second for the problem you have? The answer to that is what you're joining the table on.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Self Join Statement