How to Get A Count From Another Table In SQL?

Question

I have the following tables on SQL:

Mensajes

IdMensaje (int) PK NOT NULL
IdCliente (int)
CorreoCliente (varchar(100))
CorreosAdicionales (varchar(MAX))
Tema (varchar(100))
Mensaje (varchar(MAX))
Fecha (date)
Hora (time(5))

Archivos

IdArchivo (int) PK NOT NULL
IdMensaje (int)
Nombre (varchar(200))

Foreign Key Mensajes.IdMensaje ON Archivos.IdMensaje

If you are wondering what Mensajes.IdCliente is, yes, it has a foreign key with another table but that's another story

First an intro... I'm making a program in which you send an email... When you send an email, I'll insert all data on Mensajes, and if you attached Files on the message, it will also insert on Archivos for each file (obviously Archivos.IdMensaje equals to the Mensajes.IdMensaje which was inserted before that)

So here's my question: I want to make a query where I get All data from Mensajes, but also add another column where it will display how many files were attached to that message... I managed to use a query in which I did get that data (well, sort of)

SELECT Mensajes.IdMensaje, COUNT(Archivos.IdArchivo) AS Expr1
FROM Mensajes INNER JOIN Archivos ON Mensajes.IdMensaje = Archivos.IdMensaje
GROUP BY Mensajes.IdMensaje

However it only displays the messages that had files attached to it, not the ones with a result of 0, I want to display those messages as well... How do I do that?

Hope you can help me Thanks

That'd be a left join instead of an inner join

Zeph
– Zeph

2012-08-13 18:19:09 +00:00
Commented Aug 13, 2012 at 18:19 — Zeph
– Zeph, Commented Aug 13, 2012 at 18:19

LittleBobbyTables - Au Revoir · Accepted Answer · 2012-08-13 18:19:41Z

5

Change your INNER JOIN to a LEFT OUTER JOIN; this will select all records from Mensajes even if they don't have a related record in Archivos.

answered Aug 13, 2012 at 18:19

LittleBobbyTables - Au Revoir

33k25 gold badges113 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to Get A Count From Another Table In SQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related