[RESOLVED] Help with SQL

vertmonkee

I have one table that stores a complete list of obstacles for a location, called spot_obstacles.

In this table I have location_id, obstacle_id, obstacle_size_id and obstacle_number.

I have another table that stores descriptions of the obstacles, called obstacles.

In this table I have obstacle_id and obstacle_description.

My other table stores details about the sizes of obstacles, called obstacle_size.

In this table I have obstacle_size_id and obstacle_size_description.

When a user views details about the an individual spot a query is run on the server that selects all the obstacles from spot_obstacles and then displays them to the user.

So far I have this sql

SELECT * FROM spot_obstacles
LEFT JOIN obstacles
ON spot_obstacles.obstacle_id = obstacles.obstacle_id
WHERE location_id = $spot_id

This gives me for example, 2 stair Handrail.

When what I would like it to say is Medium 7 stair Handrail.

The 1 in the first statement corresponds to small in my obstacle_size table.

But if I try to join the obstacle_size table on it prints out every option to the screen.

Hopefully I have made this clear enough for someone to be able to offer some advice.

See http://ttphp.open.ac.uk/~idl27/skatespots/spot_individual.php?spot_id=1
for an example of what I am doing.

Thanks very much.

napper

Try this:

SELECT 
sp.*,
ob.obstacle_description, 
sz.obstacle_size_description
FROM 
  spot_obstacles sp
LEFT OUTER JOIN obstacles ob ON sp.obstacleid=op.obstacleid
LEFT OUTER JOIN obstacle_size sz ON sp.obstacle_size_id=sz.obstacle_size_id

I didnt test it but look at it and see if it works.

napper

I just created 3 simple tables in my db to test this for you. The following SQL statement worked for me. I added about 10 rows to my size table and a few rows to the other. This is taking it from the obstacle table and not the spot table like the one above is (the way I would do it since ideally it would be the key table).

SELECT     sp.*, ob.obstacle_description, sz.obstacle_size_description
FROM         obstacles ob
LEFT OUTER JOIN  spot_obstacles sp ON ob.obstacle_id = sp.obstacle_id
LEFT OUTER JOIN  obstacle_size sz ON sp.obstacle_size_id = sz.obstacle_size_id

vertmonkee

Excellent thanks very much for that.

It is all solved now.

I also added on

WHERE location_id = $spot_id

my $spot_id is the individual spot number the user is currently viewing.

Thanks again.

napper

Just remember to build indexes on the fields that you are doing the inner joins on. I would assume you have a Primary Key setup for the obstacle_size_id in the obstacle_size and a PK for obstacle_id in the obstacle table, which would be the only index required for those tables if you only had the 2 other fields you mentioned. I like to have indexes on every field that I do inner joins on to avoid having to do tabls scans.

If this is MySQL, simple put the word EXPLAIN before the SQL Statement for MySQL to tell you if it is doing a table scan or a index lookup. For MSSQL, use Query Analyzer and show the execution plan. Look at the high cost items for items which are taking the longest. If any are red, right click and have MSSQL generate the missing statistics.