SQL statement w/ multiple inner joins

converting sql statements to mysql

[discosuperfly]

I have a page where a visitor clicks on a link that takes them to a detail page. The details are displayed based on a key called RegNo.

Here is a glance at the table structure..
tblOne
+++++
RegNo ¦
+++++

tblTwo
++++++++++
MemberNo ¦
++++++++++

tblThree
++++++++++++++++++
MemberNo ¦ RegNo ¦
++++++++++++++++++

I need to pull out everything from tblOne based on the RegNo in the query string. Then I need info from tblTwo based on the same RegNo and info from tblThree based on the MemberNo.

All of this was originally done in Access and I have the SQL statements but they are throwing errors when I try to add them to the web page.

Here's an example...

SELECT tblOne.RegNo, tblOne.OldRegNo, tblOne.Name, tblThree.FirstName, tblThree.MiddleName, tblThree.LastName, tblThree.Street1, tblThree.City1, tblThree.State1, tblThree.Zip1, tblThree.Country1
FROM (tblTwo INNER JOIN tblOne ON tblTwo.RegNo = tblOne.RegNo) INNER JOIN tblThree ON tblTwo.MemberNo = tblThree.MemberNo
WHERE (((tblOne.RegNo)=[Enter Registration Number]))
ORDER BY tblOne.OldRegNo;

I think my move-to-specific-record code takes care of the "Enter Registration Number" call but I can't figure out what I need to do..

When I test this code I get "[TCX][MyODBC]You have an error in your SQL syntax near '[Enter Registration Number]}} ORDER BY tblOne.OldRegNo; limit 100' at line 3"

When I strip out the WHERE statement and the ORDER BY the page just times out.

I tried looking at this [webmasterworld.com ]
but I couldn't figure out the correlation..

thanks for any help,

Troy

[Dreamquick]

[Enter Registration Number] is at least part of the problem...

When you use this in Access it pops a prompt titled "Enter registration number" if I remember correctly. This functionality doesn't exist in mySQL because it operates as a server and not a desktop database engine.

If you want to use the registration number as part of the query then I suspect you'll need to have the user input that data into the website before you run the query.

- Tony

[discosuperfly]

Yes, I agree.. this part is taken care of my search string in the URL.. [blah.com...]

Then the page displays tblOne's info based upon RegNo and gives me tblThree's data as well because of the relation defined in tblTwo between RegNo and MemberNo.

I just tried;

SELECT tblOne.RegNo,  tblWebOne.OldRegNo,  tblWebOne.Name,  tblThree.FirstName,  tblThree.MiddleName,  tblThree.LastName,  tblThree.Street1,  tblThree.City1,  tblThree.State1,  tblThree.Zip1,  tblThree.Country1
FROM tblTwo  INNER JOIN tblOne ON (tblTwo.RegNo = tblOne.RegNo) INNER JOIN tblThree ON (tblTwo.MemberNo = tblThree.MemberNo)

but the page timed out on me..

[webadept]

but the page timed out on me..

uh.. yeah.. probably did.. multi joins like this are hard on the server, and if you can think of a less heavy-handed way of doing it, you should probably take that route. But, one thing you probably don't have is an index on those rows. I'm guessing there, but if you timed out with a small data set, it is the most common cause. Set up indexes on those rows and see if that speeds things up. If it means that you are setting up indexes on every field in those tables however, look for an answer that uses a less heavy handed approach.

[webadept]

SELECT tblOne.RegNo, tblOne.OldRegNo, tblOne.Name, tblThree.FirstName, tblThree.MiddleName, tblThree.LastName, tblThree.Street1, tblThree.City1, tblThree.State1, tblThree.Zip1, tblThree.Country1
FROM (tblTwo INNER JOIN tblOne ON tblTwo.RegNo = tblOne.RegNo) INNER JOIN tblThree ON tblTwo.MemberNo = tblThree.MemberNo
WHERE (((tblOne.RegNo)=[Enter Registration Number]))
ORDER BY tblOne.OldRegNo;

I'm a bit tired here and its probably none of my buiness, but.. why do you have the table2 in there at all? I'm sure there is a resonable explaination for that extra inner join in there, but it strikes me that ... none of your selected fields are from that table.

If table2 is something like a "I've paid my bill" table that you are checking against then it would probably be much faster to make that call first, building an indexed temp table, and then making the the call to get the table3 info using the just created temp table as the join. Making two selects like that and adding the right indexes will speed up your results about 80%. At least that is my experience, your milage may vary.

Just for my own practice and thought I did a little testing here.. useing a large Forum I run using phpBB, and trying to dupicate your query set here with all three tables.

mysql> create temporary table tempUser 
  -> select phpbb_users.user_id, 
  -> phpbb_users.username, phpbb_user_group.group_id 
  -> from phpbb_users, phpbb_user_group
  -> where phpbb_user_group.user_id = phpbb_users.user_id 
  -> and phpbb_user_group.group_id = 2
  -> limit 1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from tempUser;
+---------+----------+----------+
¦ user_id ¦ username ¦ group_id ¦
+---------+----------+----------+
¦    2 ¦ glennh  ¦    2 ¦
+---------+----------+----------+
1 row in set (0.00 sec)

This creates the temp table. This type of table is a system table, an the only reason I have the Drop If Exists line in there is just in case this is the same connection that ran the last query.. if you are using a peristant connection in your web code, then you want that there too. Temp tables like this are only for the user connection that created them.. they disappear when the connection ends and another user calling for the same table name, will not interfer with another user .. look up the temporary tables on the mysql website..

Also, I added in there LIMIT 1. There is only one account number .. right? so, why make the engine continue through the table? It found it, make it stop and do something else. (just adding that made this run so much faster.. )

so, now lets get that table three info that we need, using a single join again

mysql> select tempUser.*, phpbb_groups.group_name, 
  -> phpbb_groups.group_description
  -> from tempUser, phpbb_groups
  -> where tempUser.group_id = phpbb_groups.group_id;
+---------+----------+----------+------------+-------------------+
¦ user_id ¦ username ¦ group_id ¦ group_name ¦ group_description ¦
+---------+----------+----------+------------+-------------------+
¦    2 ¦ glennh  ¦    2 ¦ Admin   ¦ Personal User   ¦
+---------+----------+----------+------------+-------------------+
1 row in set (0.00 sec)

I could, alter the temp table and add an index, if it had more than one row in it, I might have, but as you can see with this explain statement here, it is hardly worth the effort.. if the temp table had say 10 or more records the added speed on the join would probably make up for the creation of the index.. but you'll need to play with that yourself.

mysql> explain select tempUser.*, phpbb_groups.group_name, 
  -> phpbb_groups.group_description
  -> from tempUser, phpbb_groups
  -> where tempUser.group_id = phpbb_groups.group_id;
+--------------+--------+---------------+---------+---------+-------+------+-------+
¦ table    ¦ type  ¦ possible_keys ¦ key   ¦ key_len ¦ ref  ¦ rows ¦ Extra ¦
+--------------+--------+---------------+---------+---------+-------+------+-------+
¦ tempUser   ¦ system ¦ NULL     ¦ NULL  ¦  NULL ¦ NULL ¦  1 ¦    ¦
¦ phpbb_groups ¦ const ¦ PRIMARY    ¦ PRIMARY ¦    3 ¦ const ¦  1 ¦    ¦
+--------------+--------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.03 sec)

But from what I see of your code there, you are only getting a single record.. so using a multi join is very wasteful in processor time, ... but, you could try just adding the LIMIT 1 to the code you have and see if that helps. I'm sure it will.

There are other benifits to doing it this way as well. First off, you can check on the temp table creation, to see if the ID even existed, before you through another join at the system.. Any time I think I need a multi join like that.. I really start to question what the heck I think I'm doing.

webadept -- out