[Resolved] Complicated Query Help

Namadoor

Hello, I am making a map program for a little online game I am creating. The maps are made up of 'squares'. The square values are stored accross two tables, letal, and data. The table letal contains basic stuff like the square name. The data table contains the square's x and y cordinate. This is an example:

TABLE: letal
letalid | letalname
27 | example

As you can see, the x and y cordiantes are stored in seperate rows. This is my question: How do you make a query so that it select the letalid, letalname, x-coordinate (datavalue), and y-coordinate (also datavalue) so that all of them are in ONE row in the result. Also, how would I go about sorting by the x and y coordinates?

I am sorry if it is unclear what I am trying to do, I don't really know how to explain this.

Thanks for any help.

Roger_Ramjet

First thing you do is to merge the tables. Unless they contain something other than you have shown, there is not need for seperate tables. Each square has an x & y co-ordinate, and a name. X & Y are equally attributes of the Square as the Name etc.

Table design is this:
table = Squares
id = auto-increment integer
name = 'letal' name = char
x = integer
y = integer

Any queries can return all the squares on an X row, Y row, with a certain Name component, etc.

Roger_Ramjet

You don't even need the auto-increment id column. X & Y together already provide an unique key for each square. Unless you database engine is steam-driven it should support multi-column Primary Keys.

Namadoor

Thanks for your help, but I do want the tables laid out as they are. I know it looks like there is no point to the way I have done it, but there is if you knew how my system worked. Is it even possible for me to do what I want with the way the tables are provided?

Roger_Ramjet

Of course. How depends very much on the databse you are using , and the version.

Namadoor

Really? I would have to make it specific to a version of MySQL, not just version 3.x and later of MySQL?

Roger_Ramjet

First pass

2 queries will start you off. Desined for use as sub-queries.

view all x corordinates
SELECT letalid, letalname, datavalue AS x FROM letal, data USING (letalid) WHERE data.dataname = 'x-coordinate'

view all y coordinates
SELECT letalid, letalname, datavalue AS y FROM letal, data USING (letalid) WHERE data.dataname = 'y-coordinate'

You can use these as input to other queries either as subqueries or by using them to make temporary tables.

Roger_Ramjet

Yes. Ver 4 up supports sub-queries/nested queries. Earlier versions do not.

Roger_Ramjet

This, in theory, will work. It should return

letalid, letalname, x, y for all squares.
Add WHERE and SORT BY clauses as required

SELECT letalid,letalname, x, y
FROM letal,
(SELECT letalid, datavalue as x FROM data WHERE dataname='x-coordinate') as Xs,
(SELECT letalid, datavalue as y FROM data WHERE dataname='y-coordinate') as Ys
USING (letalid)
WHERE
SORT BY

tomhath

Come on guys, this is just a simple join:

select l.letalid, l.letalname, 
       dx.datavalue as xcoordinate,
       dy.datavalue as ycoordinate
from letal l
inner join data dx
on   l.letalid = dx.letalid
inner join data dy
on   l.letalid = dy.letalid
where dx.dataname = 'x-coordinate'
  and dy.dataname = 'y-coordinate'

Roger_Ramjet

Yep, but I'm sitting here coding Jet SQL and forget that mysql makes this kind of thing easy. I tend to Select from predefined Views and use atomic update functions for user input.

Namadoor

Thanks you both for all your help.