asked
3. Modify your list to include customers and suppliers. Address cards to the contact person.
I am supposed to modify this code: SELECT([FirstName] + ' ' + [LastName]) as 'Name', [Address] +' ' + [City] + ' '+ ISNULL(Region, ' ') + ' ' + [PostalCode] as 'Address1' FROM [dbo].[Employees] order by [FirstName] To include customers and suppliers and using the contactname instead of the first and last names. I Cannot figure out how to do this. I have tried a join but it is giving me ambiguous column names -.-
You will need to join to to the other tables in order to bring things together. I don't know what the other table names are, but what I would do is assign an alias to each table. e for Employees, c for Customers, s for Suppliers. Then, when referring to the columns from each of these tables, use the alias so that you're differentiating the column. Like this: SELECT c.CustomerName, s.SupplierName, etc. This is a standard practice when writing T-SQL. The other option is to fully qualify all column names. Meaning, put the table name in front of the column name: SELECT Customer.CustomerName, Supplier.SupplierName, etc. That's a very messy way to go about it though.
I am just not getting this at all. I have the three tables from doing this but i need it all in one table... This is what i have. I just can't for the life of me get the join to work because it keeps telling me it has an error and that the column names are not working. I don't know how to make it join when all the tables have all of the same column names. Two tables only use a contactname, the other table uses a firstname, lastname combined to be 'name' I cant get these to join to make it work as one table... SELECT[ContactName], [Address] +' ' + [City] + ' '+ ISNULL(Region, ' ') + ' ' + [PostalCode] as 'Address1' FROM [dbo].[Customers] Select [ContactName], [Address] +' ' + [City] + ' '+ ISNULL(Region, ' ') + ' ' + [PostalCode] as 'Address1' FROM [dbo].[Suppliers] SELECT([FirstName] + ' ' + [LastName]) as 'Name', [Address] +' ' + [City] + ' '+ ISNULL(Region, ' ') + ' ' + [PostalCode] as 'Address1' FROM [dbo].[Employees] order by [FirstName] GO
When you have tables that have matching columns, in order to refer to them within the same query, you must qualify the column names with the table. I suggest using aliases because it's easier. This means you do something like this to your query: SELECT e.City, c.City FROM Customers AS c JOIN Employee AS e ON c.SomeValue = e.SomeValue WHERE .... But from what you're showing, you have no JOIN criteria, at least none I can see, so what you probably need is a UNION operation. I'd suggest UNION ALL. Make sure all column names are identical: SELECT e.City FROM Employee as e UNION ALL SELECT c.City FROM Customer as c That will combine things like what you're asking for.
I am just not getting this at all. I have the three tables from doing this but i need it all in one table... This is what i have. I just can't for the life of me get the join to work because it keeps telling me it has an error and that the column names are not working. I don't know how to make it join when all the tables have all of the same column names. Two tables only use a contactname, the other table uses a firstname, lastname combined to be 'name' I cant get these to join to make it work as one table... SELECT[ContactName], [Address] +' ' + [City] + ' '+ ISNULL(Region, ' ') + ' ' + [PostalCode] as 'Address1' FROM [dbo].[Customers] Select [ContactName], [Address] +' ' + [City] + ' '+ ISNULL(Region, ' ') + ' ' + [PostalCode] as 'Address1' FROM [dbo].[Suppliers] SELECT([FirstName] + ' ' + [LastName]) as 'Name', [Address] +' ' + [City] + ' '+ ISNULL(Region, ' ') + ' ' + [PostalCode] as 'Address1' FROM [dbo].[Employees] order by [FirstName] GO
10 People are following this question.
