Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query 1

Status
Not open for further replies.
kzn

kzn

MIS
Jan 28, 2005
209
GB
Hi

I have two tables

Here is the query:
select * from timesheet as ts left join activities as activ on activ.activity_id = ts.activity_id and date(dateTimeStamp) = '2013-08-08' and user_id = 1 order by startTime desc;

The results are below. The problem I have is, I would like the activities part of the query to pull results not null values(I dont understand why it does not pull the correct values). I do need the timesheet part of the query to pull null values hence the reason I am using a left join.

Activities is populated does not have any null values.
Any help appreciated.

[pre]
+--------------+---------+-------------+-----------+------+---------------------+---------------------+----------+---------------------+-------------+-------------------------------------------+------------+-------+------------+
| timesheet_id | user_id | activity_id | account | so | startTime | endTime | comments | dateTimeStamp | activity_id | activity | grouporder | reqso | reqcomment |
+--------------+---------+-------------+-----------+------+---------------------+---------------------+----------+---------------------+-------------+-------------------------------------------+------------+-------+------------+
| 1 | 1 | 1 | Client 1 | 9500 | 2013-08-12 12:30:00 | 2013-08-12 14:00:00 | | 2013-08-08 14:14:00 | 1 | Quotes & Sales orders - Prepare or update | a | y | n |
| 2 | 1 | 1 | Client 2 | 9501 | 2013-08-12 11:24:00 | 2013-08-12 12:00:00 | | 2013-08-08 14:14:00 | 1 | Quotes & Sales orders - Prepare or update | a | y | n |
| 3 | 1 | 13 | Client 3 | 9502 | 2013-08-12 10:00:00 | 2013-08-12 11:00:00 | | 2013-08-08 14:14:00 | 13 | Visuals change requests | b | y | n |
| 14 | 1 | 6 | Client 4 | 9541 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | NULL | 2013-08-08 11:55:32 | 6 | Project communication with suppliers | a | y | n |
| 13 | 1 | 5 | Client 5 | 9541 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | NULL | 2013-08-08 11:33:32 | 5 | Project communication with client | a | y | n |
| 15 | 1 | 6 | Client 6 | 9541 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | NULL | 2013-08-08 12:51:05 | 6 | Project communication with suppliers | a | y | n |
| 32 | 1 | 17 | NULL | NULL | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | NULL | 2013-08-12 14:56:23 | NULL | NULL | NULL | NULL | NULL |
| 31 | 1 | 17 | NULL | NULL | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | NULL | 2013-08-12 14:54:46 | NULL | NULL | NULL | NULL | NULL |
| 30 | 1 | 17 | NULL | NULL | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | NULL | 2013-08-12 14:53:33 | NULL | NULL | NULL | NULL | NULL |
| 29 | 1 | 17 | NULL | NULL | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | NULL | 2013-08-12 14:52:54 | NULL | NULL | NULL | NULL | NULL |
| 28 | 1 | 17 | NULL | NULL | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | NULL | 2013-08-12 14:48:39 | NULL | NULL | NULL | NULL | NULL |
+--------------+---------+-------------+-----------+------+---------------------+---------------------+----------+---------------------+-------------+-------------------------------------------+------------+-------+------------+
[/pre]

CODE
CREATE TABLE `timesheet` (
`timesheet_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`activity_id` int(10) NOT NULL,
`account` varchar(60) DEFAULT NULL,
`so` int(10) DEFAULT NULL,
`startTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`endTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`comments` text,
`dateTimeStamp` datetime NOT NULL,
PRIMARY KEY (`timesheet_id`)
) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=latin1



CREATE TABLE `activities` (
`activity_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`activity` varchar(60) NOT NULL,
`grouporder` varchar(5) DEFAULT NULL,
`reqso` char(1) DEFAULT NULL,
`reqcomment` char(1) DEFAULT 'n',
PRIMARY KEY (`activity_id`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1
 
Sort by date Sort by votes
have you forgotten the where clause?

Code: 
SELECT        ts.*, activ.* 
FROM          timesheet AS ts 
LEFT JOIN     activities AS activ 
ON            activ.activity_id = ts.activity_id 
WHERE         date(dateTimeStamp) = '2013-08-08' 
AND           user_id = 1 
ORDER BY      startTime DESC;
 
Upvote 0 Downvote
Hi Jpadie

Thanks for the reply ... but no that does not work.

I have added an attachment image showing the problem. The first example is what I would like to achieve the second is what I am currently getting with my join. Orange headers show the timesheet table and the blue shows the activities table. The green what I would like to see and the red is what I am getting now. Your query will not return row 2.

Thanks again.
 
 http://s13.postimg.org/4b2ijgv13/Query_problem.png
Upvote 0 Downvote
Hi

Using the following statement nearly gives me the result but I need it to include the date:
SELECT * from timesheet as ts left outer join activities as activ using (activity_id) WHERE user_id = 1 ORDER BY startTime DESC;

Now if I include the date in the statement it fails to give me what I need
SELECT * from timesheet as ts left outer join activities as activ using (activity_id) WHERE date(dateTimeStamp) = '2013-08-08' AND user_id = 1 ORDER BY startTime DESC;

I dont understand because date(dateTimeStamp) has a value equal to 2013-08-08
 
Upvote 0 Downvote
try doing some explicit casting (why is this a php question, by the way?)

Code: 
WHERE        DATE(dateTimeStamp) = CAST('2013-08-08' AS DATE)

 
Upvote 0 Downvote
Hi Jpadie

Sorry its a query I am running in my php code. Its looking like its not possible ... I have spent a day and a half and no luck totally baffled. It must be possible some how :)
 
Upvote 0 Downvote
Hi

I think I have sorted it now, maybe there is an easier way of doing this but this works:

SELECT user_id,account,so,startTime,endTime,dateTimeStamp,activity,grouporder, reqso, reqcomment from timesheet left outer join activities using (activity_id) WHERE user_id = 1 union SELECT user_id,account,so,startTime,endTime,dateTimeStamp,activity,grouporder, reqso, reqcomment from timesheet right outer join activities using (activity_id) WHERE date(dateTimeStamp) = '2013-08-08' and user_id = 1 order by startTime desc;

Just thought I would post this incase someone else has a similar issue :)
 
Upvote 0 Downvote
what you are trying to achieve looks very straightforward. I do not think that a UNION query is the right approach.

could you please provide the data schema and sample data for us to work with? mysql_dump will do this for you.
 
Upvote 0 Downvote
sorry - i have the create data but would still like the sample data in some suitable export format
 
Upvote 0 Downvote
Hi Jpadie

Here is the dump (sample data and create table)

-- Table structure for table ` activities `

DROP TABLE IF EXISTS ` activities `;

CREATE TABLE `activities` (
`activity_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`activity` varchar(60) NOT NULL,
`grouporder` varchar(5) DEFAULT NULL,
`reqso` char(1) DEFAULT NULL,
`reqcomment` char(1) DEFAULT 'n',
PRIMARY KEY (`activity_id`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;

LOCK TABLES `activities` WRITE;
INSERT INTO `activities` VALUES (1,'Quotes & Sales orders - Prepare or update','a','y','n'),(2,'Purchase orders for suppliers','a','y','n'),(3,'File preparation / editing','a','y','n'),(4,'Review Activity / QA','a','y','n'),(5,'Project communication with client','a','y','n'),(6,'Project communication with suppliers','a','y','n'),(7,'Project communication with internal team','a','y','n'),(8,'Status Sheets & Network folders','a','y','n'),(9,'Client invoicing','a','y','n'),(10,'Supplier invoicing','a','n','n'),(11,'Formatting','b','y','n'),(12,'QA','b','y','n'),(13,'Client change requests','b','y','n'),(14,'PDF','b','y','n'),(15,'Development','c','n','y'),(16,'Other development','c','n','y'),(17,'Lunch/Breaks','d','n','n'),(18,'Revenue Report & Outstanding Quotes','d','n','n'),(19,'Supplier recruitment','d','n','y'),(20,'Other communication','d','n','y'),(21,'Internal/External meeting','d','n','y'),(22,'Social Media','e','n','y'),(23,'Training','e','n','y'),(24,'Research','e','n','y'),(25,'Office admin','e','n','y'),(26,'Personal appointments','e','n','y');



-- Table structure for table `timesheet`

DROP TABLE IF EXISTS `timesheet`;
CREATE TABLE `timesheet` (
`timesheet_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`activity_id` int(10) NOT NULL,
`account` varchar(60) DEFAULT NULL,
`so` int(10) DEFAULT NULL,
`startTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`endTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`comments` text,
`dateTimeStamp` datetime NOT NULL,
PRIMARY KEY (`timesheet_id`)
) ENGINE=MyISAM AUTO_INCREMENT=44 DEFAULT CHARSET=latin1;
-- Dumping data for table `timesheet`

