[SOLVED] Trouble with NATURAL JOIN

[mellis95]

I have a query that doesn't work and I have wracked my brain trying to determine why. It produces an empty set even though I have verified that there is matching data in each table. Maybe I have just been staring at the screen too long and it is something simple.

Here is the query:

 

SELECT referral_id, lname, fname, date, location FROM tbl_referral_info NATURAL JOIN tbl_location order by lname;

 

Here are the two tables:

 

tbl_location | CREATE TABLE `tbl_location` (

  `location_id` int(11) NOT NULL auto_increment,

  `location` char(50) default NULL,

  `add1` char(50) default NULL,

  `add2` char(50) default NULL,

  `city` char(50) default NULL,

  `state` char(2) default NULL,

  `zip` varchar(15) default NULL,

  `phone` varchar(15) default NULL,

  `fax` varchar(15) default NULL,

  PRIMARY KEY  (`location_id`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |

 

tbl_referral_info | CREATE TABLE `tbl_referral_info` (

  `referral_id` int(11) NOT NULL auto_increment,

  `physician_id` int(11) default NULL,

  `lname` char(50) default NULL,

  `fname` char(50) default NULL,

  `PT` tinyint(1) default NULL,

  `OT` tinyint(1) default NULL,

  `ST` tinyint(1) default NULL,

  `date` varchar(15) default NULL,

  `add1` char(50) default NULL,

  `add2` char(50) default NULL,

  `city` char(50) default NULL,

  `state` char(2) default NULL,

  `zip` varchar(15) default NULL,

  `phone` varchar(15) default NULL,

  `email` char(50) default NULL,

  `existing` tinyint(1) default NULL,

  `payor1` char(50) default NULL,

  `payor2` char(50) default NULL,

  `age_yr` int(5) default NULL,

  `age_mo` int(2) default NULL,

  `location_id` int(11) default NULL,

  PRIMARY KEY  (`referral_id`)

) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1

 

Here is the proof that there is matching data in each table:

 

mysql> select location_id from tbl_referral_info;

+-------------+

| location_id |

+-------------+

|          1 |

|          1 |

|          1 |

|          1 |

|          3 |

+-------------+

5 rows in set (0.00 sec)

 

mysql> select location_id from tbl_location;

+-------------+

| location_id |

+-------------+

|          1 |

|          2 |

|          3 |

|          4 |

+-------------+

4 rows in set (0.00 sec)

 

[fenway]

Don't know how you came up with "NATURAL JOIN"...

The NATURAL JOIN of two tables is defined to be semantically equivalent to an INNER JOIN with a USING clause that names all columns that exist in both tables.

 

Definitely *NOT* what you want.

 

Try (with table prefixes, please!):

 

SELECT r.referral_id, r.lname, r.fname, r.date, l.location FROM tbl_referral_info AS r INNER JOIN tbl_location AS l USING ( location_id ) order by r.lname;

[mellis95]

Thanks. The inner join is just what I needed.