SQL help

[Steve Albin]

Thanks John,

I see where my syntax error occurred, but the resultant cursor for this does not give me what I want.  The TypeGenre table is a small table with a dozen records that contain tags that may or may not be on an album.  I want a cursor that will contain the same number of records as contained in the TypeGenre table, but the nAlbumId field will be null for occurrences where the TypeGenre record is not used.  That is the purpose of the outer join.  

Here is what I want:

Hollywood		null	null				null		
Piano			null	null				null	
Jazz			1	Plays W. C. Handy	1
Pop			null	null				null			
Blues			null	null				null			
Country		null	null				null			
Classical		null	null				null			
Christmas		null	null				null			
Latin			null	null				null			
Vocals		1	Plays W. C. Handy	1		
Anthology		null	null				null			
Soundtrack		null	null				null			
Opera		null	null				null		


Here is what I am getting using the SQL you've corrected:

Jazz			1	Plays W. C. Handy	1
Vocals		1	Plays W. C. Handy	1		

(Like you, I hope the formatting holds up for this!) 


The SQL I created using the WHERE clause to specify the outer join works.  Specifying the outer join in the FROM clause does not work.  I find this odd, but maybe there is a reason for it.

I'm satisfied using the SQL with the join specified in the WHERE clause.  You've answered my question as to the syntax problem I was having and I'm glad to know that for other uses.

Thanks,

Steve




--
Steve Albin, Montclair, NJ
http://www.steve-albin.com
http://www.jazzdiscography.com

On Feb 25, 2011, at 3:50 PM, Steve Albin wrote:

> I built this SQL using VStudio 4.8 SQL builder:
> 
> SELECT TypeGenre.cTypeAbbrev, AlbumTags.nAlbumId, Albums.cTitle, Albums.nAlbumId
> FROM	AlbumTags RIGHT OUTER JOIN TypeGenre ON AlbumTags.typePtr = TypeGenre.RecID 
> 	AlbumTags INNER JOIN Albums ON AlbumTags.albumPtr = Albums.RecID 
> WHERE Albums.nAlbumId = 1
> 
> On running this, I get an error saying unexpected WHERE token.
> 
> I created this SQL myself:
> 
> SELECT TypeGenre.cTypeAbbrev, AlbumTags.nAlbumId, Albums.cTitle, Albums.nAlbumId
> FROM	AlbumTags,TypeGenre, Albums
> WHERE (albums.nAlbumId= 1)
> and AlbumTags.typePtr =* TypeGenre.RecID 
> and AlbumTags.albumPtr = Albums.RecID
> 
> This gives me the result I am looking for.
> 
> My question is, what is wrong with the first SQL?  Do we have a bug in VStudio?  Am I missing something obvious?
> 
> --
> Steve Albin, Montclair, NJ
> http://www.steve-albin.com
> http://www.jazzdiscography.com
>