QUERY Help

[Canman2005]

Hi all

 

I have a table called `classes` which looks like

 

'id','name'
1,English
2,French

 

I also have a table called `staff` which looks like

 

'id','name','class'
1,Dave,2
2,Sarah,2
3,Brian,1

 

in my URL string I am using

 

staff[]=2&staff[]=1

 

how can I write a QUERY to return a list of all `classes` which staff are attending.

 

In my example (staff[]=2&staff[]=1) it would return just

 

French

 

because in the URL string I am geting "staff ID 1" (staff[]=1) and "staff ID 2" (staff[]=2) which are Dave & Sarah which both attend the "French" class

 

but if for example I passed the URL string

 

staff[]=2&staff[]=5

 

it would return nothing, as there is no staff with ID number 5 (staff[]=5)

 

Any help would be brill

 

thanks

 

ed

[Jax2]

So are you saying your URL will look something like this?

 

http://www.foo.com/report.php?staff=1&staff=2

 

?

 

 

[JAY6390]

Hi Ed.

Use the IN keyword

Take a look here for examples

http://www.webdevelopersnotes.com/tutorials/sql/tutorial_mysql_in_and_between.php3

[Canman2005]

Hi

 

I tried "IN" but it seems to return a result if just one of the ID's passed in the URL exist in the table.

 

How can I make sure it only returns rows which match every ID passed in the URL

 

Thanks guys

[AdRock]

Would help to see what your query look like already

[Canman2005]

Sure, I have changed my db fields a little, but the QUERY I use is

 

SELECT s.name FROM staff s JOIN classes c ON s.id = c.staff WHERE c.class IN (1,3,10) GROUP BY c.staff

 

basically I only want it to return a result if "c.class" equals all the values passed, so it must match ID 1,3,10.

 

At the moment it returns a result if any of ID (1,3,10) exist, I only want it to return a result if all the IDs exist

 

Can anyone help?

 

Thanks very much

[Canman2005]

My new table structure looks like

 

`classes` (`id`, `class`, `staff`)
(1, 0, 0),
(2, 0, 0),
(3, 1, 1),
(4, 2, 1),
(5, 2, 2);

`staff` (`id`, `name`)
(1, 'Dave'),
(2, 'Sarah'),
(3, 'Brian');

[Canman2005]

any ideas?

[Canman2005]

Can anyone help? I cannot work out the corrct QUERY and I have tried so many variations.

 

I'm sure its really simple

 

Thanks

 

Ed

[ignace]

So are you saying your URL will look something like this?

 

http://www.foo.com/report.php?staff=1&staff=2

 

?

 

 

 

If your query looks like this then you'll get 2 as a result and not 1,2 therefor use staff_1=1&staff_2=2

[Canman2005]

But even if I run

 

SELECT s.name FROM staff s JOIN classes c ON s.id = c.staff WHERE c.class IN (1,3,10) GROUP BY c.staff

 

it finds a result, where as it should not find anything, as id 3 and 10 don't exist under the "class" field in the "classes" table.

 

If the QUERY was

 

SELECT s.name FROM staff s JOIN classes c ON s.id = c.staff WHERE c.class IN (1,2) GROUP BY c.staff

 

then it should find something, as id 1 and 2 exist

 

does that kind of make any sense?

 

any help would be great

 

thanks

 

ed

[PFMaBiSmAd]

Are you still trying to find classes that have two staff members in them? An example using your new table structure would help. And do you want only results that have exactly two staff members in a class or is it actually two or more in a class?

 

In general, here is what you would need to do to check if there are x (or more) rows in a group -

 

1) The GROUP BY should actually be on the class value column.

2) You need to do a count(*) to get the number of rows in each group.

3) Use a HAVING clause to only return results that have x (or more) rows in a group.

 

Edit: You would actually only use a WHERE clause if you want to pick a subset of either the classes or the staff to include in the check.

[Canman2005]

Basically what I want to do is, if I pass

 

(1,2)

 

in my QUERY, I want it to only return `staff` values from the `classes` table where it matches every ID passed.

 

So with my table

 

`classes` (`id`, `class`, `staff`)
(3, 1, 1),
(4, 2, 1),
(5, 2, 2);

 

If I ran (1,2) in my QUERY, it would only return `staff` id 1 from the above table, because only `staff` id 1 has `class` values "1" and "2" in the above table.

 

If I ran just (2), then it would return, ID numbers 1 and 2, because both contain `class` values "2"

 

If I ran (1,2,10), then it would return nothing, as non of the staff ID's have a matching "1","2" and "10" IDs

 

I might not be explaining this very well

 

Does it make any sense?

[Canman2005]

any advice anyone?

[AdRock]

I've read this a few times and I am completely lost.

 

What you are trying to do is probably so simple but i don't get what you're saying

 

Try this and tell me what happens

 

SELECT s.name FROM staff s JOIN classes c ON s.id = c.staff WHERE c.staff IN (1,3,10) GROUP BY c.staff

[AdRock]

But even if I run

 

SELECT s.name FROM staff s JOIN classes c ON s.id = c.staff WHERE c.class IN (1,3,10) GROUP BY c.staff

 

it finds a result, where as it should not find anything, as id 3 and 10 don't exist under the "class" field in the "classes" table.

 

Bu the class id 1 does exist so it would return a result (only for that id)

[PFMaBiSmAd]

Actually, I think I understand.  You want to provided an arbitrary list of classes (2 or 1,2 or 1,3,10 ...) and you want the staff that have attended each of the classes in the supplied list?

 

The only generic way I know of and have used for doing this is by using the HAVING clause, but you must supply how many classes to match (i.e. if your list of classes contains two values, you need to supply the number 2 in the HAVING clause.) If you are willing to do that (or you could figure out how to get mysql to do this for you), the following should work -

$num = 2; // produce the number 'classes' for the HAVING clause. The IN() term is a list containing two items, $num is the value 2 

$query = "SELECT s.name, count(*) as cnt FROM staff s JOIN classes c ON s.id = c.staff WHERE c.class IN (1,2) GROUP BY c.staff HAVING cnt = $num";

 

Since php would be dynamically producing the list in the IN() term, it would be a simple matter to produce the correct $num value.

 

I have seen a more complex way of joining a table to itself, once for each item it the list (the example using IN(1,3,10) would consist of a table joined to itself three times) but this requires that you dynamically produce the whole query, rather than just dynamically producing the IN() list and a number for the HAVING clause.

[Canman2005]

Hi

 

I'm sure i'm making sound harder than it is.

 

Let me try and explain a bit better

 

Okay, so I have 2 tables, one is called `staff` and it contains a list of staff (ie: Dave, Sarah).

 

I also have another table called `classes` which contain a list of ID numbers for classes attended (that field is called `class`) and ID numbers of staff runing the class (that field is called `staff`).

 

 

 

So if in my QUERY, I pass ID number 1, I want it to look in the `classes` table, and grab a list of `staff` ID numbers which attend `class` ID number 1 ONLY, so it would get just;

 

 

 

But if in my QUERY, I pass ID numbers 2 and 3, then it would get just;

 

 

 

But if in my QUERY, I pass ID numbers 1, 2 and 3, it would get nothing, as there are no staff that attend all classes 1,2 and 3, so it would get just;

 

 

Does that make it anymore clear for anyone?

 

Thanks so much everyone for your help

 

Ed

[Canman2005]

Thanks everyone, I didn't see a 2nd page of comments 

 

It seems that PFMaBiSmAd suggestion to use

 

$num = 2;
$query = "SELECT s.name, count(*) as cnt FROM staff s JOIN classes c ON s.id = c.staff WHERE c.class IN (1,2) GROUP BY c.staff HAVING cnt = $num";

 

Works fantastic  , i'm not 100% until I run all the tests, but it seems to be working.

 

Thanks to PFMaBiSmAd and everyone else 

[Canman2005]

Just one question, if I pass the `class` ID numbers in my URL string such as

 

class[]=2&class[]=1&class[]=33

 

How can I count the total number of

 

class[]

 

passed in the URL string, so in the above example, it would be "3" in total

 

It's just to that I can calculate the number for

 

$num = 2

 

Or is there a better way to pass those ID numbers in the URL string?

 

Thansk again

 

Ed

[JAY6390]

$num = count($_GET['class']);