Combining Columns from 2 Tables

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combining Columns from 2 Tables

Author

Topic

dandee266
Starting Member

2 Posts

Posted - 2014-08-07 : 19:14:21

I am using the JOIN function to pull data from two tables. Table_A has all columns I need; Table_B contains only 1 column I need. The column I need data from in Table_A is called CITY_NAME and stops May 1st. The column I need in Table_B (which has the same values but begins May 2nd) is labeled CITY. In Table_A I have NULL values starting Mat 1st for CITY_NAME. In Table_B, I have NULL values for any date before May 2nd.

I need to replace the NULL values in table B (May 1st and forward) with the values that are in Table B

SELECT
a.DATE,
a.STATE,
b.CITY
FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.ID = b.ID

I need to use a function similar to UNION, but TABLE_A has 10 columns and TABLE_3 has 3 columns.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-07 : 19:45:57

Could you show us some sample data and expected output to make your question clear?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

dandee266
Starting Member

2 Posts

Posted - 2014-08-08 : 09:55:46

Thank you and yes:

Table_A
ID DATE CITY
1 4/29/14 Denver
2 4/30/14 Spokane
3 5/1/14 Cheyenne
4 4/29/14 NULL
5 4/30/14 NULL
6 5/1/14 NULL
7 4/29/14 NULL
8 4/30/14 NULL
9 5/1/14 NULL
10 4/29/14 NULL

Table_B
ID DATE City_NAME
1 4/29/14 NULL
2 4/30/14 NULL
3 5/1/14 NULL
4 4/29/14 Denver
5 4/30/14 Spokane
6 5/1/14 Cheyenne
7 4/29/14 Denver
8 4/30/14 Spokane
9 5/1/14 Cheyenne
10 4/29/14 Denver

Results:
DATE CITY Number
4/29/14 Denver 3
4/30/14 Spokane 4
5/1/14 Cheyenne 3

Query to get count, but not sure how to join the CITY and CITY_NAME from the two tables:

SELECT
a.DATE,
a.CITY,
COUNT (a.ID) as NUMBER
FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.ID = a.ID
GROUP BY a.DATE, a.CITY

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-08-09 : 12:21:08

Okay be warned, I have only been programming for 2 weeks, and have no schooling, so......but -

to replicate --
----------------------------------------------------------------
CREATE TABLE #TABLE_A (ID INT,DATE_ DATE,CITY CHAR(15))
INSERT INTO #TABLE_A (ID,DATE_,CITY)
VALUES (1, '4/29/14', 'Denver')
INSERT INTO #TABLE_A (ID,DATE_,CITY)
VALUES (2, '4/30/14', 'Spokane')
INSERT INTO #TABLE_A (ID,DATE_,CITY)
VALUES (3, '5/1/14', 'Cheyenne')
INSERT INTO #TABLE_A (ID,DATE_,CITY)
VALUES (4, '4/29/14', NULL)
INSERT INTO #TABLE_A (ID,DATE_,CITY)
VALUES (5, '4/30/14', NULL)
INSERT INTO #TABLE_A (ID,DATE_,CITY)
VALUES (6, '5/1/14', NULL)
INSERT INTO #TABLE_A (ID,DATE_,CITY)
VALUES (7, '4/29/14', NULL)
INSERT INTO #TABLE_A (ID,DATE_,CITY)
VALUES (8, '4/30/14', NULL)
INSERT INTO #TABLE_A (ID,DATE_,CITY)
VALUES (9, '5/1/14', NULL)
INSERT INTO #TABLE_A (ID,DATE_,CITY)
VALUES (10,'4/29/14', NULL)

CREATE TABLE #TABLE_B (ID INT, DATE_ DATE, CITY_NAME CHAR(15))
INSERT INTO #TABLE_B(ID,DATE_,CITY_NAME)
VALUES (1, '4/29/14', NULL)
INSERT INTO #TABLE_B(ID,DATE_,CITY_NAME)
VALUES (2, '4/30/14', NULL)
INSERT INTO #TABLE_B(ID,DATE_,CITY_NAME)
VALUES (3, '5/1/14', NULL)
INSERT INTO #TABLE_B(ID,DATE_,CITY_NAME)
VALUES (4, '4/29/14', 'Denver')
INSERT INTO #TABLE_B(ID,DATE_,CITY_NAME)
VALUES (5, '4/30/14', 'Spokane')
INSERT INTO #TABLE_B(ID,DATE_,CITY_NAME)
VALUES (6, '5/1/14', 'Cheyenne')
INSERT INTO #TABLE_B(ID,DATE_,CITY_NAME)
VALUES (7, '4/29/14', 'Denver')
INSERT INTO #TABLE_B(ID,DATE_,CITY_NAME)
VALUES (8, '4/30/14', 'Spokane')
INSERT INTO #TABLE_B(ID,DATE_,CITY_NAME)
VALUES (9, '5/1/14', 'Cheyenne')
INSERT INTO #TABLE_B(ID,DATE_,CITY_NAME)
VALUES (10, '4/29/14', 'Denver')

-----------------------------------------------------------------
okay select * from #TABLE_A ORDER BY DATE_
---------------------------------
ID DATE_ CITY
1 2014-04-29 Denver
4 2014-04-29 NULL
7 2014-04-29 NULL
10 2014-04-29 NULL
8 2014-04-30 NULL
5 2014-04-30 NULL
2 2014-04-30 Spokane
3 2014-05-01 Cheyenne
6 2014-05-01 NULL
9 2014-05-01 NULL

------------------------------------------------------------------
Bringing information in from other table if date is greater than 2014/5
-------------------------------------------------------------------

BEGIN TRAN
UPDATE #TABLE_A
SET
#TABLE_A.CITY = #TABLE_B.CITY_NAME
FROM #TABLE_A
INNER JOIN #TABLE_B
ON #TABLE_A.DATE_ = #TABLE_B.DATE_ AND #TABLE_A.DATE_>='2014-05-01'

----------------------------------------------------------
table a if committed
------------------------------------------------
ID DATE_ CITY
1 2014-04-29 Denver
4 2014-04-29 NULL
7 2014-04-29 NULL
10 2014-04-29 NULL
8 2014-04-30 NULL
5 2014-04-30 NULL
2 2014-04-30 Spokane
3 2014-05-01 Cheyenne
6 2014-05-01 Cheyenne
9 2014-05-01 Cheyenne

Hope that helps, fun setting it up, ty ty

Thanks,
Chris
Jr Programmer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources