asked
Trying to join three tables in sql
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
I answered this in the comments on your last post too. First, to JOIN, you need some criteria that matches the values together. From what you're showing of structure, you have none. If there is additional information, please post it. If, on the other hand, you're not trying to do a real JOIN, but simply trying to combine multiple disparate tables into a common result set, then you need to use UNION or UNION ALL. UNION will get you a unique data set, eliminating duplicate values. It's much more costly for performance. Based on what you're showing, UNION ALL would be better. This simply combines the data set. [Here's the Books Online entry for UNION][1]. Make sure you keep the column names the same. SELECT[ContactName], [Address] +' ' + [City] + ' '+ ISNULL(Region, ' ') + ' ' + [PostalCode] as 'Address1' FROM [dbo].[Customers] UNION ALL Select [ContactName], [Address] +' ' + [City] + ' '+ ISNULL(Region, ' ') + ' ' + [PostalCode] as 'Address1' FROM [dbo].[Suppliers] UNION ALL SELECT([FirstName] + ' ' + [LastName]) as 'ContactName', [Address] +' ' + [City] + ' '+ ISNULL(Region, ' ') + ' ' + [PostalCode] as 'Address1' FROM [dbo].[Employees] Note that I modified your alias in the last statement to say ContactName like the other two. [1]:
http://msdn.microsoft.com/en-US/library/ms180026(v=sql.105).aspx
This is just an aesthetic point - nothing to do with you original question, but a friend showed me the other day an nicer alternative to: ...[City] + ' '+ ISNULL(Region, ' ') + ' ' + [PostalCode]... In the above, if region is null you end up with three space characters in a row, which looks a bit unprofessional. An alternative is ...[City] + ' ' + INSULL(Region + ' ','') + [PostalCode]... If Region is NULL it follows that Region + ' ' is also NULL, so the entire string is replaced with an empty string, so you end up with ...[City] + ' ' + [PostalCode]... Well I thought it was nifty anyway.
11 People are following this question.
