SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Display all Records from Left and Right Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

happening
Starting Member

India
7 Posts

Posted - 08/01/2014 :  07:04:12  Show Profile  Reply with Quote
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

India
675 Posts

Posted - 08/01/2014 :  07:17:02  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
Go to Top of Page

happening
Starting Member

India
7 Posts

Posted - 08/01/2014 :  07:36:12  Show Profile  Reply with Quote
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
Go to Top of Page

happening
Starting Member

India
7 Posts

Posted - 08/01/2014 :  07:41:21  Show Profile  Reply with Quote
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

Go to Top of Page

ahmeds08
Aged Yak Warrior

India
675 Posts

Posted - 08/01/2014 :  07:48:52  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
Go to Top of Page

happening
Starting Member

India
7 Posts

Posted - 08/01/2014 :  07:54:21  Show Profile  Reply with Quote
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


Go to Top of Page

ahmeds08
Aged Yak Warrior

India
675 Posts

Posted - 08/01/2014 :  08:01:49  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
Go to Top of Page

happening
Starting Member

India
7 Posts

Posted - 08/01/2014 :  08:16:42  Show Profile  Reply with Quote
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
-------------------------------------------------------------
Go to Top of Page

Ifor
Aged Yak Warrior

615 Posts

Posted - 08/01/2014 :  08:22:43  Show Profile  Reply with Quote
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
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
675 Posts

Posted - 08/01/2014 :  08:23:53  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
Go to Top of Page

happening
Starting Member

India
7 Posts

Posted - 08/01/2014 :  08:28:14  Show Profile  Reply with Quote
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
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
675 Posts

Posted - 08/01/2014 :  08:30:33  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
welcome

Javeed Ahmed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000