query from 2 tables using count() or a better method

Posted on 2005-03-28
Medium Priority
Last Modified: 2010-04-17
this is my query to the database,

("select a.*, count(b.clientId) AS myCount FROM clients a, cards b WHERE a.storeId=" .$_SESSION['storeId']. " AND a.clientId=b.clientId GROUP BY a.clientId")

it works but if there are no cards from the cards table that match the clientId because of my where statement the client doesnt appear in the client list...  
my goal is to have in a table: [clientId - clientName - clientCompany - numberOfCards(count() from another table)]
do you know what I could do to show all the clients even though they have 0 as numbewr of cards

Question by:jtrudeau
1 Comment

Accepted Solution

WKalata earned 2000 total points
ID: 13645364
What you want to use is a LEFT JOIN,  syntax looks basically like:
SELECT stuff, junk FROM TableA LEFT JOIN TableB ON TableA.somefield = TableB.somefield
What this accomplishes is that every entry from TableA (the one on the left of "LEFT JOIN") is included, and TableB's rows are returned if they happen to match.  If there is no corresponding row for TableB, any fields returned for that row will be null.

Try the following:

("SELECT a.*, count(b.clientId) AS myCount FROM clients a LEFT JOIN cards b ON a.clientId=b.clientId  WHERE a.storeId=" .$_SESSION['storeId']. " GROUP BY a.clientId")

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
No other job is as rewarding and demanding as building an iPhone app is. It is not really in the hands of the developer for the success of an iPhone app. Many factors operate jointly for every iOS application's success in the market.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question