Inner join problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Inner join problem

Author

Topic

seekaye2
Starting Member

2 Posts

Posted - 2012-08-19 : 06:23:31

Hi
I have three tables

PUPILSSTUDYING

PupilID : PupilName : SubjectID : Subject
-------------------------------------------
1------ : Joe Smith : 1001----- : Maths
1------ : Joe Smith : 1002----- : Stats
1------ : Joe Smith : 1003----- : Geography

SUBJECTLIST

SubjectID : Subject-- : TargetTYPE
-----------------------------------
1001----- : Maths---- : Mathematics
1002----- : Stats---- : Mathematics
1003----- : Geography : Humanities

TARGETS

PupilID : PupilName : TargetType--: Target
------------------------------------------
1------ : Joe Smith : Mathematics : A
1------ : Joe Smith : Humanities--: C
1------ : Joe Smith : English---- : B

I want to return

PupilID : PupilName : SubjectID : Subject---: TARGET
----------------------------------------------------
1------ : Joe Smith : 1001----- : Maths---- : A
1------ : Joe Smith : 1002----- : Stats---- : A
1------ : Joe Smith : 1003----- : Geography : C

I tried

SELECT PS.pupilID,PS.pupilname,PS.xsubject,T.Target
FROM ((pupilsstudying PS
INNER JOIN subjectlist SL ON ps.subjectID=SL.subjectID)
INNER JOIN targets T ON SL.targettype=T.targettype)
WHERE PS.PupilID=1

but this didn't seem to match the targets properly and generated lots of extra lines

I'm coding in VB.net using an Access database

Any help appreciated, thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-19 : 07:05:32

I am familiar with Access only in passing, so the following query may not compile correctly. But the idea is that you need to join on the PupilID as well.

SELECT
	ps.PupilID,
	ps.PupliName,
	ps.SubjectId,
	ts.Target
FROM
	PUPILSSTUDYING ps
	INNER JOIN SUBJECTLIST sl ON 
		sl.SubjectID = ps.SubjectId AND sl.Subject = ps.Subject
	INNER JOIN TARGETS ts ON
		ts.PupliId = ps.PupilID AND ts.TargetType = sl.TargetType
WHERE
	PS.PupilID=1

seekaye2
Starting Member

2 Posts

Posted - 2012-08-19 : 09:26:54

Many thanks.
Without joining the UPNs it was returning records for other pupils as well, working properly now

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-19 : 10:22:02

the given suggestion will yield multiple match from last join. Are you sure you dont anymore fields to join with last table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources