Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
DATABASES, TABLES AND QUERIES >>  SQL JOINS

  tushar Kanvinde
  Where is tushar Kanvinde?
 Kolhapur, Maharastra
 India
 tushar Kanvinde



We will create 2 cursors with common and different id

CREATE CURSOR Cursor1 (id int, Narration c(20))
INSERT INTO Cursor1 values (1,'Cursor1 with ID 1')
INSERT INTO Cursor1 values ( 2,'Cursor1 with ID 2')
INSERT INTO Cursor1 values ( 3,'Cursor1 with ID 3')
INSERT INTO Cursor1 values ( 4,'Cursor1 with ID 4')

CREATE CURSOR Cursor2 ( id int, Narration c ( 20))
INSERT INTO Cursor2 values ( 1,'Cursor2 with ID 1')
INSERT INTO Cursor2 values ( 2,'Cursor2 with ID 2')
INSERT INTO Cursor2 values ( 5,'Cursor2 with ID 5')
INSERT INTO Cursor2 values ( 6,'Cursor2 with ID 6')

The Common IDs are 1,2 Lets call them SET A
IDs only in Cursor1 are 3,4 Lets call them SET B
IDs only in Cursor2 are 5,6 Lets call them SET C

We do an inner join on the two cursors

SELECT ;
cursor1.id id1,cursor1.narration narration1,cursor2.id id2,cursor2.narration narration2 ;
FROM cursor1 ;
INNER JOIN cursor2 ON cursor1.id=cursor2.id INTO CURSOR innerjoin

* INNER JOIN = SET A

We do an left join on the two cursors

SELECT ;
cursor1.id id1,cursor1.narration narration1,cursor2.id id2,cursor2.narration narration2 ;
FROM cursor1 ;
LEFT JOIN cursor2 ON cursor1.id=cursor2.id INTO CURSOR leftjoin

* LEFT JOIN = SET A + SET B

We do an right join on the two cursors

SELECT ;
cursor1.id id1,cursor1.narration narration1,cursor2.id id2,cursor2.narration narration2 ;
FROM cursor1 ;
RIGHT JOIN cursor2 ON cursor1.id=cursor2.id INTO CURSOR rightjoin

* RIGHT JOIN = SET A + SET C

We do an full join on the two cursors

SELECT ;
cursor1.id id1,cursor1.narration narration1,cursor2.id id2,cursor2.narration narration2 ;
FROM cursor1 ;
FULL JOIN cursor2 ON cursor1.id=cursor2.id INTO CURSOR fulljoin

* FULL JOIN = SET A + SET B + SET C

FEEDBACK

Khurram Tahir @ 6/25/2011 9:01:02 AM
Thanks for such a good example to understand join , in Last line
Full Join = SET A + SET C + SET C

In my opinion

Full Join = SET A + SET B + SET C

tushar @ 6/25/2011 9:07:35 AM
Thanks Khurram. Have corrected it

mohammad shahzad @ 11/16/2011 7:16:16 PM
i want to learn foxpor 9.0 first please tell about system menu how can i use foxpor system

lee hock ang @ 10/18/2012 2:14:05 PM
thanks !

radha sundar @ 2/18/2013 2:08:53 PM
sir,
i have a exam table which has date_exam field. in it different dates are there.i want to take a report in such a way that the report should be grouped and come in such a way that the different dates in date_exam should appear horizontally and under that which subject code the students are writing.
my table will be like this
subcode date_exam papertitle session
csa1 14/4/2013 digital FN
himc 14/4/2013 archieves AN
stba 17/4/2013 statistics FN
elca 15/4/2013 journalism AN
ecma 15/4/2013 microeco FN

and so on
but i need the report to be like this
14/4/2013 FN AN 17/4/2013 FN AN 15/4/2013 FN AN
csa1 himc stba ecma elca

like this
how to do it

radha sundar @ 2/18/2013 2:09:45 PM
this may not be relevent to this page. But I would like to solve this. will you please help me

tushar @ 2/18/2013 2:46:48 PM
Hi Radha

Could you put this question in the forum. I can then help you there.

gurubarand @ 11/27/2018 1:50:11 PM
super sir!



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: