I have two tables, one for users and the other one for messages.
User contains: id, name, address, etc etc
Message contains: id, userid, status, etc etc
How to get a table that contains the User.Name and the number (count?) messages received by that user (Message.userid)?
One step forward:
How to get a table that contains the User.Name and the number (count?) messages received by that user (Message.userid) with Message.status = Unread?
@pmonte
in the query given replace "INNER JOIN" with "LEFT OUTER JOIN" this will return the users without messages.
7 2540 @pmonte
select user.id,user.name,count(messages.id)
from user inner join
message on message.userid = user.id
group by user.id,user.name
this query gives you the list for all users. to get the results for a particular user or on a specific condition use where clause or having clause
@deepuv04
thanks a lot! It almost worked as I need. The only point is that:
if a user has no messages this is not included in the resulting table while I'd need also the user with no messages in it (with count = 0).
@pmonte
in the query given replace "INNER JOIN" with "LEFT OUTER JOIN" this will return the users without messages.
@deepuv04
so one or the other, cannot generate a table with both (user with AND without messages)
Basically my goal is to "add a coloum" to user table with the number of messages received by each user
Try this -
select user.id,user.name,
-
count(messages.id) as Messages,
-
COUNT( case when message.Status ='Unread' then 1 else null) as UnreadMessages
-
from user LEFT OTUER JOIN
-
message on message.userid = user.id
-
group by user.id,user.name
-
@deepuv04
as far as I can understand this count the read and unread messages but I'd need this:
User table has two users, id=1 (name= paul) and id=2 (name=john). Message table has three messages all of them with userid=1.
The results should be:
Paul 3
John 0
@deepuv04
left outer join worked exactly as I need, sorry for the confusion and thanks a lot for your help!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Thanos |
last post by:
hello,
I've created a temp table within a stored pprocedure which was updated
and changed. However I need to update an existing table with some
column from the temp table. Below is my update...
|
by: UDBDBA |
last post by:
Hi All:
I have a query which is running against large table. The query:
SELECT DSRC_ACCT_ID, ADDR1, ADDR2, ADDR3, CITY, STATE, POSTAL_CODE,
COUNT(*) FROM ERD.ADDRESSA GROUP BY DSRC_ACCT_ID,...
|
by: Daniel Wetzler |
last post by:
Dear MS SQL Experts,
I have to get the number of datasets within several tables in my MSSQL
2000 SP4 database.
Beyond these tables is one table with about 13 million entries.
If I perform a...
|
by: db2udbgirl |
last post by:
If I perform a select count(*) from tred.order_delivery query will it
internally perform a full table scan to determine the row count for the
following scenario
case 1: There is a primary key on a...
|
by: Martin Joergensen |
last post by:
Hi,
I have some files which has the following content:
0 0 0 0 0 0
0 1 1 1 1 0
0 1 1 1 1 0
0 1 1 1 1 0
0 1 1 1 1 0
0 0 0 0 0 0
|
by: Amy |
last post by:
I need some help.
I have this table with alternate row colors. Class gray and class
white. I have javascript that do highlight when mouseover row ... and
onclick to select row and highlight it...
|
by: Art |
last post by:
Hi,
I'm trying to count rows in each of the tables in an SQL DB. I tried the
following ExecuteScalar command:
"Select Count(*) from " & mTableName
The problem is that one of the tables is...
|
by: Chris |
last post by:
Can't seem to figure out how to do this and have been reading for some
time now......
I want to select a row count from a table name in SYSTABLES.
This statement does not return what I needed,...
|
by: Dejavous |
last post by:
Hi there, I'm a SQL Server 2000 noob and looking for some help.
I need to make a simple script that counts the number of rows in a table.
Also, i need to make another version ofthe same script,...
|
by: KewlToyZ |
last post by:
Good day,
I am stuck in a strange situation. SQL 2000 Server, creating a stored procedure to use in Crystal Reports 11.
I am trying to build a report without creating a table or temprorary table in...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |