# MySql SELECT statement help please



## Akumos (Nov 18, 2014)

Hi Everyone!

Please bear in mind that I'm quite new to his.

I'm trying to select everything from a table that doesn't exist.. and I'm not sure if it's possible.

I'm making a CPD tacker:
I have a CPD table to store each session.
I also have a staff table.
Then, keep record of what staff attended what course, I have a staff_cpd table which only holds staffid and cpdid IF they attended.

So my statement for everyone who attended works fine:
$sql = "SELECT staffname FROM staff JOIN staff_cpd ON staff.staffid=staff_cpd.staffid WHERE staff_cpd.cpdid=".$_GET["id"];

But I also want to show everyone who didn't attend.

Is this possible?

_*Thanks for taking the time to read and help.*_


----------



## Mindweaver (Nov 18, 2014)

I would do it

Statement for everybody

SELECT a.staffname, b.cpdid
FROM staff a LEFT OUTER JOIN staff_cpd b
ON a.staffid=b.staffid
ORDER BY b.cpdid;


----------



## Akumos (Nov 19, 2014)

Hi - thanks for the reply.

Pasted as is, this returns everyone - but it lists the people that did attend at the bottom.

If I add:

WHERE b.cpdid=1

It returns the people that did attend.

Any ideas?

Thanks again!


----------



## jihadjoe (Nov 19, 2014)

Try fiddling with the order by clause instead of adding another where condition:
ORDER BY b.cpdid DESC


----------



## Akumos (Nov 19, 2014)

Thanks. Just swaps the order, so the people who attended appear at the top but then everyone else is listed below.

I'm hoping to exclude the people that attended from that list.


----------



## caleb (Nov 19, 2014)

WHERE CPDID IS NULL


----------



## Akumos (Nov 19, 2014)

Doesn't return anything..

This is what I tried:

$sql = "SELECT a.staffname, b.cpdid
FROM staff a LEFT OUTER JOIN staff_cpd b
ON a.staffid=b.staffid
WHERE b.cpdid = NULL";

_*Edit:* Ah, I see you need to use IS NULL - let me try! _


----------



## Akumos (Nov 19, 2014)

got it - great! thanks for all the input 

$sql = "SELECT a.staffname, b.cpdid
FROM staff a LEFT OUTER JOIN staff_cpd b
ON a.staffid=b.staffid
WHERE b.cpdid IS NULL";


----------



## caleb (Nov 19, 2014)

Use just LEFT JOIN (I never use those outer/inner stuff) and make sure you tried IS NULL ?


----------



## Mindweaver (Nov 19, 2014)

Try what I have below. You want to use !=1 to see everything but 1

SELECT a.staffname, b.cpdid
FROM staff a LEFT OUTER JOIN staff_cpd b
ON a.staffid=b.staffid
WHERE b.cpdid!=1
ORDER BY b.cpdid;


@caleb - I used left outer because I thought he wanted everything including NULL's.


----------



## Akumos (Nov 19, 2014)

Thanks - but like i said in previous post, all working    using :

$sql = "SELECT a.staffname, b.cpdid
FROM staff a LEFT OUTER JOIN staff_cpd b
ON a.staffid=b.staffid
WHERE b.cpdid IS NULL";

Thanks again!


----------



## caleb (Nov 19, 2014)

Yeah I know what a Left join is, all I was saying is that there is no need for the "OUTER" word as it doesnt do anything and I never used it


----------



## Easy Rhino (Nov 19, 2014)

caleb said:


> Yeah I know what a Left join is, all I was saying is that there is no need for the "OUTER" word as it doesnt do anything and I never used it



What do you mean OUTER does not do anything?


----------



## caleb (Nov 20, 2014)

LEFT JOIN does exactly the same thing as LEFT OUTER JOIN


----------



## Easy Rhino (Nov 20, 2014)

caleb said:


> LEFT JOIN does exactly the same thing as LEFT OUTER JOIN



I know that. You said the word OUTER doesn't do anything when in fact it clarifies that is not a LEFT INNER JOIN. it is good practice to specifcy OUTER or INNER for the sake of readability especially when you are working with a dozen tables and doing mulitple joins on each.


----------



## Akumos (Nov 21, 2014)

Hi - run in to a problem with this SELECT statement so opening this back up 

It works, but when a second CPD session is added, it only shows people that didn't attend the other session. It should show everyone, because no one has attended. Any ideas?

*Thanks for taking the time to read and help!*


----------



## Easy Rhino (Nov 21, 2014)

It is hard to know without the data infront of me.


----------



## Black.Raven (Nov 21, 2014)

You could try something like this: Im pretty sure this is not the solution, but i think I you can do something with it. especially look at the while statement is false

<?php

//Selecting data with an prepared statement 

$link = mysqli_connect("localhost", "root", "yourpassword here", "database here", mysql port here 3307);
// Your query
    $stmt = mysqli_prepare($link, ""SELECT a.staffname, b.cpdid FROM staff a LEFT OUTER JOIN staff_cpd b ON a.staffid=b.staffid WHERE b.cpdid IS ?";");
    mysqli_stmt_bind_param($stmt, "s", $id);
    $id = ($_GET['id']);
    mysqli_stmt_bind_param($stmt, $staffname, $cpdid)
// run the statement
    mysqli_stmt_execute($stmt);

// Check for id who are not database
    while (mysqli_stmt_fetch($stmt) == FALSE) {
        print ($staffname . "With id" . $cpdid . "Has not attended<br>");
       }

?>


----------



## BiggieShady (Nov 25, 2014)

You need to use left join and check for nulls

$sql_who_did_not_attend = "SELECT staffname FROM staff LEFT JOIN staff_cpd ON staff.staffid=staff_cpd.staffid WHERE staff_cpd.staffid IS NULL AND staff_cpd.cpdid=".$_GET["id"];


----------



## Akumos (Nov 27, 2014)

Thanks for your input!

Rave: It just times out, i'm not good enough to manipulate the code to try anything different.

Biggie: It returns no data. 

If someone is willing to jump on my phpmyadmin to take a look, please PM me for the details.


----------



## CounterZeus (Nov 27, 2014)

How about something like this:

$sql = "select st.staffname
from staff st
where st.staffid not in (
select st2.staffid
from staff_cpd st2
where st2.cpdid=".$_GET["id"].")"


----------



## Akumos (Nov 27, 2014)

Thanks CounterZues - this looks promising.. need to test further


----------



## Akumos (Nov 27, 2014)

awesome  working perfectly.. thank you!



CounterZeus said:


> How about something like this:
> 
> $sql = "select st.staffname
> from staff st
> ...


----------



## BiggieShady (Nov 27, 2014)

BiggieShady said:


> You need to use left join and check for nulls
> 
> $sql_who_did_not_attend = "SELECT staffname FROM staff LEFT JOIN staff_cpd ON staff.staffid=staff_cpd.staffid WHERE staff_cpd.staffid IS NULL AND staff_cpd.cpdid=".$_GET["id"];





Akumos said:


> Biggie: It returns no data.



That's because I fucked up


```
$sql_who_did_not_attend = "SELECT staffname FROM staff 
LEFT OUTER JOIN staff_cpd ON staff.staffid=staff_cpd.staffid 
WHERE staff_cpd.staffid IS NULL AND staff_cpd.cpdid=".$_GET["id"];
```

"not in" subquery option by @CounterZeus is also fine, but the faster way is with "not exists"


```
$sql_who_did_not_attend = "select staff.staffname
from staff
where not exists
(select 1
from staff_cpd
where staff_cpd.staffid=staff.stafid
and staff_cpd.cpdid=".$_GET["id"].")";
```

I'm doing all this from my head so I might have fucked up again


----------



## Akumos (Nov 28, 2014)

lol thanks Biggie!

i've added startdate and enddate columns in the staff table so I may come back for more help if I can't figure it out


----------



## Aquinus (Nov 28, 2014)

I would use the LEFT JOIN version that Biggie wrote 2 posts ago. Sub-queries can have performance penalties if there are enough nested loops generated by the query planner. It's been quite some time since I've used MySQL but PostgreSQL will let you add "EXPLAIN ANALYZE" to the beginning of any SELECT query and it will describe how the query planning is going to attack doing the query.

Without seeing anything like that, it looks like that an index on staff_cpd.staffid and an index on staff_cpd.cpdid (if not already a primary key) might speed up the query.

Also next time you ask a SQL question, try to include some schema so we know what we're querying.


----------



## CounterZeus (Nov 28, 2014)

True. For a simple query like this the optimizer would normally see that it's the same subquery over and over again and only execute it once and keep it in memory.
Alas this is not always the case. In Oracle I use the WITH clause for large subqueries and if it gets recalculated for every row, I use the materialize hint to change the explain plan so it would get fetched from the memory. This is of course a bit dirty, but the statistics on the database I work with (+-500GB of raw data) aren't always up to date or completely wrong and sometimes give weird explain plans.


----------

