SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Combining Left and Inner Join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

seekaye
Starting Member

6 Posts

Posted - 12/16/2012 :  10:52:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/16/2012 :  16:36:23  Show Profile  Reply with Quote
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';
Go to Top of Page

seekaye
Starting Member

6 Posts

Posted - 12/16/2012 :  17:14:10  Show Profile  Reply with Quote
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...
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17626 Posts

Posted - 12/16/2012 :  20:15:45  Show Profile  Reply with Quote
are you using SQL Server ?


KH
Time is always against us

Go to Top of Page

seekaye
Starting Member

6 Posts

Posted - 12/17/2012 :  01:15:52  Show Profile  Reply with Quote
I'm using VB.net with access
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17626 Posts

Posted - 12/17/2012 :  01:27:47  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000