Re: Help with a sql statement...

You need to decide where to do the processing, Client language (which you do not note) or PL/SQL. This may also depend upon the final destination of the data.

From a SQL perspective 2 starting options are easy to see. 1. Create a single result set with a query like SELECT S.SITE_ID, SN.SITE_NAME
FROM SITES S, SITE_NAMES SN
WHERE (S.SITE_ID = SN.SITE_ID)
ORDER BY S.SITE_ID You can then walk the result set concatenating SITE_NAMEs until you see a change in SITE_ID (oldSiteId != newSiteId)

2. Use 2 SQL queries and a nested loop
 SELECT DISTINCT SITE_ID FROM SITES SELECT SITE_NAME FROM SITE_NAMES
WHERE (SITE_ID = :SITE_ID)
In a client IDE you would probably set up a master detail relationship between the two queries using concatenation logic similar to option 1.

In PLSQL you would do it by declaring the two queries as cursors and looping through the iner query once for each record fetched from the outer query.

HTH - Clive
On Thu, 07 Dec 2000 15:20:50 GMT, Harry <a_harrison_uk_at_yahoo.co.uk> wrote:

>I have 2 tables -
>
>SITES SITE_NAMES
>--------- ----------------------
>SITE_ID SITE_ID
>SITE_VALUE SITE_NAME
>SITE_COST
>
>A site can have many names - I need to return a recordset set that includes in a single site record
>all "SITE_NAME"'s concatenated in to a single field i.e called "SITE_NAMES" that match on the
>"SITE_ID" field
>
>I know it's some sort of nested SELECT statement but I can't seem to get it to work as I want to
>
>Any ideas?
>
>thanks
>
>Harry
  Received on Sat Dec 09 2000 - 12:08:20 CST