Re-construct 4 tables into 1 table and sort Fraction value [Question]

Question

Post reply

Re-construct 4 tables into 1 table and sort Fraction value [Question]

newtechhk

Newbie

Points: 1
More actions
January 3, 2012 at 6:57 am

#265713

Bet Table:
BetId, int
BetName, nvarchar
BettypeId, int
Bettype Table:
BettypeId, int
BettypeName, nvarchar
BookMaker Table:
BookMakerId, int
BookMakerName, nvarchar
SortOrder, int
Odds Table:
OddsId, int
BookMakerId, int
BettypeId, int
BetId, int
MatchId, int
FixtureId, int
OddsValue, nvarchar (e.g. 31/11, it is a fraction)
SQL:
SELECT COUNT(BetId) FROM [Bet] where BettypeId = 1
It can count how many Bet under a Bet type.
Result: 3
SELECT * FROM [odds] where MatchId=116 and BettypeId = 1 and
BetId in (SELECT BetId FROM [Bet] where BettypeId = 1)
order by BookMakerid, BetId
Result:
235511111611611
23331121161169/2
23111131161161/4
235621111611611
23342121161169/2
23122131161161/4
23573111161169
23353121161169/2
23133131161162/7
23584111161168
23364121161169/2
23144131161161/4
23595111161169
23375121161169/2
23155131161162/7
23606111161169
233861211611619/4
23166131161161/4
Target output:
BookMakerId=1, BookMakerName, BetId=1, BetName, OddsValue, BetId=2, BetName, OddsValue, BetId=3, BetName, OddsValue
BookMakerId=2, BookMakerName, BetId=1, BetName, OddsValue, BetId=2, BetName, OddsValue, BetId=3, BetName, OddsValue
BookMakerId=3, BookMakerName, BetId=1, BetName, OddsValue, BetId=2, BetName, OddsValue, BetId=3, BetName, OddsValue
BookMakerId=4, BookMakerName, BetId=1, BetName, OddsValue, BetId=2, BetName, OddsValue, BetId=3, BetName, OddsValue
BookMakerId=5, BookMakerName, BetId=1, BetName, OddsValue, BetId=2, BetName, OddsValue, BetId=3, BetName, OddsValue
BookMakerId=6, BookMakerName, BetId=1, BetName, OddsValue, BetId=2, BetName, OddsValue, BetId=3, BetName, OddsValue
If the number of BetId changes, the columns (a set of BetId, BetName, OddsValue) will be adjusted.
Sort by OddsValue for 3 Bet of 3 bookmakers:
BetId=1, BetName, OddsValue (Highest value put in 1st row)
BetId=2, BetName, OddsValue (Highest value put in 2nd row)
BetId=3, BetName, OddsValue (Highest value put in 3rd row)
How is the SQL?

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11420 More actions · Answer 1

can you provide some table layouts, sample data and expected output? What have you tried so far?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/