Merging tables - SQL Server Forums

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

Merging tables

Author

Topic

sqlkid
Starting Member

41 Posts

Posted - 2009-06-12 : 12:02:27

I have 2 tables with similar structure – T1 & T2.(see image below)
While TI has data for males,T2 has female data. I need to 1)join(?) the 2 tables subject to male & female having the same address ,date and if their activity time matches. (ie if both have same start & end time or it lies between the start & end time of the other)Pls note that the number of male/female with same address may not be equal.
2)Retrieve the data which is not joined – ie, remaining portion of T1& T2 which is not part of the joined table
[url][/url][url=http://img25.imageshack.us/i/datax.png/][img=http://img25.imageshack.us/img25/2962/datax.th.png][/url]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-12 : 12:47:11

What do want the output to look like (AKA - What is your expected output)?

Here is an exmple which might get you started:

DECLARE @T1 TABLE (Name VARCHAR(2), Address VARCHAR(2), StartTime INT, EndTime INT, Date VARCHAR(2))
INSERT @T1
SELECT 'M1', 'A1', 2113, 2119, 'd1'
UNION ALL SELECT 'M1', 'A1', 2120, 2135, 'd1'
UNION ALL SELECT 'M2', 'A2', 2321, 2330, 'd1'
UNION ALL SELECT 'M2', 'A2', 1232, 1245, 'd1'


DECLARE @T2 TABLE (Name VARCHAR(2), Address VARCHAR(2), StartTime INT, EndTime INT, Date VARCHAR(2))
INSERT @T2
SELECT 'F1', 'A1', 2116, 2118, 'd1'
UNION ALL SELECT 'F2', 'A2', 2100, 2130, 'd1'
UNION ALL SELECT 'F2', 'A2', 2323, 2328, 'd1'


SELECT 
	*
FROM 
	@T1 AS M
INNER JOIN 
	@T2 AS F
	ON M.Address = F.Address
	AND M.Date = F.Date
	AND
	(
		(M.StartTime >= F.StartTime AND M.EndTime <= F.EndTime)
		OR (F.StartTime >= M.StartTime AND F.EndTime <= M.EndTime)
	)

sqlkid
Starting Member

41 Posts

Posted - 2009-06-12 : 13:17:08

quote:

Originally posted by Lamprey

What do want the output to look like (AKA - What is your expected output)?

Here is an exmple which might get you started:

DECLARE @T1 TABLE (Name VARCHAR(2), Address VARCHAR(2), StartTime INT, EndTime INT, Date VARCHAR(2))
INSERT @T1
SELECT 'M1', 'A1', 2113, 2119, 'd1'
UNION ALL SELECT 'M1', 'A1', 2120, 2135, 'd1'
UNION ALL SELECT 'M2', 'A2', 2321, 2330, 'd1'
UNION ALL SELECT 'M2', 'A2', 1232, 1245, 'd1'


DECLARE @T2 TABLE (Name VARCHAR(2), Address VARCHAR(2), StartTime INT, EndTime INT, Date VARCHAR(2))
INSERT @T2
SELECT 'F1', 'A1', 2116, 2118, 'd1'
UNION ALL SELECT 'F2', 'A2', 2100, 2130, 'd1'
UNION ALL SELECT 'F2', 'A2', 2323, 2328, 'd1'


SELECT 
	*
FROM 
	@T1 AS M
INNER JOIN 
	@T2 AS F
	ON M.Address = F.Address
	AND M.Date = F.Date
	AND
	(
		(M.StartTime >= F.StartTime AND M.EndTime <= F.EndTime)
		OR (F.StartTime >= M.StartTime AND F.EndTime <= M.EndTime)
	)

If i do an inner join, since the number of male/feamle entries with same address may be unequal wont some columns get repeated ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 01:46:00

yup..if you do inner join you will get record repeated with all records of other table where times fall in b/w. so you want one male record to be always associated to single female record?

sqlkid
Starting Member

41 Posts

Posted - 2009-06-26 : 10:30:49

quote:
Originally posted by visakh16

yup..if you do inner join you will get record repeated with all records of other table where times fall in b/w. so you want one male record to be always associated to single female record?

No - just wanted to clarify. But this query fails in case the time extends to next day. eg : Male 22:30 - 00:15 Female 23:23 - 23:45 ? How to tackle such cases ?There is no separate 'day' column
Also my ques 2)Retrieve the data which is not joined – ie, remaining portion of T1& T2 which is not part of the joined table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 13:41:40

quote:
Originally posted by sqlkid

quote:
Originally posted by visakh16

yup..if you do inner join you will get record repeated with all records of other table where times fall in b/w. so you want one male record to be always associated to single female record?

No - just wanted to clarify. But this query fails in case the time extends to next day. eg : Male 22:30 - 00:15 Female 23:23 - 23:45 ? How to tackle such cases ?There is no separate 'day' column
Also my ques 2)Retrieve the data which is not joined – ie, remaining portion of T1& T2 which is not part of the joined table

if you just have to join based on time, remove M.Date = F.Date condition from where
2, to get this just take data from main table left joined to the first result on your primary key and filter for case where pkcol is null from the query

sqlkid
Starting Member

41 Posts

Posted - 2009-06-27 : 11:52:20

[
if you just have to join based on time, remove M.Date = F.Date condition from where
2, to get this just take data from main table left joined to the first result on your primary key and filter for case where pkcol is null from the query
[/quote]
what i meant is the query with (
(M.StartTime >= F.StartTime AND M.EndTime <= F.EndTime)
OR (F.StartTime >= M.StartTime AND F.EndTime <= M.EndTime)
)fails in cases where endtime<starttime for either male or female. date gives only start date and is a condition for joining

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-28 : 00:53:16

sorry i didnt get you. may be you can provide some data to show what you mean.

sqlkid
Starting Member

41 Posts

Posted - 2009-06-28 : 13:50:54

quote:
Originally posted by visakh16

sorry i didnt get you. may be you can provide some data to show what you mean.

ive sorted this out - thanx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources