×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL to join two tables by ID
6

SQL to join two tables by ID

SQL to join two tables by ID

(OP)
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.

CODE -->

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.

CODE -->

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

RE: SQL to join two tables by ID

Too many where clauses:

CODE -->

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

But why make it two different steps?

CODE -->

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. 

RE: SQL to join two tables by ID

(OP)
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

RE: SQL to join two tables by ID

Hi,

Quote:


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

CODE -->

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

RE: SQL to join two tables by ID

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

Thank you

Steve Williams

RE: SQL to join two tables by ID

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.

CODE -->

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

RE: SQL to join two tables by ID

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

Thank you

Steve Williams

RE: SQL to join two tables by ID

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

RE: SQL to join two tables by ID

(OP)
Hi JRB-Bldr

Great link and appreciate the share.

Thank you

Steve Williams

RE: SQL to join two tables by ID

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.

RE: SQL to join two tables by ID

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/conferencepap..., 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

RE: SQL to join two tables by ID

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

Thank you

Steve Williams

RE: SQL to join two tables by ID

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

Thank you

Steve Williams

RE: SQL to join two tables by ID

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

Tamar

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close