[RESOLVED] updated SQL statement returns no records

edwardp

I am writing a forum style log using MySQL as my databse

I have written the following query which worked successfully:

[SQL]
SELECT a.buildnumber, a.entered_by, a.date, b.Description as primdesc, c.Description as secdesc, a.notes, a.resolved as status FROM tbl_fault_log as a, tbl_primary_fault as b, tbl_secondary_fault as c where (a.primary_fault_id = b.id) and (a.secondary_fault_id = c.id) ORDER BY a.id ASC;
[/sql]

the table tbl_fault_log has a field history_id which links to tbl_history where all other history records for each record in tbl_fault_log are kept.

I want to amend the above query to include a count of history records in tbl_history for each record in tbl_fault_log.

I wrote this:

[sql]
SELECT a.buildnumber, a.entered_by, a.date, b.Description as primdesc, c.Description as secdesc, a.notes, a.resolved as status, a.history_id, count(DISTINCT d.id) as historycount FROM tbl_fault_log as a, tbl_primary_fault as b, tbl_secondary_fault as c,tbl_history as d where (a.primary_fault_id = b.id) and (a.secondary_fault_id = c.id) Group by a.buildnumber, a.entered_by, a.date, primdesc, secdesc, a.notes, status, a.history_id ORDER BY a.id ASC;
[/sql]

In my tbl_fault_log i have 2 dummy records setup to test functionality but there are no records as yet in tbl_history, there fore count(distinct d.id) should return 0.

However, with the new query (which doesnt throw any errors, warnings, fatals etc) i dont get any returned data whatsoever and no errors

Can anyone see where i am going wrong??

thanks in advance

edwardp

also...Do i have to include all of the fields in the select section of the statement in the group by???

Roger_Ramjet

You are letting it default to inner joins and since there are no records in 1 table then no records will be returned from any table. If you want to get zero counts then you will have to use left joins explicitly.

It depends on the db engine you are using as to whether you need to name all selected columns in the group by clause. In mysql you do not have to name them all.

edwardp

cheers for that....I have now amended my sql statement to:
[sql]
SELECT a.buildnumber, a.entered_by, a.date, b.Description as primdesc, c.Description as secdesc, a.notes, a.resolved as status, a.history_id, count(DISTINCT d.id) as historycount
FROM tbl_fault_log as a
LEFT JOIN tbl_history d on a.history_id = d.id
INNER JOIN tbl_primary_fault b on a.primary_fault_id=b.id
INNER JOIN tbl_secondary_fault on a.secondary_fault_id=c.id
GROUP BY a.id
ORDER BY a.id;
[/SQL]

It works so far, but wuill have to test it proper when history logs are inserted....

Thanks very much for the pointer

edwardp

hmmmph....now i inserted taht query into my php script i am now getting this error

Fatal error: Call to a member function fetch_array() on a non-object

if i go back to my original query (see first post) it works perfectly fine

Help please!

edwardp

why doesnt php have any error explanationson their site??? what the hell does the above error mean?

How can a simple change in an sql statement cause an error??

edwardp

Please ingonore...i have figured out what was done wrong.....

INNER JOIN tbl_secondary_fault on a.secondary_fault_id=c.id

should be

INNER JOIN tbl_secondary_fault c on a.secondary_fault_id=c.id