count(), left join..

[want2php]

Hi, newbie here.

 

I have 3 tables:

 

1. log

  - log_id

  - stat_id

  -times_tamp

2. status

  - stat_id

  - stat_label

3. detail

  - detail_id

  - stat_id

 

 

i would like to count the total number and display all data from log table that matches stat_id from status and detail table.  I was thinking of the left join statement but my newbie coding skills failed.

 

This is what ive done so far.

 

$sql = "SELECT log_id, stat_id, times_stamp FROM log, status, detail
          LEFT JOIN log on (log.stat_id = status.stat_id)
           where log.stat_id = status.stat_id
            and log.stat_id = detail.stat_id

 

and am stuck, don't know how to proceed from here.

 

any help is greatly appreciated. thanks.

[ashton321]

Not Sure what you want but would this help?

 

 $sql = "SELECT * FROM log LEFT JOIN status ON log.stat_id = status.stat_id LEFT JOIN detail ON status.stat_id = detail.stat_id WHERE log.stat_id = status.stat_id AND log.stat_id = detail.stat_id

 

Im not sure if this will help, but I do not really understand the problem if you could elborate more I may be able to help.

[kickstart]

Hi

 

I think you are looking for a count of the log records and a count of the detail records for each stat_id.

 

If so try this:-

 

SELECT a.stat_id, a.stat_label, b.detail_stat_count, c.log_stat_count
FROM status a
LEFT OUTER JOIN (SELECT stat_id, COUNT(*) AS detail_stat_count from detail GROUP BY stat_id) b ON a.stat_id = b.stat_id
LEFT OUTER JOIN ((SELECT stat_id, COUNT(*) AS log_stat_count from log GROUP BY stat_id) c ON a.stat_id = c.stat_id

 

All the best

 

Keith