LOCK TABLES `timesheet` WRITE;
/*!40000 ALTER TABLE `timesheet` DISABLE KEYS */;
INSERT INTO `timesheet` VALUES (1,1,1,'BT',9500,'2013-08-12 11:30:00','2013-08-12 13:00:00','','2013-08-08 14:14:00'),(2,1,1,'Caltex',9501,'2013-08-12 10:24:00','2013-08-12 11:00:00','','2013-08-08 14:14:00'),(3,1,4,'Pepsi',9502,'2013-08-12 09:00:00','2013-08-12 10:00:00','','2013-08-08 14:14:00'),(35,1,4,NULL,NULL,'0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,'2013-08-13 11:41:01'),(37,2,4,NULL,NULL,'0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,'2013-08-08 14:00:12'),(43,1,23,NULL,NULL,'0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,'2013-08-13 16:59:32');
 
Upvote 0 Downvote
Hi

No, I need it to show the null values for user 1, but in the activity_id, activity,grouporder,reqso,reqcomment ... should have values

Have a look at the image below... I need the top result not the below one

Query_problem.png
 
Upvote 0 Downvote
Hi

in the data set that you send me there are no records in the timesheets table that have a user_id of 1 and a dateTimeStamp on 2013-08-08

[image ]

so I am not sure how I would be able to get the results you want given the dataset provided!

however if I change timesheet 37 to reference user_id 1 then this is the result i get

[image ]

this seems to be what you are looking for, insofar as I can discern the requirements from the image you gave above. the query I used for this was

Code: 
SELECT 		t . * , a . * 
  FROM 		timesheet t
  JOIN 		activities a 
    ON 		t.activity_id = a.activity_id
 WHERE 		DATE( dateTimeStamp ) =  CAST('2013-08-08' AS DATE)
   AND 		t.user_id = 1
 
Upvote 0 Downvote
HI, I have tried your query and it only returns the first three rows not the fourth with timesheet_id = 37 as you have. Your image is exactly what I am trying to achieve. ... I now just wonder if your query works on your set up ... maybe there is something wrong with my mysql version?

+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.69 |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | redhat-linux-gnu |
+-------------------------+---------------------+


 
Upvote 0 Downvote
i have changed timesheet 37 to user_id 1. this is the dataset I am using

Code: 
-- Server version: 5.5.29

INSERT INTO `timesheet` (`timesheet_id`, `user_id`, `activity_id`, `account`, `so`, `startTime`, `endTime`, `comments`, `dateTimeStamp`) VALUES
(1, 1, 1, 'BT', 9500, '2013-08-12 09:30:00', '2013-08-12 11:00:00', '', '2013-08-08 14:14:00'),
(2, 1, 1, 'Caltex', 9501, '2013-08-12 08:24:00', '2013-08-12 09:00:00', '', '2013-08-08 14:14:00'),
(3, 1, 4, 'Pepsi', 9502, '2013-08-12 07:00:00', '2013-08-12 08:00:00', '', '2013-08-08 14:14:00'),
(35, 1, 4, NULL, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, '2013-08-13 11:41:01'),
(37, 1, 4, NULL, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, '2013-08-08 14:00:12'),
(43, 1, 23, NULL, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL, '2013-08-13 16:59:32');

I am using server version 5.5.29 but this part of the mysql core has been stable as long as I can remember.
Protocol version 10

the most likely source of the mismatch is a difference in underlying dataset
 
Upvote 0 Downvote
Hi Jpadie

Thank you so much for your help. Its now working as it should be. I have updated to version 5.6.

Thanks again :)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Rutsagig
  • Locked
  • Question
mysql_insert_id returns 0 after SELECT query
Replies
0
Views
151
Rutsagig
Rutsagig
rogerte
  • Locked
  • Question
SELECT Query works in phpMyAdmin but not in PHP program 1
Replies
4
Views
272
spamjim
spamjim
aa__live
  • Locked
  • Question
php loading modules query 2
2
Replies
39
Views
454
aa__live
aa__live
Katerine
  • Locked
  • Question
Query with bind parameters, exported results to CSV 1
Replies
2
Views
150
Katerine
Katerine
southbeach
  • Locked
  • Question
SQL Query - Conditional SELECT with a twist 1
Replies
4
Views
103
southbeach
southbeach

Part and Inventory Search

Sponsor

Back
Top