[SOLVED] 2 tables (I-M) count only one time? thks

[khunjack]

Dears,

 

as longer I'm trying as more I get confused.

 

I have 2 tables

 

table 1 has records with  'account id' unique (only once)

 

table 2 has 'account it' multiple times

 

(one to many relationship)

 

 

I want just counting the recs in table 1 which having one or many records in table 2

 

The wished result is:

count only one when 'account id' exist in table 2 ( even when table 2 has e.g. 10 records with the 'account id' from table 1)

 

Dazed and confused .....

 

Any help/hint is highly appreciated - Thanks a lot in advance

 

 

 

 

 

 

 

 

 

[toplay]

Example 1:

 

# Count of how many table 1 ID's have something in table 2

SELECT

            count(DISTINCT t1.id) AS number_of_rows   

  FROM

            table1 t1

    JOIN

            table2 t2

      ON

            t2.id = t1.id

 

GROUP BY

            t1.id

;

 

 

 

Example 2:

 

# Count of how many table 1 ID's have something in table 2

SELECT

             count(DISTINCT t1.id) AS number_of_rows     

   FROM

             table1 t1

   LEFT JOIN

             table2 t2

      ON

             t2.id = t1.id

WHERE

             t2.id IS NOT NULL

 

GROUP BY

             t1.id

;

 

[cooldude832]

try

<?php
$min_post = "6";
$q = "Select IF(COUNT(Table2.Account_ID) >= '".$min_post."')TABLE1.Account_ID AS Account_Id from `TABLE2`, `TABLE`";
?>

That should select the users account ID if the count of the number of hits is equal to or greater than 6

 

[khunjack]

Thanks a lot toplat and colldude832

 

I tried the first eaxample from toplay as

SELECT 
COUNT(accounts.account_id) AS number_of_rows 
FROM 
kj_accounts accounts 
left JOIN kj_stats_sys_notification sysnotify 
ON 
(accounts.account_id=sysnotify.account_id) 
WHERE 
sysnotify.account_id is not null 

 

Result: 261 (I'm expecting 126)

 

table1 : accounts (158 records)

table2: sysnotify (261)

 

tried cooldude832 suggestion as

 

Select 
IF(COUNT(sysnotify.account_id) >= '6') accounts.account_id AS account_id 
from 
kj_stats_sys_notification sysnotify, kj_accounts accounts

 

that brings an syntax error:

1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') accounts.account_id AS account_id from kj_stats_sys_notification sysnotify, kj' at line 1

 

 

Thanks so far a lot for your help

 

 

[toplay]

I've updated the queries in my previous post to include DISTINCT in the COUNT() (and used "group by" just to make it clear).

[khunjack]

Thanks a lot again ....

 

all make so much sense, when I look at it

...but

Result (for both): 1

 

used:

SELECT 
COUNT(DISTINCT accounts.account_id) AS number_of_rows 
FROM 
kj_accounts accounts 
left JOIN kj_stats_sys_notification sysnotify 
ON (accounts.account_id=sysnotify.account_id) 
WHERE 
sysnotify.account_id is not null 
GROUP BY accounts.account_id 

 

and

 

SELECT 
COUNT(DISTINCT accounts.account_id) AS number_of_rows 
FROM 
kj_accounts accounts JOIN kj_stats_sys_notification sysnotify 
ON 
(accounts.account_id=sysnotify.account_id) 
GROUP BY 
accounts.account_id 

 

..thanks ..thanks for any other ideas ... so much appreciated

[khunjack]

BTW:

I need that count for paging (total record #).

 

to get the actual entries in table 2 (counting how many sysnotification that id got)

 

seems to work

 

SELECT 
COUNT(sysnotify.sender_site) AS mail_cnt, 
accounts.account_id, 
account_screenname
FROM 
kj_accounts accounts 
RIGHT JOIN kj_stats_sys_notification sysnotify
ON 
(accounts.account_id=sysnotify.account_id) 
GROUP BY 
accounts.account_id 
ORDER BY 
mail_cnt desc, created_dt desc 
LIMIT 0, 10

 

I need the count for that,

sure I think the work around would be

 

using above without LIMIT and doing an count($aResult)

 

wouldn't fit into all my other routines .... drives me nuts

[khunjack]

I'm puzzled .... made it very simple

SELECT 
COUNT(DISTINCT accounts.account_id) 
FROM 
kj_accounts accounts JOIN kj_stats_sys_notification sysnotify 
ON 
(accounts.account_id=sysnotify.account_id)

 

and it seems to work fine

 

I tried that already earlier but without the 'DISTINCT '

 

It helped big time pointing that out.

 

Guys was great that you responded so fast to my help request.

 

Thanks a lot.