SQL to join two tables by ID 6

[Steve-vfp9user]

Hello all
I have two tables MEMBERS and LKPJTYP
The MEMBERS table holds relevant data and the LKPJTYP table holds information about courses linked to members. The link URN between both tables is MEMBERID.
I am trying to create an SQL statement so I can link the members records to their respective courses by the MEMBERID which is a numeric field as is the MEMBERS dbf field.

Select * From MEMBERS WHERE MEMBERID In (Select MEMBERID From LKPJTYP Where LKPJTYP.MEMBERID=MEMBERS.MEMBERID) ;
  INTO TABLE tempinsert+'dbf'

The above provides a blank database but once it's correct I would then move on to the below.

USE tempfile+'.dbf' EXCLUSIVE

COPY TO myfile FIELDS TITLE, FORENAME1, SURNAME, ADD01, ADD02, ADD03, ADD04, ADD05, ;
  POSTCODE, EMAIL, PROJECTDES TYPE XLS

CLOSE DATABASES

DECLARE INTEGER ShellExecute IN shell32.dll ; 
  INTEGER hndWin, ; 
  STRING cAction, ; 
  STRING cFileName, ; 
  STRING cParams, ;  
  STRING cDir, ; 
  INTEGER nShowWin

cFileName = "\rsvpdms\"+mcsvfile
cAction = "open"
ShellExecute(0,cAction,cFileName,"","",1)

Can anyone please suggest what I'm doing wrong with the SQL.


Thank you

Steve Williams

 

[Dan Freeman]

Too many where clauses:

Select * From MEMBERS WHERE MEMBERID In (Select MEMBERID From LKPJTYP) ;
  INTO TABLE tempinsert+'dbf'

But why make it two different steps?

Select TITLE, FORENAME1, SURNAME, ADD01, ADD02, ADD03, ADD04, ADD05, ;
  POSTCODE, EMAIL, PROJECTDES From MEMBERS WHERE MEMBERID In (Select MEMBERID From LKPJTYP) ;
  INTO Cursor tempinsert
COPY TO myfile TYPE XL5

* etc.

 

[Steve-vfp9user]

Hi Dan
Thank you for the quick response
I am still getting a blank file after running the process.
Maybe I haven't made it clear, my mistake.
I am trying to use ALL the members records into a table together with thier associated courses linked by the MEMBERID

JONES Driving course
SMITH Driving course
SMITH HR course
WILLS Computer course
WILLS Driving course
WILLS HR course

Needless to say I've shortened the above just to show you what I mean.

If I didn't make it clear, then my apologies.

Thank you

Steve Williams

 

[mjcmkrsr]

Hi,

I am trying to use ALL the members records into a table together with their associated courses linked by the MEMBERID

You'll have to specify which fields are from which table. Try something like below

Select MB.TITLE, MB.FORENAME1, MB.SURNAME, MB.ADD01, MB.ADD02, MB.ADD03, MB.ADD04, MB.ADD05, MB.POSTCODE, MB.EMAIL, LK.PROJECTDES ;
     From MEMBERS MB ;
     JOIN LKPJTYP LK ON MB.MEMBERID = LK.MEMBERID ;
     INTO Cursor tempinsert
...

hth

MK

 

[Steve-vfp9user]

Thank you MK
That worked exactly as required.
Appreciate the responses

Thank you

Steve Williams

 

[mjcmkrsr]

Hi,
Thanks.
Please do also have a look at the code below. It shows a sketch of how you could approach the members/courses relations.

SET DELETED ON

CLOSE ALL 

&&& Create cursor with names

CREATE CURSOR curNames (cPKey C(4), cName C(10))
INSERT INTO curNames VALUES ( "1000","Joe")
INSERT INTO curNames VALUES ( "1001","Sam")
INSERT INTO curNames VALUES ( "1002","Jill")
INSERT INTO curNames VALUES ( "1003","Mary")
INSERT INTO curNames VALUES ( "1004","Zoe")
INSERT INTO curNames VALUES ( "1005","Jack")

&&& Create cursor with Subjects

