Help in Select using 3 tables - 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

General SQL Server Forums

New to SQL Server Programming

Help in Select using 3 tables

Author

Topic

elvinny
Starting Member

1 Post

Posted - 2010-09-29 : 11:43:09

Hi

I need help in doing a query: I have 3 tables, one with a channelID and a groupID (table1), another with a AssetID and a groupID(table2) and for last other with AssetID and OfferID. I need a select which chooses 8 offerIDs, ramdomly for each channelID. If i have 120 channels i need 120*8 offer ids, or 8 offers per channel. I used the next sql query but the result is not right.




SELECT
 table1.CHANNEL_ID 
,table3.OFFER_ID

FROM [RECOM_VOD].[CHANNELS_VS_ASSET_GROUP] as table1
INNER JOIN [RECOM_VOD].[ASSETS_VS_ASSET_GROUP] as table2
ON  table2.ASSET_GROUP_ID = table1.ASSET_GROUP_ID
JOIN [RECOM_VOD].[ASSETS] as table3
ON table3.ID_ASSETS = table2.ASSET_ID

WHERE table2.ASSET_ID IN 
( 

SELECT top 8

tableA.ID_ASSETS
FROM .[RECOM_VOD].[ASSETS] as tableA 
WHERE OFFER_ID is not NULL 
order by NEWID()
)
GROUP BY table3.OFFER_ID,table1.CHANNEL_ID
ORDER BY table1.CHANNEL_ID

Regards

Luis

jleitao
Posting Yak Master

100 Posts

Posted - 2010-09-29 : 18:46:25

hi elvinny,

First at all, sorry my english :( I hope my SQL be a bit better.

Right now i dont have way to test and see performace of the next query. However tomorrow i can test it if you have any problem.

The query return a result set for each channel_id you have. If you need all in the same result set you can "replace" the select commad by a insert command to a new table and then you can make a simple select. If you cant create a new table probably someone else will have a great and better ideia (we hope) :D

DECLARE
@AUX_ROW_NUMBER AS INT,
@AUX_MAX_ROW_NUMBER AS INT

--ENUMERATE ALL CHANNEL_ID AND GIVES A ROW NUMBER
--IF CHANNEL_ID IS NOT UNIQUE YOU NEED ADD A DISTINCT IN THE NEXT QUERY
SELECT @AUX_MAX_ROW_NUMBER = MAX(ROWNUMBER) FROM (
SELECT ROW_NUMBER() OVER(ORDER BY CHANNEL_ID) AS ROWNUMBER
FROM TABLE1) A

SET @AUX_ROW_NUMBER = 1

--EXECUTE THE QUERY (TOP 8) FOR EACH CHANNEL_ID YOU HAVE
WHILE @AUX_ROW_NUMBER =< @AUX_MAX_ROW_NUMBER
BEGIN

--
SELECT TOP 8
C.ID_ASSETS
AUXB.CHANNEL_ID
FROM TABLE3 C
INNER JOIN
(
SELECT B.ASSETID, AAUX.CHANNEL_ID
FROM TABLE2 B
INNER JOIN
(
SELECT * FROM(
--IF CHANNEL_ID IS NOT UNIQUE YOU NEED ADD A DISTINCT IN THE NEXT QUERY
SELECT ROW_NUMBER() OVER(ORDER BY CHANNEL_ID) AS ROWNUMBER, CHANNEL_ID, GROUPID
FROM TABLE1 A
)A1 WHERE ROWNUMBER = @AUX_ROW_NUMBER
)AAUX
ON
B.GROUPID = AAUX.GROUPID
) AUXB
ON C.ASSETID = AUXB.ASSETID
WHERE OFFER_ID IS NOT NULL
ORDER BY NEWID()
--

SET @AUX_ROW_NUMBER = @AUX_ROW_NUMBER + 1

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources