IN Query I think :-) - 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

SQL Server Development (2000)

IN Query I think :-)

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-06-23 : 02:24:59

Hello,

tbl_points (id, soort)
tbl_checkout (id, pijl_1, pijl_2, pijl_3, punten)

Its for a dart game, so lets say I have left 170 points to trow.
The checkout for that is:
single bool (pijl_1=60), single bool (pijl_2= 60), double bool pijl_3 = 62)

The relation between the two tables is that in the table checkout pijl_1 has an id, the id correspont with the id in table points. And soort is the discription (single bool etc).

Nou i want to display is someone has 170 point
single bool - single bool - double bool

But I cant get this to work...

Thanxx
Bjorn

Cya
Bjorn

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-23 : 03:08:08

Ok, I dont play darts, but your question is a little unclear.

I am not quite sure what this means:
single bool (pijl_1=60), single bool (pijl_2= 60), double bool pijl_3 = 62)
You said 170 points, but 60+60+62=182...or maybe I need to get to the nearest bar right away

Could you post some sample data and expected results? That will help us in giving a better answer.

Owais

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-06-23 : 03:41:12

Here Is what I think he wants..

Say he is playing 500.. First person to throw exactly 500 points win.

In his example a player has 170 points on his turn (3 throws)

He has a table of all possible points from a single throw in tbl_points.

Here is this table that I have called Points.. You will need a Numbers or tally table...


CREATE TABLE Points (Point TINYINT NOT NULL PRIMARY KEY)
GO
--Points 0 (If you miss the board that is 0) to 20 plus the outer bullseye (25) and the inner bullseye (50)
INSERT Points
	SELECT Number FROM Numbers N
	WHERE Number BETWEEN 0 AND 20 OR NUMBER IN (25,50)
GO
--Doubles of 1 to 20
INSERT Points
	SELECT  Number * 2 FROM Numbers N
	WHERE Number BETWEEN 1 AND 20
	AND NOT EXISTS (SELECT  1 FROM Points WHERE Point = N.Number * 2)
GO
--Triples of 1 - 20
INSERT Points
	SELECT  Number * 3 FROM Numbers N
	WHERE Number BETWEEN 1 AND 20
	AND NOT EXISTS (SELECT  1 FROM Points WHERE Point = N.Number * 3)
GO

So now we need a way to sum up any combination of points on 3 throws to equal 170...

Enter the CROSS JOIN, each reference of the table represents one throw.


Select X.Point AS Throw1, Y.Point AS Throw2 ,Z.Point AS Throw3
From Points X CROSS JOIN Points Y CROSS JOIN Points Z
WHERE (X.Point + Y.Point + Z.Point) = 170

This lists all possible turns that will give 170...

DavidM

"SQL-3 is an abomination.."

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-06-23 : 06:03:19

Oke sorry for my bad explanation, I had to get the train.


tbl_points
 id
 soort

id    soort
0     1x1
1     1x2
2     1x3
3     1x4
4     1x5
....
57    3x19
....
60    3x20
61    single bool
62    double bool

tbl_checkout
pijl_1
pijl_2
pijl_3
punten

pijl_1   pijl_2    pijl_3   punten
60       60        62       170
60       57        62       167

So if I have 167 point left I have to trow (60, 57, 62) but the 60 stands for 3x20 and the 57 stands for 3x19 and the last one 62 stands for double bool.

So I have a field where the amount of left point I have to trow.
I place this variable in an intPointLeft (lets say this is 167)

I want to get out of the query:
3x20 - 3x19 - double bool

Hope this will help!

Thanxx
Bjorn

Cya
Bjorn

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-23 : 06:16:34

Okay I wont pretend to understand exactly what you mean, but try

SELECT
first.soort, second.soort, third.soort
FROM
tbl_checkout
INNER JOIN
tbl_points first on pijl_1 = first.[id]
INNER JOIN
tbl_points second on pijl_2 = second.[id]
INNER JOIN
tbl_points third on pijl_3 = third.[id]
WHERE
tbl_checkout.punten = 167

