SQL Query

[ro88o]

I have a SQL Query that looks like this...

SELECT released_title
FROM finishedtrack ft
WHERE ft.originates_from
IN (SELECT originates_from
FROM groupedon
WHERE album_id = (SELECT album_id
FROM Twenty_People_Info));

What I want to do is display a field called sequence from the groupedon table next to the released_title column (they are related by their originates_from value).

What my results should look like is the numbers 1-10 with a song name next to each however, the only thing I have been able to achieve is a list of song names in blocks of 10 with random numbers between 1 and 10 next to them (I attempted this by doing SELECT released_title, sequence FROM finishedtrack ft, groupedon go etc. which is obviously wrong).

Does anybody know how to get what I want (I'm not sure if I've explained my problem too well =P) ?

Thanks in advance for any help, I'll try to clarify anything you need if you don't understand what I'm getting at,
Tom

 

[r937]

SELECT ft.released_title
     , G.sequence
  FROM finishedtrack ft
inner
  join groupedon as G
    on G.originates_from = ft.originates_from 
   and G.album_id = 
          ( SELECT album_id
              FROM Twenty_People_Info )

note that your subquery selecting album_id from Twenty_People_Info has the potential of returning more than one row, which will cause a syntax error

r937.com | rudy.ca

 

[ro88o]

Thank you so much that's fantastic. Worked a treat (just had to remove 'as' from 'join groupedon as G'.

 

[r937]

just out of curiosity, which database system are you using? i've never heard of needing to remove the AS keyword

r937.com | rudy.ca

 

[ro88o]

I'm using an sql*plus with an Oracle database. With the 'as' keyword it threw up this error:

inner join groupedon as G
*
ERROR at line 3:
ORA-00905: missing keyword

I'd not seen the 'as' used in that situation in my course material so I just removed it.