subquery problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

subquery problem

Author

Topic

davidliv
Starting Member

45 Posts

Posted - 2004-05-01 : 14:56:36

I'm making my query way to complex. There has to be a simpler path than the one I'm on.

I have two tables.
My first table (Event) contains event details. The second table contains a list of products for an event using the EventID.

I simply want a result set that looks like this:

EventID Event Start End Products
1 SpringEvent 4/5/2004 6/10/2004 app1, app2, app4, app7
2 SummerEvent 1/1/2004 1/20/2004 app2, app4, app8, app9
3 TestEvent 7/1/2004 8/1/2004 app7, app8, app5, app10
4 SecretEvent 8/1/2004 10/1/2004 app8, app2, app4, app7

My problem is that my joins are returning multiple event records. 1 per product.

Thanks in advance.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-01 : 15:03:36

Is there a max of 4 products per event?

select eventid ,
(select top 1 product from products p where p.eventid = e.eventid order by product) ,
(select top 1 product from (select top 2 product from products p where p.eventid = e.eventid order by product) a order by product desc) ,
(select top 1 product from (select top 3 product from products p where p.eventid = e.eventid order by product) a order by product desc) ,
(select top 1 product from (select top 4 product from products p where p.eventid = e.eventid order by product) a order by product desc) ,
from Evet e

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

davidliv
Starting Member

45 Posts

Posted - 2004-05-01 : 15:19:35

No, there can be any number of products per event.. and event no products.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-01 : 15:41:06

Then you can do the same ting in dynamic sql
declare @i int, @sql varchar(8000)
select @i = max(prds) from (select prds = count(*) from products group by event_id) a

while @i > 0
begin
select @sql = coalesce(@sql + ',','') + '(select top 1 * from (select top ' + convert(varchar(10),@i) + ' product from products p where p.eventid = e.eventid order by product) a order by product desc)'
select @i = @i -1
end
select @sql = 'select eventid , ' + @sql + ' from Event e'
exec (@sql)

You can't get the variable number of cols without dynamic sql or a called SP.
You could also give back a delimitted list in a chanracter column. For this you would would create a udf to return the products in a string for te event and use it in the select.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

davidliv
Starting Member

45 Posts

Posted - 2004-05-01 : 16:29:55

returning a comma delimited string from a subquery was my initial thought.

Something like....
SELECT eventid, event, CONVERT(varchar(150), SELECT product FROM tProduct WHERE eventid = eventid) FROM tEvent

But I couldn't figure out how to 1)query the product table with the current row's eventid or 2)how to place the returned string within the query.

any further ideas

gpl
Posting Yak Master

195 Posts

Posted - 2004-05-01 : 17:58:21

David
If you are using SQL2000, then you can create a user defined function to return the list
Here is an example using code I pinched from this site

Graham

Select
EventID,
Event,
Start
[End],
Convert(Varchar(2000), dbo.ListProducts(EventID))
From Event

CREATE FUNCTION dbo.ListProducts (@EventID int)
RETURNS[sql_variant] AS

BEGIN
DECLARE @ProductList varchar(2000)

SELECT @ProductList = COALESCE(@ProductList + ', ', '') + Product
FROM Products
WHERE EventID = @EventID

RETURN @ProductList
END

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-01 : 21:53:29

That should be
RETURNS varchar(2000)

call it by
select eventid , dbo.ListProducts(eventid)
from Evet e

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

davidliv
Starting Member

45 Posts

Posted - 2004-05-02 : 14:04:33

Sweet. Thanks a lot. Using the User Function just opened a whole new world for me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources