Query Help

[ashkumar]

Hi All,
I am new to mySql and I am struggling get one query running. The query is =>

SELECT A.memberno, A.title, A.firstname, A.lastname, A.expiry, A.email, count(*) as count_port FROM member A, portfolio_assessor B WHERE A.memberno = B.memberno and B.status != '3' and A.status = '3' GROUP BY A.memberno ORDER BY count_port, A.memberno

It works fine if the second table "portfolio_assessor" assesor has few corresponding records to first table "member". But I am struggling to get it to show records from first table with count(*) as 0, when there are no corresponding records in 2nd table.
Please help me. I've already lost all my hair over it in last couple if hours. Any help will be really appreciated.
Best Regards,
Ash

[Scott MacVicar]

you query is being too specific you'll need to do a join.

SELECT A.memberno, A.title, A.firstname, A.lastname, A.expiry, A.email, count(*) as count_port FROM member AS A LEFT JOIN portfolio_assessor AS B ON (A.memberno = B.memberno) WHERE B.status != 3 AND A.status = 3 GROUP BY A.memberno ORDER BY count_port, A.memberno

that should work.

[ashkumar]

Hi Scot,
No it doesn't work :(. Here are the structure of both tables ->

CREATE TABLE member (
title varchar(255) default NULL,
firstname varchar(255) default NULL,
lastname varchar(255) default NULL,
memberno int(11) default NULL,
expiry varchar(255) default NULL,
email varchar(255) default NULL,
address1 varchar(255) default NULL,
address2 varchar(255) default NULL,
address3 varchar(255) default NULL,
suburb varchar(255) default NULL,
state varchar(255) default NULL,
country varchar(255) default NULL,
postcode varchar(255) default NULL,
phone varchar(255) default NULL,
fax varchar(255) default NULL,
branch varchar(255) default NULL,
status enum('1','2','3') NOT NULL default '1'
) TYPE=MyISAM;


CREATE TABLE portfolio_assessor (
portfolio_id int(11) unsigned NOT NULL default '0',
memberno int(11) unsigned NOT NULL default '0',
status enum('1','2','3') NOT NULL default '1',
date datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (portfolio_id,memberno)
) TYPE=MyISAM;


It worked fine if there is record for say memberno 123 in "portfolio_assessor" too with status != 3. But when i change the status to 3 or delete the record for corresponding record in "portfolio_assessor", nothing is displayed. If there is no record in the "portfolio_assessor" it should display content from A table with count_port as 0. Any idea how to do it. Waiting for response. Thanks alot for your time.
Thanks and Best Regards,
Ash