SQL combine two select statements output into one

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

SQL combine two select statements output into one

Author

Topic

ahaile
Starting Member

25 Posts

Posted - 2009-06-03 : 12:47:55

I am trying to merge/join two queries on two different tables. Due to unique code value on table 1, JOIN returns 4 rows. Does anyone knows on how to get by this?
I have been searching around for days but not able to find a solution. Any help would be greatly appreciated.
SQL SERVER 2005

table1
cname enum date code
----- ----- -------- --------
max 1234 12/05/08 m
max 1234 12/05/08 g

table 2
cname enum time
------- --------- -------------
max 1234 12/05/08 7:00 AM
max 1234 12/05/08 3:30 PM

Desired Output:

cname enum date code time
----- ------ ------ --------------
max 1234 12/05/08 m 12/05/08 7:00 AM
max 1234 12/05/08 g 12/05/08 3:30 PM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 12:50:36

how do you decide which record of table1 should be joined which record of table2? both date fields have same datevalue

ahaile
Starting Member

25 Posts

Posted - 2009-06-03 : 12:54:57

That is the challege i am having. Is it possible to have the raw with earlier timestamp join first.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 12:59:23

k. then use this


SELECT t1.cname,t1.enum,t1.date,t1.code,t2.time
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY cname,enum ORDER BY date) AS Seq,* FROM table1)t1
INNER JOIN  (SELECT ROW_NUMBER() OVER (PARTITION BY cname,enum ORDER BY date) AS Seq,* FROM table2)t2
ON t2.cname=t1.cname
AND t2.enum=t1.enum
AND t2.Seq=t1.Seq

ahaile
Starting Member

25 Posts

Posted - 2009-06-03 : 13:20:12

thanks for your help!
I am getting syntax error on ROW_NUMBER() OVER. unexpected '(' expecting ')'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 13:25:03

are you using sql 2005? which editor are you using? also if used code is different from what i gave, please post your full code

ahaile
Starting Member

25 Posts

Posted - 2009-06-03 : 13:30:20

yes, i am using sql 2005 business intellegence visual studio.

ahaile
Starting Member

25 Posts

Posted - 2009-06-03 : 13:31:59

select top 100 * , t1.business_unit_id, t1.employee_id, t1.business_date, t1.case_type_code, t2.punch_time_stamp
FROM (Select ROW_NUMBER() OVER (PARTITION BY business_unit_id, employee_id ORDER BY business_date) AS Seg, * FROM LAB_PAY_EXCEPTION)t1
INNER JOIN (Select ROW_NUMBER() OVER (PARTITION BY business_unit_id, employee_id ORDER BY business_date) AS Seq, * FROM LAB_PUNCH_RAW)t2
ON t2.business_unit_id = t1.business_unit_id
AND t2.employee_id = t1.employee_id
AND t2.Seq= t1.Seq

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 13:39:17

can you try running this in query analyser first and see if it works.
If not working try the below and post result

SELECT @@VERSION

ahaile
Starting Member

25 Posts

Posted - 2009-06-03 : 13:49:01

I got UNRECOGNIZED command.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 13:53:41

are you running this in query analyser as i suggest?

ahaile
Starting Member

25 Posts

Posted - 2009-06-03 : 14:02:18

I am running it from Microsoft report designer. The only access I have to client database.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 14:07:24

dont you have query analyser access?

ahaile
Starting Member

25 Posts

Posted - 2009-06-03 : 14:45:28

no query analyser. Only query builder which Microsoft Report Designer. But i was able to run SELECT @@VERSION on my local SQL SERVER 2008.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-03 : 14:58:30

then use temporary tables


Select  (SELECT COUNT(*) +1 FROM LAB_PAY_EXCEPTION WHERE business_unit_id = l.business_unit_id and employee_id =l.employee_id AND business_date<l.business_date) AS Seq, * INTO #LAB_PAY_EXCEPTION 
FROM LAB_PAY_EXCEPTION l

Select  (SELECT COUNT(*) +1 FROM LAB_PAY_EXCEPTION WHERE business_unit_id = l.business_unit_id and employee_id =l.employee_id AND business_date<l.business_date) AS Seq, * INTO #LAB_PUNCH_RAW
FROM LAB_PUNCH_RAW l

SELECT top 100  t1.business_unit_id, t1.employee_id, t1.business_date, t1.case_type_code, t2.punch_time_stamp
FROM #LAB_PAY_EXCEPTION t1
INNER JOIN #LAB_PUNCH_RAW t2
ON t2.business_unit_id = t1.business_unit_id
AND t2.employee_id = t1.employee_id
AND t2.Seq= t1.Seq

ahaile
Starting Member

25 Posts

Posted - 2009-06-03 : 15:10:23

Now I am getting invalid column name 'business_date'. 'business_date' is a valid column name on Lab_pay_exception table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-04 : 12:43:58

show your query please

ahaile
Starting Member

25 Posts

Posted - 2009-06-04 : 15:30:07

I am trying to use the query you provided above. i was not sure if i were suppose to make change.

Select (SELECT COUNT(*) +1 FROM LAB_PAY_EXCEPTION WHERE business_unit_id = l.business_unit_id and employee_id =l.employee_id AND business_date<l.business_date) AS Seq, * INTO #LAB_PAY_EXCEPTION
FROM LAB_PAY_EXCEPTION l

Select (SELECT COUNT(*) +1 FROM LAB_PAY_EXCEPTION WHERE business_unit_id = l.business_unit_id and employee_id =l.employee_id AND business_date<l.business_date) AS Seq, * INTO #LAB_PUNCH_RAW
FROM LAB_PUNCH_RAW l

SELECT top 100 t1.business_unit_id, t1.employee_id, t1.business_date, t1.case_type_code, t2.punch_time_stamp
FROM #LAB_PAY_EXCEPTION t1
INNER JOIN #LAB_PUNCH_RAW t2
ON t2.business_unit_id = t1.business_unit_id
AND t2.employee_id = t1.employee_id
AND t2.Seq= t1.Seq

ahaile
Starting Member

25 Posts

Posted - 2009-06-04 : 16:07:54

I changed the business_date to punch_timestamp on the 2nd select statement, and added where clause and couple filters. At least at this point the query does not error instead it is timing out, but this could be something from my end.

thank you for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-05 : 13:10:50

quote:
Originally posted by ahaile

I changed the business_date to punch_timestamp on the 2nd select statement, and added where clause and couple filters. At least at this point the query does not error instead it is timing out, but this could be something from my end.

thank you for your help!

how much data does your tables contain?

ahaile
Starting Member

25 Posts

Posted - 2009-06-05 : 14:49:14

I am only expecting two rows for teting. I am now attempting to use your suggestion which is inserting into a temp table. I am working to see output one table at a time but still timing out.

SELECT (select count(*) + 1 from lab_punch_raw where business_unit_id =1000026 AND punch_timestamp BETWEEN '12/6/2008' AND '12/7/2008' AND employee_id = '1021060') AS temp, * INTO #Lab_punch_raw From lab_punch_raw

SELECT business_unit_id ,employee_id , punch_timestamp, punch_type_code FROM lab_punch_raw
WHERE punch_timestamp BETWEEN '12/6/2008' AND '12/7/2008'
AND business_unit_id = '1000026'
AND employee_id = '1021060'

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources