Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Query

Status
Not open for further replies.

ro88o

Programmer
Jun 25, 2005
24
GB
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
 
Code:
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
 
Thank you so much that's fantastic. Worked a treat (just had to remove 'as' from 'join groupedon as G'.
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top