SQL Problems

[Primogenitor]

Im sure this is but the first of many SQL problems I will have, but I shall plod on regardless. Thanks in advance for any help you can offer!

What is wrong with these statements, and how can I fix it?

[Papillon]

Hmm, maybe try parentheses around the tests, e.g.

(yaxis = '500') and (...) and (...)
_________________
Papillon

[monezz]

I think youre missing a ' after the last 500.

[NoMercy]

SQL could really use some nicer error messages :)

[Xildjian]

You could also try putting brackets around your keys eg [key] =

I had to do this in my scripts. For some reason I have never understood I had to do this when quering a table I created with MS access. I never needed to do this with a table created in game, so I just put [] around all my keys, and things work great. No more syntax errors.
_________________
Member Shadow of Iniquity development team

[Primogenitor]

Papillon wins the first correct answer prize, ( ) around them fixes it nicely, so it ends up looking like:

[monezz]

select A1
from table_a
, table_b
where A2 = B1
and B2 = 4

or

SELECT A1 FROM Table_A WHERE A2 in (SELECT B1 FROM Table_B WHERE B2 = 4)

Both would produce the same result.
The first query is generally faster becase the second one uses an inline view.

The example query would give you error (singe-row subquery returns more then one row in oracle), the in keyword solves that problem.

Gr.Monezz

[Primogenitor]

Thank you, that did it nicely.

Now what about if I want to check more than one row in the second table. For example:

I have two tables, Table_A and Table_B. Table_A is
A1 A2 A3
1 3 2
2 4 2
3 4 4
4 5 3
Table_B is:
B1 B2
2 3
3 4
4 4
5 3

I want all the data in A1 where the B2 entry on the same row as the matching A2 to B1 is equal to 4 and where the B2 entry on the same row as the matching A3 to B1 is equal to 4 I.e. I should get only 3 in my data set.

Can I do:

select A1
from table_a , table_b
where A2 = B1
and A3 = B1
and B2 = 4

[monezz]

That query would produce 1 record.
You will ony get A1 = 3.
If thats what you want thenthe query is correct.

I would rewrite the where clause to:
where A2 = A3
and A2 = B1
and B2 = 4

but thats just a matter of taste. Both work the same.

I would recomend to use aliases for the tables.
It will make you're query easier to read with more functional names

example:

Select pla.id
, pla.ip_address
, cha.name
from players as pla
, characters as cha
where cha.pla_id = pla.id
and cha.class_1 = "Fighter"
and cha.xp <= 3000