Issue with LEFT JOIN query

[arenaninja]

Hello everyone. I'm trying to run a LEFT JOIN query, it looks as follows:

SELECT tblreservations.*, tblusers.last_name, tblusers.first_name,tblusers.email FROM tblreservations LEFT JOIN tblusers ON (tblreservations.fkUserID=tblusers.userid) WHERE `fkEquipID`='4' AND `timeStart` BETWEEN '2012-03-10' AND '2012-03-17 00:00:00' ORDER BY `timeStart`

I've run this a few times and I keep getting NULL values for the fields from tblusers. I'm not an expert with LEFT JOINs, but I've run a few before (just a week ago I made a query that has four of them, and it works properly). The fields are not set up as foreign keys, but tblusers.userid IS a PK. However as I already mentioned, the query with four LEFT JOINs works just fine to date, and I pretty much copy/pasted that previous query to get this to work. Am I missing something here?

[cpd]

I've not gone and tested your query at all but have you considered looking at the BETWEEN part? I notice your searching between a date and a datetime. Not sure how it would effect it but maybe it would?

[AyKay47]

`timeStart` is either a DATE or a DATETIME type field. Which one is it?

Also, make sure that `fkEquipID` and `timeStart` are not ambiguous.

Also, use mysql_error to debug the query, assuming your RDBMS is mysql.

[arenaninja]

Hello,

 

I considered your suggestions, but as far as troubleshooting goes I've already considered issues comparing a date to a timestamp (no issues from several attempts). I also have tried specifying labreservations.[fieldname] on the WHERE clause but it made no difference. The reservations are properly queried, but the corresponding user data doesn't display. For an explicit example, I tried this (at this point I'm debugging from the command line):

SELECT tblreservations.*, tbladmin.last_name, tbladmin.first_name,tbladmin.email FROM tblreservations LEFT JOIN tbladmin ON (tblreservations.fkUserID=tbladmin.userid) WHERE tblreservations.fkEquipID='4' AND tblreservations.timeStart BETWEEN '2012-03-10 00:00:00' AND '2012-03-17 00:00:00' ORDER BY tblreservations.timeStart;

And this is what I'm given:

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

| pkResID | dateReserved        | timeStart          | timeEnd            | pmtID | fkEquipID | fkUserID | comments          | last_name | first_name | email |

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

|      5 | 2012-03-10 01:39:23 | 2012-03-15 05:00:00 | 2012-03-15 05:30:00 |        8 |        4 |      66 | Moar tests      | NULL      | NULL      | NULL  |

|      3 | 2012-03-10 01:35:56 | 2012-03-16 01:00:00 | 2012-03-16 01:30:00 |        2 |        4 |      66 | Testing, too    | NULL      | NULL      | NULL  |

|      2 | 2012-03-10 01:34:12 | 2012-03-16 01:30:00 | 2012-03-16 02:00:00 |        7 |        4 |      66 | testing            | NULL      | NULL      | NULL  |

|      4 | 2012-03-10 01:38:48 | 2012-03-16 17:00:00 | 2012-03-16 17:30:00 |        7 |        4 |      66 | Yet another test | NULL      | NULL      | NULL  |

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

However, I can confirm that this fkUserID value exists in tbladmin.

I also tried removing the WHERE clause for a simple join:

SELECT tblreservations.*, tbladmin.last_name, tbladmin.first_name,tbladmin.email FROM tblreservations LEFT JOIN tbladmin ON (tblreservations.fkUserID=tbladmin.userid) ORDER BY tblreservations.timeStart;

And next I removed the ORDER BY clause for a real barebones approach, but the output is the same, all fields from tbladmin come back as 'NULL'.

 

So, I'm going to go by the sticky and add some more data here.

• MySQL Version: 5.5.21
  
• Code is attached, there are no mysql syntax errors

Create statement for tbladmin:

| tbladmin | CREATE TABLE `tbladmin` (
  `userid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `last_name` varchar(20) NOT NULL,
  `first_name` varchar(20) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `userlevel` tinyint(3) NOT NULL,
  `statusid` tinyint(3) NOT NULL,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`userid`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |

(Some sensitive info edited or omitted)

And for tblreservations:

| tblreservations | CREATE TABLE `tblreservations` (
  `pkResID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `dateReserved` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `timeStart` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `timeEnd` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `pmtID` int(10) unsigned NOT NULL,
  `fkEquipID` int(10) unsigned NOT NULL,
  `fkUserID` int(10) unsigned NOT NULL,
  `comments` blob,
  PRIMARY KEY (`pkResID`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 |

(Again, some sensitive fields edited or omitted)

[arenaninja]

UPDATE: after some google-fu, I opted to try using a subquery:

 SELECT tblreservations.*,tbladmin.first_name,tbladmin.last_name,tbladmin.email FROM tblreservations,tbladmin WHERE tblreservations.fkUserID NOT IN (SELECT tbladmin.userid FROM tbladmin);

And it returns exactly what I expect from the LEFT JOIN query (non-null values for the corresponding first_name and last_name). So now I have two follow up questions:

[*]How do I duplicate this result set using the LEFT JOIN query?

[*]Why am I negating tblreservations.fkUserID? Logically, I should think that I'm looking for the result set that matches the one from the IN clause, so what gives?

[arenaninja]

Sorry for wasting everyone's time. Turns out that I had two tables -- users and tblusers. One was the imported table and the other one was the skeleton of what I was going to modify the original one to. I finally noticed this and the query works like a charm