UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Left Join And Right Join In Query, SQL Server 2008 R2    
 
   
mr.siro
post Sep 5 2019, 07:16 AM
Post#21



Posts: 185
Joined: 27-January 18



QUOTE
If you mean that literally then forgive me for saying so but that seems a bit naive. Adding an arbitrary row number won't make the data any more unique than it already was. You DO need to worry about uniqueness and data integrity irrespective of whether you use IDENTITY or not. Are you saying that the data in the other three columns is not expected to be unique? Your example data suggests otherwise.

I know i can set primary key with two column (EmpID and WorkDate). But i don't know primary key with two column better than one column (identity). If it better in my case, please tell me more detail, maybe i will change table structure.
Go to the top of the page
 
nvogel
post Sep 5 2019, 08:35 AM
Post#22



Posts: 1,023
Joined: 26-January 14
From: London, UK


From a data integrity point of view if EmpID and WorkDate are required to be unique then make them non-nullable and put a key constraint (i.e. uniqueness constraint) on those two columns. There are two ways to define key constraints: as UNIQUE or as PRIMARY KEY. It doesn't really matter which key is defined as PRIMARY KEY because that makes no practical difference.

You should also give some thought to which column(s) should be clustered on (i.e. clustered index). IDENTITY is often a good candidate for clustering but that does not mean you should add an IDENTITY column just to become the cluster key. Indexing is a big topic though and is not directly relevant to your question.



Go to the top of the page
 
mr.siro
post Sep 5 2019, 09:10 AM
Post#23



Posts: 185
Joined: 27-January 18



I have some test, with query below i get result which i want to. But if i change to BETWEEN WorkDate in WHERE clause, it's give wrong result. Have any ideal, please tell me.
CODE
SELECT *
FROM
    (SELECT EmpID,WorkDate,WorkID from dbo.tblA WHERE WorkDate = '20190904') as tbl1
FULL OUTER JOIN
    (SELECT EmpID,WorkDate,WorkID from dbo.tblB WHERE WorkDate = '20190904') as tbl2
ON tbl1.EmpID = tbl2.EmpID

This post has been edited by mr.siro: Sep 5 2019, 09:11 AM
Go to the top of the page
 
nvogel
post Sep 5 2019, 09:21 AM
Post#24



Posts: 1,023
Joined: 26-January 14
From: London, UK


Perhaps you wanted the date to be part of the join? Try this:

SELECT *
FROM tblA a
FULL OUTER JOIN tblB b
ON a.EmpID = b.EmpID
AND a.WorkDate = b.WorkDate
WHERE a.WorkDate BETWEEN '20190904' AND '20190904'
OR b.WorkDate BETWEEN '20190904' AND '20190904';
Go to the top of the page
 
mr.siro
post Sep 5 2019, 10:44 AM
Post#25



Posts: 185
Joined: 27-January 18



excellent, it's what i want. Problem solved. You are my idol nvogel.
Go to the top of the page
 
nvogel
post Sep 6 2019, 02:47 AM
Post#26



Posts: 1,023
Joined: 26-January 14
From: London, UK


You're welcome. Good luck with your project.
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2019 - 10:32 PM