I think your tables may benefit from different arrangement, the normalisation seems a little off.

-------
Moo.

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-06-23 : 06:31:53

Thanxx for the help but the query is not working.
I also do not really understand the query and also your comment about:

-------------
I think your tables may benefit from different arrangement, the normalisation seems a little off.
-------------

I use the tbl_punten also for other purpose, stats etc.
I think you mixed up the names in the given query.

Thanxx
Bjorn

Cya
Bjorn

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-23 : 06:34:14

quote:

Thanxx for the help but the query is not working.

Darn. :(

quote:

I also do not really understand the query and also your comment about:

-------------
I think your tables may benefit from different arrangement, the normalisation seems a little off.
-------------

I use the tbl_punten also for other purpose, stats etc.
I think you mixed up the names in the given query.

probably. The reason I mentioned that is because you have apparently repeating data stored in multiple columns of the same table.

-------
Moo.

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-06-23 : 06:40:29

I allready had a solution but it was not a nice one.

 
mySQL = "SELECT pijl_1, pijl_2, pijl_3 FROM tbl_checkout WHERE punten = 167"
Set myRecSet = conn.Execute(mySQL)

x = 0
strLijs = ""
Do while not myRecSet.EOF
 mySQL = "SELECT soort FROM tbl_punten WHERE id = myRecSet(x)
 Set myRecSet2 = conn.Execute(mySQL)
 strLijst = strLijst + myRecSet2(0)
 x = x + 1
 myRecSet.MoveNext
Loop

and in strLijst is now the value I want:
3x20 - 3x19 - double bool

Cya
Bjorn

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-23 : 06:41:10

quote:

Ok, I dont play darts, but your question is a little unclear.

You said 170 points, but 60+60+62=182...or maybe I need to get to the nearest bar right away

Owais

On a darts board you can only get a maximum of 60 points in one single throw (that's a treble twenty).

What the original poster wants is a toolthat would give you the shots you need to finish 167 in three throws.

So with his example of 167, he will need treble 20,

167 - (3*20) = 107

with 107 he can not go for another treble 20 because that will leave him with 47 which is not a double to finish. (In darts you have to finish on the bullseye or double). So what he needs to do is take off the odd number. If he shoots for the treble 19 he will have 50 remaining which will give him a chance to go for bullseye and win.

In other words, he wants a query where by you give a total and it returns to you the best way to finish.

170 = Treble 20 + Treble 20 + Bullseye (Very hard to do!)

Hope this helps.

Good Luck!

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-06-23 : 07:36:03

Amethystium thanxx for the explanation, your completly right.

and the ID numbers in tbl_checkout are not the amount of points.
The id you have to lookup in the tbl_points to see what really you have to trow 3x10 of single bool etc.

Now only the solotion to this problem

is yet not given.

SELECT pijl_1, pijl_2, pijl_3 FROM tbl_checkout WHERE punten = 167
Gives me as result: 60, 57, 62.
SELECT soort FROM tbl_punten WHERE id = 60
Gives me as result: 3x20
SELECT soort FROM tbl_punten WHERE id = 57
Gives me as result: 3x19
SELECT soort FROM tbl_punten WHERE id = 62
Gives me as result: double bool

But now this have to be in ONE query.

Thanxx
Bjorn

---------
Some thing like this but this is not working because the second select query gives more results back
---------


Select X.soort AS Pijl1, Y.soort AS Pijl2, Z.soort AS Pijl3
From tbl_punten X, tbl_punten Y, tbl_punten Z, tbl_checkout A
WHERE X.id IN(
  SELECT pijl_1, pijl_2, pijl_3
  FROM tbl_checkout B
  WHERE B.punten = 170
)

Edited by - neutcomp on 06/23/2003 07:48:45

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-06-23 : 08:05:24

Put the subqueries in the SELECT clause:


SELECT
  (SELECT soort FROM tbl_punten WHERE id = pijl_1) AS Pijl1,
  (SELECT soort FROM tbl_punten WHERE id = pijl_2) AS Pijl2,
  (SELECT soort FROM tbl_punten WHERE id = pijl_3) AS Pijl3
FROM tbl_checkout B
WHERE B.punten = 170

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-06-23 : 08:11:53

Thanxxxxxxxx

You see easy solution to a difficult explanation

Cya
Bjorn

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-06-23 : 08:15:30

But where did tbl_checkout come from in the first place?

neutcomp
Posting Yak Master

111 Posts

Posted - 2003-06-23 : 08:35:22

quote:

But where did tbl_checkout come from in the first place?

In the table checkout there are all possible solution how to finish.
170 = 3x20, 3x20, double bool
etc

Cya
Bjorn

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-23 : 11:31:04

Hey a darts question!

How cool

bool? You mean bull, no? Or have I been getting it wrong all this time...

also, are we talking about 501 and doubling out?

And aren't you looking for the best (fastest) out.

There would be only 1 I imagine.

The only thing I think about when I'm throwing, is beer, dart women, and keeping my darts on the board....(order by may varying depending on the quatity of beer consumed)

Brett

8-)

EDIT: Also the "out" is a matter of preferance..not everyone preferes "trip" 20's...

Edited by - x002548 on 06/23/2003 11:32:13

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-23 : 11:51:29

quote:

Hey a darts question!

How cool

bool? You mean bull, no? Or have I been getting it wrong all this time...

That really confused me as well. I kept reading it as 'booool' even though I knew he meant bull!

What the poster is doing is implementing a similar system they use in darts competition. We know darts players usually do the sums in their heads but they occasionally mess things up. Also, when you are on a finish, you really should go for your best shots. It is no good risking say a double 15 when you could go for a double 16 (which is easier).
Anyway, he wants a system where by you give the number of points to finish and it returns to you the throws needed.

So for 91 points the query would return the following :
19
32
40

but I think he would rather have the result shown as

12
double 16
double 20

As you know, that's not the only way to finish 91 but I have to say the double top is my favourite!

... and beer does make the game seem more fun.

Good Luck!

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-06-23 : 12:02:32

Right, so I suppose what I was trying to establish is whether tbl_checkout contains all 30000 or whatever possible ways of finishing on no more than 3 darts, or just the 'best' way for each desired score.
Because getting from all the possible ways of doing 91 to a 'best' solution is far harder than what we've been answering. For example, if you like double-top, why not 11 double-20, double-20? [

D'oh! What a stupid question!]

Edited by - Arnold Fribble on 06/23/2003 12:05:42

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-23 : 12:27:11

Trip 20's, One, Double 16...

I like to go out on double 16 because theres a natural out all the way down to mad house in case you single on a number...

Brett

8-)

Edited by - x002548 on 06/23/2003 12:29:39

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-23 : 12:30:41

And anyway...since when do the play darts in the Netherlands?

Brett

8-)

Edit:

quote:

single bool (pijl_1=60), single bool (pijl_2= 60), double bool pijl_3 = 62)

A single is 25 point and a double is 50 btw

Oh, and in canada they (BC) I saw a Quadruple ring...

Please leave the game alone....

Edited by - x002548 on 06/23/2003 12:33:29

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-23 : 12:40:33

quote:

And anyway...since when do the play darts in the Netherlands?

OH MY GOD!!!!

They love darts in the Netherlands! I can't remember his name but they even had a world champion not long ago!

Shame on you Brett!

Good Luck!

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-23 : 12:44:18

quote:

Right, so I suppose what I was trying to establish is whether tbl_checkout contains all 30000 or whatever possible ways of finishing on no more than 3 darts, or just the 'best' way for each desired score.

I think you can cut down the number of possibilities because as you know, you can only finish on a double or a bullseye so you could remove all the possibilities which end with an odd number?
You're right though, developing such a tool would never give you the best way to finish because that's ultimately the players choice.

I don't bother.. I just aim for the board and hope for the best!

Good Luck!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources