Strange MySQL Query Results

[mndwn]

Hello, for some reason when input this query:

 

SELECT routes.routeid, depart.departcode, arrive.arrivecode

FROM routes, depart, arrive

WHERE routes.routeid = \"1\";

 

Instead of getting 1 result (the actual amount) I get (15376 total), thats alot more then there really are in the tables.

Here is some of the weird results I recieve:

 

routeid departcode arrivecode

1 YPAD YPAD

1 YMAY YPAD

1 YBAS YPAD

1 NSFA YPAD

1 YARM YPAD

1 NZAA YPAD

1 YAYE YPAD

1 YBNA YPAD

1 VTBD YPAD

1 YBAR YPAD

1 ZBAA YPAD

1 YBCK YPAD

1 YBTR YPAD

1 KBOS YPAD

1 YBBN YPAD

1 YBRM YPAD

1 SAEZ YPAD

1 YBUD YPAD

1 YWYY YPAD

1 YBCS YPAD

1 YSCB YPAD

1 YBCV YPAD

1 KORD YPAD

1 NZCH YPAD

1 YSCH YPAD

1 YCOM YPAD

1 KDFW YPAD

1 YPDN YPAD

1 WRRR YPAD

1 YDPO YPAD ....

 

Really their should be only one result which is : 1 YPAD YBAS

 

Has anyone encounted this problem when quering many tables and how do you overcome it? Thanx

[shivabharat]

SELECT routes.routeid, depart.departcode, arrive.arrivecode  

FROM routes, depart, arrive  

WHERE routes.routeid = \\\"1\\\";

 

I agree with the result you get because you have just specified the macthing criteria as routeid=\"1\" isnt so?

 

Really their should be only one result which is : 1 YPAD YBAS

 

To get the above result you have to write something like this

 

SELECT routes.routeid, depart.departcode, arrive.arrivecode

FROM routes, depart, arrive

WHERE routes.routeid = \"1\" AND depart.departcode=\"YPAD\" ;

[gizmola]

It\'s actually not strange at all. When you specify 3 tables in a query, and do not relate (ie join) those tables, the result is what\'s called a \"cartesian product\" or in other words, you will get a row for table1*table2*table3.

 

To correct this you simply need to provide the required joins between the tables.

[mndwn]

To get the above result you have to write something like this

 

SELECT routes.routeid, depart.departcode, arrive.arrivecode  

FROM routes, depart, arrive  

WHERE routes.routeid = \\\"1\\\" AND depart.departcode=\\\"YPAD\\\" ;

 

Yes, but I want to allow my users to serach for a route by its id. So how do I join the tables together gizmola? Could you give me an example.

[shivabharat]

Why is that you have many record with the same route ID arent they supposed to be different?

 

Now if you can work on it and make it a unique key (primary) then what you are looking for will become simple.

[mndwn]

The routeid column is the primary key, thats why when i search for \"1\" in the routeid with the other colums it should come up as 1 YPAD YBAS

[shivabharat]

I am bit confused

 

How do you have so many records with a unique ID ??

 

routeid departcode arrivecode  

1 YPAD YPAD  

1 YMAY YPAD  

1 YBAS YPAD  

1 NSFA YPAD  

1 YARM YPAD  

1 NZAA YPAD  

1 YAYE YPAD  

1 YBNA YPAD  

1 VTBD YPAD  

1 YBAR YPAD  

1 ZBAA YPAD  

1 YBCK YPAD  

1 YBTR YPAD  

1 KBOS YPAD  

1 YBBN YPAD  

1 YBRM YPAD  

1 SAEZ YPAD  

1 YBUD YPAD  

1 YWYY YPAD  

1 YBCS YPAD  

1 YSCB YPAD  

1 YBCV YPAD  

1 KORD YPAD  

1 NZCH YPAD  

1 YSCH YPAD  

1 YCOM YPAD  

1 KDFW YPAD  

1 YPDN YPAD  

1 WRRR YPAD  

1 YDPO YPAD

[mndwn]

I agree with you, but Gizoma says:

 

It\'s actually not strange at all.  When you specify 3 tables in a query, and do not relate (ie join) those tables, the result is what\'s called a \\\"cartesian product\\\" or in other words, you will get a row for table1*table2*table3.

 

To correct this you simply need to provide the required joins between the tables.

 

So I have to change the Query, that is the problem, so how can I relate the tables in the Query so the result isn\'t a \"cartesian product\"?

[mndwn]

This is my new query with the JOIN command:

 

SELECT routes.routeid, depart.depart, arrive.arrive FROM arrive, depart, routes INNER JOIN ( depart INNER JOIN routes ON depart.departid = routes.departid ) ON arrive.arriveid = routes.arriveid WHERE ( ( ( routes.routeid ) = 1 ) )

 

Though it comes up as an error, here is what MySQL said:

 

You have an error in your SQL syntax near \'( depart INNER JOIN routes ON depart.departid = routes.departid ) ON arrive.arr\' at line 1

 

Got any ideas on how I could fix the problem with the Query?