LEFT OUTER 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

LEFT OUTER JOIN

Author

Topic

akramm
Starting Member

5 Posts

Posted - 2007-10-23 : 07:27:43

Hi All,

This is my first post to the forums. Can anyone tell me what is wrong with the following statement: -

SELECT V.VNUMBER,
V.PARAMETERID,
V.SEQUENCENUM,
V.VALUE,
V.GROUPID,
G.DESCRIPTION,
V.ENTRYDATE,
V.COMMENTS,
P.PARAMETERLABEL,
P.CATEGORY,
P.FIELDSCALE
FROM VBS_PARAMETERS P, (VBS_VOLUNTEER3 V LEFT OUTER JOIN VBS_GROUPS G)
ON V.GROUPID = G.GROUPID
AND P.PARAMETERID = V.PARAMETERID
WHERE V.PROSAMPLEID = 1

I'll appreciate a response.

Many Thanks,

I.A

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 07:32:52

This perhaps?


FROM VBS_PARAMETERS P
     JOIN VBS_VOLUNTEER3 V 
         ON V.PARAMETERID = P.PARAMETERID
     LEFT OUTER JOIN VBS_GROUPS G
         ON G.GROUPID = V.GROUPID

or were you trying to get the LEFT OUTER JOIN to VBS_GROUPS to be "optional" on the VBS_PARAMETERS table too?

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 07:34:14

[code]SELECT V.VNUMBER,
V.PARAMETERID,
V.SEQUENCENUM,
V.VALUE,
V.GROUPID,
G.DESCRIPTION,
V.ENTRYDATE,
V.COMMENTS,
P.PARAMETERLABEL,
P.CATEGORY,
P.FIELDSCALE
FROM VBS_PARAMETERS P
INNER JOIN VBS_VOLUNTEER3 V ON P.PARAMETERID = V.PARAMETERID
LEFT OUTER JOIN VBS_GROUPS G ON V.GROUPID = G.GROUPID
WHERE V.PROSAMPLEID = 1[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 07:34:49

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

akramm
Starting Member

5 Posts

Posted - 2007-10-23 : 07:41:22

Thanks Khtan..

Whats the difference between INNER & OUTER Joins?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 07:43:51

see http://msdn2.microsoft.com/en-us/library/ms191472.aspx

This is actually from BOL

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

akramm
Starting Member

5 Posts

Posted - 2007-10-23 : 07:53:33

SELECT V.VNUMBER, V.PARAMETERID, V.SEQUENCENUM, V.VALUE,
V.GROUPID,
G.DESCRIPTION,

P.FIELDSCALE
FROM VBS_VOLUNTEER3 V,
VBS_PARAMETERS P,
VBS_GROUPS G

WHERE V.PROSAMPLEID = ....
AND P.PARAMETERID = V.PARAMETERID
AND V.GROUPID *= G.GROUPID

This is the original query written in SQL Server 2000 specific syntax

Re-writing this in ANSI Specific syntax.... and I came up with the above which offcourse doesnt work... any ideas?

Thanks!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 08:14:53

Old style JOINS are not allowed in SQL Server 2005.
For valid syntax, see Kristens and Khtans suggestions.

E 12°55'05.25"
N 56°04'39.16"

akramm
Starting Member

5 Posts

Posted - 2007-10-23 : 08:27:42

Yeh hence the reason Im trying to replace them with ANSII Specific syntax! Do you know the valid syntax for the above query!?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 08:37:16

Yes I know. And Kristen. And khtan.
They have both posted a proper suggestion. I can post same again, if you prefer?

SELECT		v.VNUMBER,
		v.PARAMETERID,
		v.SEQUENCENUM,
		v.VALUE,
		v.GROUPID,
		g.DESCRIPTION,
		p.FIELDSCALE
FROM		VBS_VOLUNTEER3 AS v
INNER JOIN	VBS_PARAMETERS AS p ON p.PARAMETERID = v.PARAMETERID
LEFT JOIN	VBS_GROUPS AS g ON g.GROUPID = v.GROUPID
WHERE		v.PROSAMPLEID = ....

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 08:43:05

quote:
Originally posted by Peso

Old style JOINS are not allowed in SQL Server 2005.
For valid syntax, see Kristens and Khtans suggestions.

E 12°55'05.25"
N 56°04'39.16"

Do you expect error for the following?

select 	t1.n from 
	(select 1 as n union all select 3) as t1,
	(select 1 as n union all select 4) as t2
where t1.n=t2.n

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 08:49:01

Nah.
Cross joins are still allowed.

I was referencing

*=
=*

style joins.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 08:54:27

quote:
Originally posted by Peso

Nah.
Cross joins are still allowed.

I was referencing

*=
=*

style joins.

E 12°55'05.25"
N 56°04'39.16"

Well. I think I didnt see OP's old style query

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 09:07:25

"I can post same again, if you prefer?"

Hahahaha! I give up

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-23 : 09:31:59

LOL !

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources