SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Combining Columns from 2 Tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dandee266
Starting Member

USA
2 Posts

Posted - 08/07/2014 :  19:14:21  Show Profile  Reply with Quote
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

USA
37316 Posts

Posted - 08/07/2014 :  19:45:57  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

dandee266
Starting Member

USA
2 Posts

Posted - 08/08/2014 :  09:55:46  Show Profile  Reply with Quote
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



Go to Top of Page

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 08/09/2014 :  12:21:08  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000