CREATE CURSOR curSubjects (cPKey C(4), cSubject C(10))
INSERT INTO curSubjects VALUES ( "1000","English-1")
INSERT INTO curSubjects VALUES ( "1001","Math-1")
INSERT INTO curSubjects VALUES ( "1002","French-1")
INSERT INTO curSubjects VALUES ( "1003","History-1")
INSERT INTO curSubjects VALUES ( "1004","Chem-1")
INSERT INTO curSubjects VALUES ( "1005","Physics-1")
INSERT INTO curSubjects VALUES ( "1100","English-2")
INSERT INTO curSubjects VALUES ( "1101","Math-2")
INSERT INTO curSubjects VALUES ( "1102","French-2")
INSERT INTO curSubjects VALUES ( "1103","History-2")
INSERT INTO curSubjects VALUES ( "1104","Chem-2")
INSERT INTO curSubjects VALUES ( "1105","Physics-2")


&&& Create cursor with subjects taken

CREATE CURSOR curCross (cFKNames C(4), cFKSubjects C(4))
INSERT INTO curCross VALUES ( "1000","1000") && Joe takes English
INSERT INTO curCross VALUES ( "1000","1001") && Joe takes Math
INSERT INTO curCross VALUES ( "1000","1003") && Joe takes History
INSERT INTO curCross VALUES ( "1001","1000") && Sam takes English
INSERT INTO curCross VALUES ( "1001","1003") && Sam takes History
INSERT INTO curCross VALUES ( "1001","1004") && Sam takes Chem
INSERT INTO curCross VALUES ( "1002","1000") && Jill takes English
INSERT INTO curCross VALUES ( "1002","1002") && Jill takes French
INSERT INTO curCross VALUES ( "1002","1003") && Jill takes History
INSERT INTO curCross VALUES ( "1003","1003") && Mary takes History
INSERT INTO curCross VALUES ( "1004","1001") && Zoe takes Math
INSERT INTO curCross VALUES ( "1004","1004") && Zoe takes Chem
INSERT INTO curCross VALUES ( "1001","1002") && Sam takes French

&&& Create cursor with all possible combinations

SELECT curNames.cPKey as cFKNames, curSubjects.cPKey as cFKSubjects FROM curNames, curSubjects ;
	INTO CURSOR curNamesBysubjects READWRITE 
	
&&& Create cursor with the subjects NOT taken

DELETE curNamesBySubjects FROM curNamesBySubjects ;
	JOIN curCross ON curNamesBySubjects.cFKNames = curCross.cFKNames AND curNamesBySubjects.cFKSubjects = curCross.cFKSubjects
	
&&& Show names with subjects  - by name-subject

SELECT curNames.cName, curSubjects.cSubject FROM curNames ;
	LEFT JOIN curCross ON curNames.cPKey = curCross.cFKNames ;
	LEFT JOIN curSubjects ON curSubjects.cPKey = curCross.cFKSubjects ;
	ORDER BY 1, 2 ;
	INTO CURSOR curByName

LOCATE 
BROWSE TITLE "Subjects - by Name and Subject" 

&&& Show names with subjects - by subject-name

SELECT curNames.cName, curSubjects.cSubject FROM curNames ;
	RIGHT JOIN curCross ON curNames.cPKey = curCross.cFKNames ;
	RIGHT JOIN curSubjects ON curSubjects.cPKey = curCross.cFKSubjects ;
	ORDER BY 2, 1 ;
	INTO CURSOR curBySubject

LOCATE
BROWSE TITLE "Subjects - by Subject and Name"

&&& Show names with subjects NOT taken - by name-subject

SELECT curNames.cName, curSubjects.cSubject FROM curNames ;
	JOIN curNamesBySubjects ON curNames.cPKey = curNamesBySubjects.cFKNames ;
	JOIN curSubjects ON curSubjects.cPKey = curNamesBySubjects.cFKSubjects ;
	ORDER BY 1, 2 ;
	INTO CURSOR curSubjectsNotTaken

LOCATE
BROWSE TITLE "Subjects NOT taken , by Name - Subject" 

