SQL help

[John Sims]

Hi Steve,

It's hard to say for sure without seeing the actual database structure, 
but try this:

SELECT TypeGenre.cTypeAbbrev, AlbumTags.nAlbumId, Albums.cTitle, Albums.nAlbumId
FROM TypeGenre
LEFT OUTER JOIN AlbumTags ON AlbumTags.typePtr=TypeGenre.RecID
LEFT OUTER JOIN Albums ON Albums.RecID=AlbumTags.albumPtr


-John



On 2/25/11 6:19 PM, Steve Albin wrote:
> 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
>>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina