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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Simple Linear Regression
Starting up a Project
Suggested Courses

801 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