1

I have 2 SQL tables each with a column that represents the name... Name, FName.

I would like to programmatically select all of the Names in Table1 and all of the names in Table2 and view them as if they were all in one column called Name.

I have this so far, its not what I expected.

SELECT
    t1.FName AND t2.Name as Name
FROM 
    Table1 t1, Table2 t2

so imagine this

enter image description here

Linger
  • 14,942
  • 23
  • 52
  • 79
Jimmyt1988
  • 20,466
  • 41
  • 133
  • 233
  • Depending upon the RDBMS you're using, you can easily use concatenation operators and thus concatenate the two columns to select/display as one single column. Oracle uses `||` whereas MySQL and SQL Server allow the use of `+` as a concatenation operator. You can simply `SELECT t1.fname + ' ' + t2.fname AS name...`. – Rachcha Nov 27 '13 at 18:59

2 Answers2

2

In MySQL and SQL Server:

SELECT CONCAT(t1.FName, ' ', t2.Name) AS FullName
FROM Table1 t1, Table2 t2

In MS Access you would do the following:

SELECT t1.FName & ' ' & t2.Name AS FullName
FROM Table1 t1, Table2 t2

If you want all rows contained in on field then you really need the GROUP_CONCAT function. However, there is no GROUP_CONCAT in Microsoft Access. You will probably have to use some VBA to accomplish this task. Take a look at: Concatenate records and GROUP BY in Access.

EDIT:

Now your update is asking something totally different. If you want to the above result the following will give that to you without any duplicates:

SELECT t1.FName AS [Name]
FROM Table1 AS t1
UNION
SELECT t2.Name as [Name]
FROM Table2 AS t2

However, if t1 and t2 has a record that is the same and you don't want them to be combined then you would want to use:

SELECT t1.FName AS [Name]
FROM Table1 AS t1
UNION ALL
SELECT t2.Name as [Name]
FROM Table2 AS t2
Community
  • 1
  • 1
Linger
  • 14,942
  • 23
  • 52
  • 79
  • unfortunately CONCAT is throwing an error in Access´... any ideas why... it does say im in SQL view. I also don't want to concatenate them into the cells, I just want the list from 1 and the list from the other into one long list of the same thing – Jimmyt1988 Nov 27 '13 at 19:07
  • your last edit did the concatenation but unfortunatel I do not want my columns to have "data1 data2" as a row entry.. I just want to have a long list of all of them together in one viewing column – Jimmyt1988 Nov 27 '13 at 19:11
  • Keep in mind that CONCAT can only be used with SQL Server 2012 – NickyvV Nov 27 '13 at 19:22
  • I don't think I want to concatenate anything though? I just want to kind of do a Select * on both tables then return the data as if it were 1 table in 1 column – Jimmyt1988 Nov 27 '13 at 19:37
  • that is concatenating. String concatenation is the operation of joining two character strings end-to-end. For example, the concatenation of "snow" and "ball" is "snowball" – Linger Nov 27 '13 at 19:42
  • I can see how you misunderstood, sorry if it wasted your time man... Thanks for your help! – Jimmyt1988 Nov 27 '13 at 19:52
  • 1
    your welcome, remember `UNION` and `UNION ALL` could produce two different results. – Linger Nov 27 '13 at 19:53
1

Based on the image you added to the question, it seems you want a single column of unique names from those 2 tables with no concatenation involved. You can get that with a UNION query.

SELECT
    t1.Name AS [Name]
FROM 
    Table1 AS t1
UNION
SELECT
    t2.FName as [Name]
FROM 
    Table2 AS t2
HansUp
  • 95,961
  • 11
  • 77
  • 135