question

Sirkssim avatar image
Sirkssim 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 -.-
sql-server-2008sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
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.
0 Likes 0 ·
Sirkssim avatar image
Sirkssim answered
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 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.