Display all Records from Left and Right Table

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)

Display all Records from Left and Right Table

Author

Topic

happening
Starting Member

7 Posts

Posted - 2014-08-01 : 07:04:12

I have 2 Table
Table01 has 3 fields ie [ID01, Code01 , Amount01]
Table02 has 3 fields ie [ID02, Code02 , Amount02]
----------------
Table01 Values
ID01 Code01 Amount01
698 AAA698 100
698 BBB698 200
698 CCC698 300
699 AAA699 400
699 BBB699 500
--------------------
Table02 Values
ID02 Code02 Amount02
698 AAA698 100
698 BBB698 200
699 AAA699 300
699 BBB699 400
699 CCC699 500

Note : Now I have for
ID 698 I have from Table01 three records and Table02 Two Records
ID 699 I have from Table01 Two records and Table02 Three Records

Now Out put should be as follows
ID01 Code01 Amount01 ID01 Code01 Amount01
698 AAA698 100 698 AAA698 100
698 BBB698 200 698 BBB698 200
698 CCC698 300 --- --- ---
699 AAA699 400 699 AAA699 300
699 BBB699 500 699 BBB699 400
--- --- --- 699 CCC699 500

The similar equal keys are from
Table01-ID01 and Code01
Table02-ID02 and Code02

Please Help me to get this output
Thanking you

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-01 : 07:17:02

select a.id01,a.code01,a.amount01,b.id01,b.code01,b.amount01
from table01 a
left join table01 b
on a.id01=b.id01

Javeed Ahmed

happening
Starting Member

7 Posts

Posted - 2014-08-01 : 07:36:12

Thanks Javed for the Reply
The output is duplicated several times with respect to each and every Code01
and there is no null records shown

happening
Starting Member

7 Posts

Posted - 2014-08-01 : 07:41:21

Only 6 Records should be the output
Now Out put should be as follows
ID01 Code01 Amount01 ID02 Code02 Amount02
698 AAA698 100 698 AAA698 100
698 BBB698 200 698 BBB698 200
698 CCC698 300 --- ----- ---
699 AAA699 400 699 AAA699 300
699 BBB699 500 699 BBB699 400
--- ----- ---- 699 CCC699 500

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-01 : 07:48:52

sorry my mistake,try this
select a.id01,a.code01,a.amount01,b.id01,b.code01,b.amount01
from table01 a
left join table02 b
on a.id01=b.id01

Javeed Ahmed

happening
Starting Member

7 Posts

Posted - 2014-08-01 : 07:54:21

I tried this
but records are duplicated with respect to code01
eg if I have 2 id's and 3 code i get many multiple records with respect to code
and also it does not show null records either on 1st or 2nd table

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-01 : 08:01:49

iam getting 5 records with this
select a.id01,a.code01,a.amount01,b.id02,b.code02,b.amount02
from table01 a
left join table02 b
on a.id01=b.id02 and a.code01=b.code02

Javeed Ahmed

happening
Starting Member

7 Posts

Posted - 2014-08-01 : 08:16:42

Thanks Javed
it is working fine but only 1 problem as shown below out of 6 records it shows only 5 records
the last Record is missing
--- ----- ---- 699 CCC699 500
This Type of records
can u also please help me in this
----------------------------------------------------------
Only 6 Records should be the output
Now Out put should be as follows
ID01 Code01 Amount01 ID02 Code02 Amount02
698 AAA698 100 698 AAA698 100
698 BBB698 200 698 BBB698 200
698 CCC698 300 --- ----- ---
699 AAA699 400 699 AAA699 300
699 BBB699 500 699 BBB699 400
--- ----- ---- 699 CCC699 500
-------------------------------------------------------------

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-08-01 : 08:22:43

You should provide test data in a consumable format:


CREATE TABLE #t1
(
	ID01 int NOT NULL
	,Code01 varchar(10) NOT NULL
	,Amount01 int NOT NULL
);
INSERT INTO #t1
VALUES (698, 'AAA698', 100)
	,(698, 'BBB698', 200)
	,(698, 'CCC698', 300)
	,(699, 'AAA699', 400)
	,(699, 'BBB699', 500);
CREATE TABLE #t2
(
	ID02 int NOT NULL
	,Code02 varchar(10) NOT NULL
	,Amount02 int NOT NULL
);
INSERT INTO #t2
VALUES (698, 'AAA698', 100) 
,(698, 'BBB698', 200)
,(699, 'AAA699', 300)
,(699, 'BBB699', 400)
,(699, 'CCC699', 500);

The following seems to be what you want:


SELECT *
FROM #t1 T1
	FULL JOIN #t2 T2
		ON T1.ID01 = T2.ID02
			AND T1.Code01 = T2.Code02

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-01 : 08:23:53

here you go
select a.id01,a.code01,a.amount01,b.id02,b.code02,b.amount02
from table01 a
full outer join table02 b
on a.id01=b.id02 and a.code01=b.code02

Javeed Ahmed

happening
Starting Member

7 Posts

Posted - 2014-08-01 : 08:28:14

Thank you very Much Javed
Yes Noted : i am just new to it that is why i did not know the actual procedure of putting the request, hence forth i will do it the way u said. Thank you very much for the kind help

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-01 : 08:30:33

welcome

Javeed Ahmed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources