Combining Left and Inner Join - 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

Combining Left and Inner Join

Author

Topic

seekaye
Starting Member

6 Posts

Posted - 2012-12-16 : 10:52:31

I have three tables

PupilStudying

PUPIL..SUBJECT

Anna...Maths
Bill...Maths
Chas...Maths
Dave...Maths
Anna...English
Bill...English
Chas...English
Dave...English

SubjectList

SUBJECT....TARGETTYPE

Maths......Numeracy
English....Literacy
Science....General

PupilTargets

PUPIL......TARGETTYPE....TARGET

Anna.......Numeracy......A
Chas.......Numeracy......D
Dave.......Numeracy......C
Anna.......Literacy......B
Chas.......Literacy......B
Dave.......Literacy......E
Anna.......General.......B
Chris......General.......B
Dave.......General.......E

I would like to retrieve each students maths target with a null value for Bill as his is missing

Anna A
Bill -
Chas D
Dave C

I've got as far as

SELECT

PS.Pupil,
PT.Target,

FROM

((PupilsStudying PS

INNER JOIN SubjectList SL
ON PS.subject=SL.subject)

LEFT JOIN PupilsTargets
ON PT.pupil=PS.pupil AND PT.targettype=SL.targettype)

WHERE
PS.SUBJECT='Maths'

but get a “JOIN NOT SUPPORTED” error. It seems to be the PT.targettype=SL.targettype which it doesn’t like. I also have a second inner join and left join in the query which works when this part is removed so i've left them out for simplicity

I'm using Access

Any help appreciated

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-16 : 16:36:23

One of these?

--- 1
SELECT
	ps.Pupil,
	pt.Target
FROM
	PupilStudying ps
	LEFT JOIN PupilTargets pt ON pt.pupil = ps.pupil
WHERE
	ps.Subject = 'Maths';

--- 2
SELECT
	ps.Pupil,
	pt.Target
FROM
	PupilStudying ps
	LEFT JOIN SubjectList sl ON sl.Subject = ps.Subject 
	LEFT JOIN PupilTargets pt ON pt.pupil = ps.pupil AND pt.TargetType = slTargetType
WHERE
	ps.Subject = 'Maths';

seekaye
Starting Member

6 Posts

Posted - 2012-12-16 : 17:14:10

thanks for your response

the first suggestion returns more than one line for some students, if their english or general target is different from their maths.

the second one still says join not supported.
it is definitely the second bit of the join it doesn't like

LEFT JOIN PupilTargets pt ON pt.pupil = ps.pupil AND pt.TargetType = sl.TargetType

the PT.TargetType=SL.TargetType, presumably because i'm not joining the pt table directly to the ps table but using a field from the sl table which i already joined on...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-12-16 : 20:15:45

are you using SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

seekaye
Starting Member

6 Posts

Posted - 2012-12-17 : 01:15:52

I'm using VB.net with access

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-12-17 : 01:27:47

You have posted under a SQL Server forum. And solution suggested are for SQL Server. for MS Access question, you should post over at http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3

I am not MS Access expert, but you can try


FROM
((PupilsStudying PS 
  INNER JOIN SubjectList SL ON PS.subject=SL.subject)
  LEFT JOIN PupilsTargets PT ON PT.pupil=PS.pupil AND PT.targettype=SL.targettype)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources