Jump to content

Strange MySQL Query Results


mndwn

Recommended Posts

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

Link to comment
Share on other sites

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\" ;

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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\"?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.