Jump to content

Archived

This topic is now archived and is closed to further replies.

mndwn

Strange MySQL Query Results

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

Share this post


Link to post
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\" ;

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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

Share this post


Link to post
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\"?

Share this post


Link to post
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?

Share this post


Link to post
Share on other sites

×

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.