Missing row - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Missing row

Author

Topic

matrixrep
Starting Member

30 Posts

Posted - 2014-04-11 : 13:49:39

I have two tables:

TABLE 1

Store------Employee------Begin----------END

001--------1201----------2014-01-01-----2014-02-01
001--------1201----------2014-02-02-----2014-02-05
001--------1201----------2014-02-06-----2014-02-08

TABLE 2

Store------Employee------Begin----------END

001--------1201----------2014-01-01-----2014-02-01
001--------1201----------2014-02-02-----2014-02-05
001--------1201----------2014-02-06-----2014-02-08
001--------1201----------2014-02-09-----0

I need a query that will find the difference between the two table in that case the missing row in table 1 from table 2
I have a lot of employees and stores.

This query can be used in msaccess.

Thanks in advance for your cooperation.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-11 : 15:24:43

[code]select * from Table2
EXCEPT
select * from Table1;
[/code]This query cannot be used in MSACCESS. I am posting only because you posted it to SQL 2008 forum.

In Access, one possible approach is to use a left join. But, from your example, I am not clear what the join conditions should be. Perhaps all the columns?

matrixrep
Starting Member

30 Posts

Posted - 2014-04-11 : 16:08:03

Find that did the trick in msaccess too.

SELECT K.STORE, K.EMPLOYEE, K.BEGIN, K.END

FROM
(SELECT MIN(tmp.TABLENAME) AS TABLELIST, tmp.STORE, tmp.EMPLOYEE, tmp.BEGIN, tmp.END
FROM (SELECT 'TABLE 1' as TABLENAME, A.STORE, A.EMPLOYEE, A.BEGIN, A.END
FROM TABLE 1 AS A
WHERE A.END="0"
UNION ALL
SELECT 'TABLE 2' as TABLENAME, B.STORE, B.EMPLOYEE, B.BEGIN, B.END
FROM TABLE 2 AS B )
AS tmp
GROUP BY tmp.STORE, tmp.EMPLOYEE, tmp.BEGIN, tmp.END)
AS K

WHERE K.TABLELIST="TABLE 2";

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources