Query Help

The_PHP_Newb

Hi guys. I have a query I can't figure out hoping someone can point me in the right direction. I need to pull data from one table using a COUNT limit from another table.

select ID from tableA

But then I need to only produce results where the ID has a count limit from tableB.

Maybe I need a JOIN or something?

I'm trying something like this:

mysql_query("select id, username from tableA where id NOT IN (select count(field) as total from tableB HAVING count(field) <= '10')");

So tableA and tableB have IDs in them. We need a list from tableA but only want the list to include IDs that have less than 10 entries in tableB.

Thanks in advanced for any help you can provide!

bradgrafelman

What is the name of the id column in tableB that corresponds to the id column in tableA? Assuming that they share the same name, here's my guess:

SELECT col1, col2
FROM tableA A
	LEFT JOIN tableB B ON A.id = B.id
GROUP BY B.id
HAVING COUNT(B.id) <= 10

(untested)

The_PHP_Newb

bradgrafelman;10978661 wrote:
What is the name of the id column in tableB that corresponds to the id column in tableA? Assuming that they share the same name, here's my guess:
SELECT col1, col2
FROM tableA A
	LEFT JOIN tableB B ON A.id = B.id
GROUP BY B.id
HAVING COUNT(B.id) <= 10
(untested)

Hi and thanks. They don't share the same name.

tableA is id
tableB is slave

I used this:

mysql_query("SELECT id, username FROM tableA A LEFT JOIN tableB B ON A.id = B.slave GROUP BY B.slave HAVING COUNT(B.slave) <= '10'");

That doesn't produce any results.

dalecosp

The_PHP_Newb;10978668 wrote:
Hi and thanks. They don't share the same name.

tableA is id
tableB is slave

I used this:
mysql_query("SELECT id, username FROM tableA A LEFT JOIN tableB B ON A.id = B.slave GROUP BY B.slave HAVING COUNT(B.slave) <= '10'");
That doesn't produce any results.

Do you ever check for errors?

$result=mysql_query("SELECT id, username FROM tableA A 
LEFT JOIN tableB B ON A.id = B.slave 
GROUP BY B.slave 
HAVING COUNT(B.slave) <= '10'");

if (!$result) { die("Mysql croaked: ".mysql_error()); }

Likely a "name is ambiguous".

Try this maybe:

$result=mysql_query("SELECT A.id, A.username FROM tableA A 
LEFT JOIN tableB B ON A.id = B.slave 
GROUP BY B.slave 
HAVING COUNT(B.slave) <= '10'");

HTH,

The_PHP_Newb

Thank you. I wasn't getting any error output like normal and I've never worked with JOINs before so I assumed the code was good just not producing results.

The code works now but it's not producing the results I'm looking for. There are 75 rows and only 1 row currently should be excluded as it's the only ID with 10 entries in B.slave.

So there should be 74 results instead I get 4 results that have no ryhme/reason to them.

Maybe it has to do with the way I'm assigning the variable?

$user_id = $qry['id'];

The_PHP_Newb

I'm still not getting the expected results. Here's the DB tables.

TableA (field: id)
100
101
102
103
104

TableB (field: slave)
100
100
100
101
100
100
100
100
100
100
102
100

The query I need is to produce all IDs in TableA where ID is not in TableB (slave) 10 times. The query in this example should output:

101
102
103
104

100 should not be listed because it's in TableB (slave) 10 times. Any help is much appreciated.. thank you!

johanafm

Select ids from slave where there are 10 of each id

SELECT slave FROM tableb
GROUP BY slave
HAVING count(slave) = 10

use where clause

SELECT id FROM tablea
WHERE id NOT in (
	SELECT slave FROM tableb
	GROUP BY slave
	HAVING count(slave) = 10
)

And note that the query is fairly inefficient, which means that if you have a lot of data in tableb and/or the query is run often, you may encounter performance issues.