Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PHP Compare from 2 tables and show data missing

Posted on 2014-02-03
18
Medium Priority
?
240 Views
Last Modified: 2014-02-26
Need help with MYSQL statement that will show me what days of the week George, Tim, etc is not scheduled to work if table 1 has his current schedule and table 2 has the possible days for the schedule. I would then want a list that shows me who is available.

Table 1 has 2 fields

George | Monday
George | Tuesday
Tim | Tuesday
Tim | Thursday
Tim | Friday

Table 2 has 1 field

Monday
Tuesday
Wednesday
Thursday
Friday

Results shows me what days each employee is not scheduled.

George | Wednesday
George | Thursday
George | Friday
Tim| Monday
Tim | Wednesday

Tried:

SELECT DISTINCT employee_id, scheduled_day, week_days
FROM table_2 LEFT JOIN table_1 ON week_days = scheduled_day
WHERE scheduled_day IS NULL

Shows no results. Help please.
0
Comment
Question by:smtpuser2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
  • +1
18 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39829189
what about this
SELECT employee_id, scheduled_day, week_days
FROM table_2 d
JOIN ( select t1.employee_id friom table_1 t1 group by t1.employee_id ) e
  ON 1 = 1
LEFT JOIN table_1 es
   ON es.scheduled_day = d.week_days 
 AND es.employee_id = e.employee_id
WHERE es.scheduled_day IS NULL 

Open in new window

0
 

Author Comment

by:smtpuser2
ID: 39829204
on line 2 what is d after table_2?
on line 3 what is e at the end?
on line 5-8 what is es?

Also, I specified Table_2 only had 1 field and you have 3 in your solution.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39829212
the letters after the table names are aliases:
http://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html

let me update the query code slightly, I omitted the usage of the aliases for the first line:
SELECT e.employee_id, d.week_days  , es.scheduled_day
FROM table_2 d
JOIN ( select t1.employee_id friom table_1 t1 group by t1.employee_id ) e
  ON 1 = 1
LEFT JOIN table_1 es
   ON es.scheduled_day = d.week_days 
 AND es.employee_id = e.employee_id
WHERE es.scheduled_day IS NULL 

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39829242
Is this a homework assignment?  I don't know anyone who has a table to tell us the days of the week -- that's usually already a known fact.  But that aside, I'll see if I can set up a test case for you.
0
 

Author Comment

by:smtpuser2
ID: 39829250
It just times out for me. Not working.
0
 
LVL 1

Expert Comment

by:imakepossible
ID: 39829323
SELECT DISTINCT employee_id, scheduled_day, week_days
FROM table_2 INNER JOIN table_1 ON week_days != scheduled_day
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39829356
>It just times out for me. Not working.

then I presume the table(s) are BIG, and not just those couple of records,

you will need indexes on the employe_id  and on the scheduleddays field(s).

please post the explain plan of the query
0
 
LVL 1

Expert Comment

by:imakepossible
ID: 39829527
use this query

SELECT distinct sch,table1.employee_id,t.week_days FROM (`table2` inner join table1) left join table1 as t on (table1.employee_id=t.employee_id and sch=t.week_days) where ifnull(t.week_days,'')=''

i assumed table like this

CREATE TABLE IF NOT EXISTS `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` varchar(255) NOT NULL,
  `week_days` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `table1`
--

INSERT INTO `table1` (`id`, `employee_id`, `week_days`) VALUES(1, 'George', 'Monday');
INSERT INTO `table1` (`id`, `employee_id`, `week_days`) VALUES(2, 'George', 'Tuesday');
INSERT INTO `table1` (`id`, `employee_id`, `week_days`) VALUES(3, 'Tim', 'Tuesday');

-- --------------------------------------------------------

--
-- Table structure for table `table2`
--

CREATE TABLE IF NOT EXISTS `table2` (
  `sch` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `table2`
--

INSERT INTO `table2` (`sch`) VALUES('Monday');
INSERT INTO `table2` (`sch`) VALUES('Tuesday');
INSERT INTO `table2` (`sch`) VALUES('Wednesday');
INSERT INTO `table2` (`sch`) VALUES('Thursday');
INSERT INTO `table2` (`sch`) VALUES('Friday');
0
 

Author Comment

by:smtpuser2
ID: 39829626
Not working imakepossible. Is the syntax correct? Says there is an error. None seem to work.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39830169
Please see http://www.laprbass.com/RAY_temp_smtpuser.php

This uses a bit of PHP code to effect the output.  I found that easier and faster than trying to figure out how to do it all in a single query.  Just add your credentials near line 38-41 and try it out.

<?php // RAY_temp_smtpuser.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28354857.html


/* PROBLEM DEFINITION
Table 1 has 2 fields

George | Monday
George | Tuesday
Tim | Tuesday
Tim | Thursday
Tim | Friday

Table 2 has 1 field

Monday
Tuesday
Wednesday
Thursday
Friday

Results shows me what days each employee is not scheduled.

George | Wednesday
George | Thursday
George | Friday
Tim | Monday
Tim | Wednesday
*/


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING THE TABLES FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE table_1
( id      INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, manname VARCHAR(24) NOT NULL DEFAULT ''
, weekday VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// CREATING THE TABLES FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE table_2
( id      INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, thedays VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// LOADING OUR DATA INTO THE TABLE
$mysqli->query("INSERT INTO table_1 (manname, weekday) VALUES ('George', 'Monday')");
$mysqli->query("INSERT INTO table_1 (manname, weekday) VALUES ('George', 'Tuesday')");
$mysqli->query("INSERT INTO table_1 (manname, weekday) VALUES ('Tim',    'Tuesday')");
$mysqli->query("INSERT INTO table_1 (manname, weekday) VALUES ('Tim',    'Thursday')");
$mysqli->query("INSERT INTO table_1 (manname, weekday) VALUES ('Tim',    'Friday')");

$mysqli->query("INSERT INTO table_2 (thedays) VALUES ('Monday')");
$mysqli->query("INSERT INTO table_2 (thedays) VALUES ('Tuesday')");
$mysqli->query("INSERT INTO table_2 (thedays) VALUES ('Wednesday')");
$mysqli->query("INSERT INTO table_2 (thedays) VALUES ('Thursday')");
$mysqli->query("INSERT INTO table_2 (thedays) VALUES ('Friday')");

/* CHECK THE TEST DATA IS OK?  YES.
$res = $mysqli->query("SELECT * FROM table_1");
while ($row = $res->fetch_object()) print_r($row);
$res = $mysqli->query("SELECT * FROM table_2");
while ($row = $res->fetch_object()) print_r($row);
*/

// GET AN ARRAY OF THE DAYS IN BOTH KEYS AND VALUES
$sql = "SELECT thedays FROM table_2";
$res = $mysqli->query($sql);
while ($row = $res->fetch_object()) $days[$row->thedays] = $row->thedays;

// GET AN ARRAY OF THE PEOPLE
$sql = "SELECT manname, weekday FROM table_1 ORDER BY manname";
$res = $mysqli->query($sql);
while ($row = $res->fetch_object()) $mans[] = $row;

// COMBINE THE DATA SETS TO GET THE DAYS EACH MAN IS AVAILABLE
$oldname = FALSE;
$my_days = array();
foreach ($mans as $row)
{
    // START A NEW DATA SET FOR EACH NEW NAME
    if ($row->manname != $oldname)
    {
        // SAVE THE DAYS THAT ARE LEFT
        $out[$oldname] = $my_days;

        // START WITH A NEW NAME AND A NEW SET OF WEEKDAYS
        $oldname = $row->manname;
        $my_days = $days;
    }

    // REMOVE THIS DAY FROM CONSIDERATION
    unset($my_days[$row->weekday]);
}
$out[$oldname] = $my_days;
unset($out[0]);

// SHOW THE DATA
print_r($out);

Open in new window

0
 

Author Comment

by:smtpuser2
ID: 39831268
I would love to accept Ray's solution but alas it is way too complicated. Looking for a MySQL query statement. This has to be a common problem.
0
 
LVL 1

Expert Comment

by:imakepossible
ID: 39831441
SELECT distinct sch,table1.employee_id,t.week_days FROM (`table2` inner join table1) left join table1 as t on (table1.employee_id=t.employee_id and sch=t.week_days) where ifnull(t.week_days,'')=''


table1  having field
employee_id,week_days


table2 having field
sch


After creating your query with this
can you give your query, i think you are missing something

it is working as i tested at my end
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39832025
...but alas it is way too complicated
Really!  Too complicated for who?  I don't see any other solutions that work :-)

Actually, in the world of computer programming tasks, it's just drop-dead simple CS101 stuff.  The code has comments explaining what it does.  It was the first working solution.  The majority of what's in the code snippet is just there to set up the test data, which is the most important thing a programmer can have.  The only way to make it harder is to try to do it all with one query.  So the question becomes, "do you want immediate results or do you want to spend your time in a struggle with a problem?"

Sometimes you may find that people will post code here at EE without testing it, or they will tell you they tested it but will not be able to show you the test case.  Since I do this stuff for a living and teach it at the university level, I find that testing is a necessary part of the process so I do it.  It is the only way you can hope to know if the code works!  In exchange for tested code samples you must provide the EE community with the SSCCE and a well-representative data set so that we can test.

I did what I could with the data provided, and the output is what you asked for.

Best of luck with your project, ~Ray
0
 

Accepted Solution

by:
smtpuser2 earned 0 total points
ID: 39840055
Sorry Ray. I really did not intend to dis your effort. I appreciate that you took the time to thoroughly explain your solution. You actually earned a gold star for your effort. As a beginning programmer, I had not used a similar technique and it looked foreign to me. Again thanks.

I was able to find another solution through a different website. Not sure if it is efficient because it seems to not load quickly. I have enclosed it below in case I should be pointed to a more efficient solution.

SELECT *
FROM (SELECT id, week_days FROM table_1, table_2
GROUP BY id,week_days) as tab  WHERE row(id,week_days) NOT IN(select id,scheduled_day from table_1)
ORDER BY id, week_days

Many thanks to all the contributors.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39840152
first, another good tip: use aliases in your queries:
http://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html

other than that, I claim my SQL is correct approach, but you did not yet come back with the answers to my question ...
0
 

Author Comment

by:smtpuser2
ID: 39840505
No homework. Creating a solution for my job.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39844191
Sigh...

Here are the sorts of things that make it hard to help (both quotes from the original question)
Table 2 has 1 field
SELECT DISTINCT employee_id, scheduled_day, week_days FROM table_2

When I tried to test the query posted here, this was the result:
Fatal error:  QUERY FAILURE: ERRNO: 1137 ERROR: Can't reopen table: 'table_1'
0
 

Author Closing Comment

by:smtpuser2
ID: 39888276
I posted the question as a usable MYSQL statement.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question