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 4 2019, 04:43 AM
Post#1



Posts: 186
Joined: 27-January 18



Hello guys, i have tblA and tblB as yellow area and blue area. How to query result like green area
Attached File(s)
Attached File  Untitled.png ( 6.06K )Number of downloads: 1
 
Go to the top of the page
 
ADezii
post Sep 4 2019, 06:26 AM
Post#2



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


Will tblA and tblB always have exactly the same number of Records?
Go to the top of the page
 
mr.siro
post Sep 4 2019, 07:17 AM
Post#3



Posts: 186
Joined: 27-January 18



no, they not same number of record.
Go to the top of the page
 
theDBguy
post Sep 4 2019, 07:18 AM
Post#4


UA Moderator
Posts: 76,910
Joined: 19-June 07
From: SunnySandyEggo


Hi. Look into FULL OUTER JOIN. Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ADezii
post Sep 4 2019, 08:05 AM
Post#5



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


@theDBGuy:
Wouldn't the Query have to be dynamically created since the ALL side of the Outer Join would depend on which Table has the most Records?
Go to the top of the page
 
theDBguy
post Sep 4 2019, 08:14 AM
Post#6


UA Moderator
Posts: 76,910
Joined: 19-June 07
From: SunnySandyEggo


Hi. The way I understood it was the OP wants to return all the records from both tables. Maybe I misread the question because it’s too early.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Sep 4 2019, 08:32 AM
Post#7


UA Admin
Posts: 36,209
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

In TSQL Full Outer Join is essentially like a Right Outer Join Unioned with a Left Outer Join on the two tables.

I'm hacking out a small demo to verify, but I'm pretty sure Full Outer Join is the right path.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ADezii
post Sep 4 2019, 08:33 AM
Post#8



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


More than likely, I am misreading the question but here is the way I interpret it:
CODE
On Error Resume Next
Dim lngCountA As Long
Dim lngCountB As Long
Dim strSQL As String
Dim qdf As DAO.QueryDef
Const conQUERY_NAME = "qryDemo"

lngCountA = DCount("*", "tblA")     'Number of Records in tblA
lngCountB = DCount("*", "tblB")     'Number of Records in tblB

CurrentDb.QueryDefs.Delete conQUERY_NAME

'The SQL will change depending of which Table has the greater number of Records
If lngCountA >= lngCountB Then
  strSQL = "SELECT tblA.ID, tblA.Info, tblB.ID, tblB.Info FROM tblA " & _
         "LEFT JOIN tblB ON tblA.ID = tblB.ID;"
Else
  strSQL = "SELECT tblA.ID, tblA.Info, tblB.ID, tblB.Info FROM tblB " & _
           "LEFT JOIN tblA ON tblB.ID = tblA.ID;"
End If

Set qdf = CurrentDb.CreateQueryDef(conQUERY_NAME, strSQL)

DoCmd.OpenQuery conQUERY_NAME, acViewNormal, acReadOnly

  1. tblA and tblB same amount of Records:
    tblA.IDtblA.InfotblB.IDtblB.Info
    1a1aa
    2b2bb
    3c3cc
    4e4dd
  2. Number of Records in tblA > tblB:
    tblA.IDtblA.InfotblB.IDtblB.Info
    1a1aa
    2b2bb
    3c3cc
    4e4dd
    5f
  3. Number of Records in tblB > tblA:
    tblA.IDtblA.InfotblB.IDtblB.Info
    1a1aa
    2b2bb
    3c3cc
    4e4dd
    5f5ee
    6ff

P.S. - Hopefully, I didn't over-complicate matters. iconfused.gif
This post has been edited by ADezii: Sep 4 2019, 08:39 AM
Go to the top of the page
 
GroverParkGeorge
post Sep 4 2019, 08:42 AM
Post#9


UA Admin
Posts: 36,209
Joined: 20-June 02
From: Newcastle, WA


Confirmed:

Attached File  FOJ.PNG ( 6.2K )Number of downloads: 0