SELECT curNames.cName, curSubjects.cPKey, curSubjects.cSubject FROM curNames ;
	JOIN curNamesBySubjects ON curNames.cPKey = curNamesBySubjects.cFKNames ;
	JOIN curSubjects ON curSubjects.cPKey = curNamesBySubjects.cFKSubjects ;
	WHERE curNames.cName = "Joe" ;
	ORDER BY 1, 2 ;
	INTO CURSOR curSubjectsNotTaken

LOCATE
BROWSE TITLE "Subjects NOT taken by Joe" 

SELECT curNames.cName, curSubjects.cPKey, curSubjects.cSubject FROM curNames ;
	JOIN curNamesBySubjects ON curNames.cPKey = curNamesBySubjects.cFKNames ;
	JOIN curSubjects ON curSubjects.cPKey = curNamesBySubjects.cFKSubjects ;
	WHERE SUBSTR(curSubjects.cPKey,2,1) = "1" ;
	ORDER BY 1, 2 ;
	INTO CURSOR curSubjectsNotTaken

LOCATE
BROWSE  TITLE "Subjects - category 1 NOT taken"

SELECT curNames.cName, curSubjects.cPKey, curSubjects.cSubject FROM curNames ;
	JOIN curNamesBySubjects ON curNames.cPKey = curNamesBySubjects.cFKNames ;
	JOIN curSubjects ON curSubjects.cPKey = curNamesBySubjects.cFKSubjects ;
	WHERE SUBSTR(curSubjects.cPKey,2,1) <= "2" ;
	ORDER BY 1, 2 ;
	INTO CURSOR curSubjectsNotTaken

LOCATE
BROWSE  

SET DELETED OFF 
CLOSE ALL

hth
MK

 

[Steve-vfp9user]

Wow, thank you for the examples MK
I'm still fairly new to SQL but picking it up quietly!

Thank you

Steve Williams

 

[JRB-Bldr]

At least as far as SQL JOIN's go I often recommend that a new developer look over:
Inner-Outer JOINs

Good Luck,
JRB-Bldr

 

[Steve-vfp9user]

Hi JRB-Bldr

Great link and appreciate the share.

Thank you

Steve Williams

 

[Olaf Doschke]

Just another comment on your initial query: You're using a query in the where clause, WHERE something IN (SELECT ...). Such a query in brackets is called subquery.

Subqueries - any form of them - are an advanced topic you need for complex queries, nothing you already need to join two tables - or even ten! Joins are part of a single query, even no matter how many tables you join together, the first time you should need additional queries is using the other join type, vertically joining data (further rows) by UNION, and even they mostly occur, if the source of the additional rows you UNION join are is another table. For data from the same table you can have two partial WHERE conditions combined with OR to union data for which condition1 is valid (the first chunk of data) OR condition2 is valid (the second chunk of data), that won't need a union.

All I'm saying is: Operations like a join are simple, and a language, that already needs a concept like a subquery for such simple operations would already be replaced by something simpler. So before you ever write a subquery ask yourself, if there can't be any simpler way. The part JOIN LKPJTYP LK ON MB.MEMBERID = LK.MEMBERID is all you need to have LKPJTYP data joined in rows with equal memberid and this pattern of how to join is very common, you join on one common column foreign key = primary key, also for further tables. Whatever fields you want in the overall result you add to the inial field list of SELECT fieldlist FROM, so you don't need another SELECT fieldlist.

Bye, Olaf.

 

[Mike Lewis]

Steve,

You've already received a lot of information, which I'm sure you'll find helpful.

I just wanted to add that, whenever I've wanted to explore some aspect of SQL, I've always found Tamar Granor's articles and conference papers particularly helpful. If you go to

http://www.tomorrowssolutionsllc.com/conferencepapers.php,

and look for articles with names like "Learn to Use SQL" and "Making the Most of SQL-SELECT", I'm sure you'll find a lot of useful information.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Olaf and Mike
Your posts and advice are most welcome and appreciated as always.

Thank you

Steve Williams

 

[Steve-vfp9user]

I have checked out that link Mike which is most beneficial so credit where its due to Tamar

Thank you

Steve Williams

 

[TamarGranor]

Thanks. Beyond the conference papers, my site also contains hundreds of my articles. Hope you find them helpful.

Tamar