SQL Problem

[Ruslan Zasukhin]

On 10/28/04 2:27 AM, "Joerg Pressel" <joergp at three-2-one.com> wrote:

Hi Joerg,

> I'm looking for some advice with a SQL statement (using V4RB 1.11):
> 
> I have three tables:
> - "Albums" with Artist_Ptr pointing to "Artists" and field "Title"
> - "Tracks" with Album_Ptr pointing to "Albums" and Artist_Ptr pointing
> to "Artists"
> - "Artists" containing artist names (field "Artist")
> 
> I want to get all Albums which contain Tracks from a certain artist.
> 
> So far so easy:
> SELECT DISTINCT Albums.Title FROM Tracks,Albums WHERE Artist_Ptr=1606
> 
> But ­ I need one additional information from the Albums which were
> found: the artist name.
> 
> I tried:
> SELECT DISTINCT Albums.Title,Artist FROM Tracks,Albums,Artists WHERE
> Artist_Ptr=1606
> This does not work, because Valentina does not know if she should take
> "Artist" using the Artist_Ptr from "Albums" or from "Tracks".
> 
> Is there any way I can get around this problem? Any suggestion what
> would be the most elegant way?

You need self specify the path

SELECT DISTINCT Albums.Title,Artist
FROM Tracks,Albums,Artists
WHERE 
     Artist_Ptr=1606
And
    albums.artist_ptr = artist.recid

-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------