At least with the small sample data set available.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Sep 4 2019, 08:51 AM
Post#10


UA Admin
Posts: 36,209
Joined: 20-June 02
From: Newcastle, WA


Caveat. I no longer have SQL Server 2008 R2 available to verify , but this syntax should be the same.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ADezii
post Sep 4 2019, 08:52 AM
Post#11



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


Sorry guys, missed the SQL Server part! shrug.gif
Go to the top of the page
 
GroverParkGeorge
post Sep 4 2019, 08:53 AM
Post#12


UA Admin
Posts: 36,209
Joined: 20-June 02
From: Newcastle, WA


Actually, because Access doesn't support Full Outer Joins, you would have to go through that sort of process to get a comparable result.

Fortunately, this is a SQL Server query.

And, I am also assuming that the small sample data set indicates the requirement is to return all records from both tables.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
MadPiet
post Sep 4 2019, 09:44 AM
Post#13



Posts: 3,367
Joined: 27-February 09



FWIW, I ran this with an extra record:

CODE
use tempdb;
GO
CREATE TABLE tblA (ID int primary key, Info char);
CREATE TABLE tblB (ID int primary key, Info char(2));
GO
INSERT INTO tblA VALUES (1,'a'),(2,'b'),(3,'c');
INSERT INTO tblB VALUES (1,'aa'),(2,'bb'),(4,'dd');
INSERT INTO tblB VALUES (5,'ee');


Same query as everyone else:
CODE
SELECT a.ID, a.info, b.id, b.info
FROM tblA a FULL OUTER JOIN tblB b ON a.ID = b.ID;


Result:
CODE
ID    info    id    info
1    a    1    aa
2    b    2    bb
3    c    NULL    NULL
NULL    NULL    4    dd
NULL    NULL    5    ee

Go to the top of the page
 
mr.siro
post Sep 4 2019, 08:23 PM
Post#14



Posts: 186
Joined: 27-January 18



hello guys, i forgot tell you guys, ID is not primary key, i have another field with identity is primary, ID not a primary key in both table. So, full outer join not give right result.
This post has been edited by mr.siro: Sep 4 2019, 08:25 PM
Go to the top of the page
 
GroverParkGeorge
post Sep 4 2019, 08:48 PM
Post#15


UA Admin
Posts: 36,209
Joined: 20-June 02
From: Newcastle, WA


This kind of miscommunication often happens when someone tries to "simplify" a problem by using aliases and leaving out pieces. That often backfires because the important details are lost or distorted, as in this case.

Can you give us some real data to look at?

The queries as offered will work as long as you have fields on which you can create the join. Please tell us what those fields are and what kind of data is in those fields. And best of all would be real sample data.

Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
MadPiet
post Sep 4 2019, 09:57 PM
Post#16



Posts: 3,367
Joined: 27-February 09



Okay. Post your CREATE TABLE scripts and INSERT scripts to recreate your situation. Include all the indexing, too.
This post has been edited by MadPiet: Sep 4 2019, 10:00 PM
Go to the top of the page
 
mr.siro
post Sep 5 2019, 02:31 AM
Post#17



Posts: 186
Joined: 27-January 18



hello guys, i sorry , my bad. Here is sample data.
two table will join on EmpID.
This post has been edited by mr.siro: Sep 5 2019, 02:33 AM
Attached File(s)
Attached File  test3.zip ( 114.14K )Number of downloads: 1
 
Go to the top of the page
 
nvogel
post Sep 5 2019, 03:48 AM
Post#18



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


Hi Mr.siro,

Do you have the solution already or do you still have a question?

Not everyone will want to restore data from a backup file. It helps if you can post DDL (CREATE TABLE) and supply sample data as INSERT statements. For completeness, here is a sample of some of the data you attached (not all of it):

CREATE TABLE dbo.tblA(
id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
EmpID nvarchar(50) NULL,
WorkDate date NULL,
WorkID nvarchar(5) NULL);

CREATE TABLE dbo.tblB(
id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
EmpID nvarchar(50) NULL,
WorkDate date NULL,
WorkID nvarchar(5) NULL);

SET IDENTITY_INSERT dbo.tblA ON;
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (420, N'10001', '2019-08-03', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (421, N'10005', '2019-08-04', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (422, N'10002', '2019-08-05', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (423, N'10002', '2019-08-06', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (424, N'10004', '2019-08-07', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (425, N'10001', '2019-08-08', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (426, N'10005', '2019-08-09', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (427, N'10002', '2019-08-10', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (428, N'10003', '2019-08-11', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (429, N'10128', '2019-08-22', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (430, N'10134', '2019-08-23', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (431, N'10141', '2019-08-23', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (432, N'10092', '2019-08-23', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (433, N'10147', '2019-08-23', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (434, N'10102', '2019-08-23', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (435, N'10053', '2019-08-23', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (436, N'10078', '2019-08-23', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (437, N'10070', '2019-08-23', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (438, N'10081', '2019-08-23', N'T24');
INSERT INTO dbo.tblA ( id, EmpID, WorkDate, WorkID ) VALUES (439, N'10097', '2019-08-23', N'T24');
SET IDENTITY_INSERT dbo.tblA OFF;

SET IDENTITY_INSERT dbo.tblB ON;
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (1, N'20173', '2019-09-03', N'CT');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (2, N'50005', '2019-09-03', N'DH');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (3, N'50035', '2019-09-03', N'HC');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (4, N'10002', '2019-09-04', N'T24');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (5, N'10003', '2019-09-04', N'DH');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (6, N'10004', '2019-09-04', N'HC');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (7, N'40098', '2019-09-04', N'HS');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (8, N'90001', '2019-09-04', N'NT');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (9, N'90003', '2019-09-04', N'O');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (10, N'90005', '2019-09-04', N'PN');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (11, N'90006', '2019-09-04', N'T12');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (12, N'90074', '2019-09-04', N'T24');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (13, N'10002', '2019-09-03', N'CT');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (14, N'10003', '2019-09-03', N'DH');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (15, N'10004', '2019-09-03', N'HC');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (16, N'40098', '2019-09-03', N'HS');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (17, N'90001', '2019-09-03', N'NB');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (18, N'90003', '2019-09-03', N'NT');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (19, N'90005', '2019-09-03', N'O');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (20, N'90006', '2019-09-03', N'PN');
INSERT INTO dbo.tblB ( id, EmpID, WorkDate, WorkID ) VALUES (21, N'90074', '2019-09-03', N'RT');
SET IDENTITY_INSERT dbo.tblB OFF;

SELECT *
FROM tblA a
FULL OUTER JOIN tblB b
ON a.EmpID = b.EmpID;

I notice that EmpID isn't unique in either table so your join on EmpID may result in more rows than you started with. I also note that the IDENTITY column is the only key in your tables - it's not necessarily wrong to do that but it tends to be relatively unusual. The other columns are all nullable and the ID (IDENTITY column) seems to be unimportant to your question. If you need more help then maybe you could explain a bit more about what the data means and what result you are looking for.
Go to the top of the page
 
mr.siro
post Sep 5 2019, 05:21 AM
Post#19



Posts: 186
Joined: 27-January 18



QUOTE
I notice that EmpID isn't unique in either table so your join on EmpID may result in more rows than you started with

Yes, that why used full outer join not work for my case.
I used identity because in my code, i send datable in vb.net to variable in storeprocedure. With identity i don't need to worry about primary key in that table.
I want to find records which are different at INFO column between tblA and tblB on EmpID. in startdate and end date i want.
Go to the top of the page
 
nvogel
post Sep 5 2019, 05:42 AM
Post#20



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


There is no column called INFO in your tables. There is a WorkID. Maybe you want something like the following. You will need to be more specific if you want an exact answer.

SELECT *
FROM tblA a, tblB b
WHERE a.EmpID = b.EmpID AND a.WorkID <> b.WorkID;

QUOTE
With identity i don't need to worry about primary key in that table

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.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    15th December 2019 - 01:57 PM