Jump to content


Photo

Strange MySQL Query Results


  • Please log in to reply
8 replies to this topic

#1 mndwn

mndwn
  • Members
  • Pip
  • Newbie
  • 8 posts
  • LocationNSW, Australia

Posted 10 July 2003 - 04:19 AM

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
<?php
function cheers($message) {
if (!isset($message)) {
$message = "Cheers!";
} echo ("$message"); exit(); }  cheers(); ?>
m5Central.net <Developing PHP-Based Solutions for the Future>

#2 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 10 July 2003 - 06:31 AM

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\" ;
Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#3 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,664 posts
  • LocationLos Angeles, CA USA

Posted 10 July 2003 - 07:26 AM

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.

#4 mndwn

mndwn
  • Members
  • Pip
  • Newbie
  • 8 posts
  • LocationNSW, Australia

Posted 10 July 2003 - 12:20 PM

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.
<?php
function cheers($message) {
if (!isset($message)) {
$message = "Cheers!";
} echo ("$message"); exit(); }  cheers(); ?>
m5Central.net <Developing PHP-Based Solutions for the Future>

#5 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 10 July 2003 - 12:23 PM

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.
Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#6 mndwn

mndwn
  • Members
  • Pip
  • Newbie
  • 8 posts
  • LocationNSW, Australia

Posted 10 July 2003 - 12:32 PM

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
<?php
function cheers($message) {
if (!isset($message)) {
$message = "Cheers!";
} echo ("$message"); exit(); }  cheers(); ?>
m5Central.net <Developing PHP-Based Solutions for the Future>

#7 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 10 July 2003 - 12:41 PM

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


Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#8 mndwn

mndwn
  • Members
  • Pip
  • Newbie
  • 8 posts
  • LocationNSW, Australia

Posted 10 July 2003 - 12:46 PM

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\"?
<?php
function cheers($message) {
if (!isset($message)) {
$message = "Cheers!";
} echo ("$message"); exit(); }  cheers(); ?>
m5Central.net <Developing PHP-Based Solutions for the Future>

#9 mndwn

mndwn
  • Members
  • Pip
  • Newbie
  • 8 posts
  • LocationNSW, Australia

Posted 10 July 2003 - 01:05 PM

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?
<?php
function cheers($message) {
if (!isset($message)) {
$message = "Cheers!";
} echo ("$message"); exit(); }  cheers(); ?>
m5Central.net <Developing PHP-Based Solutions for the Future>




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users