?
Solved

How to SQL Count rows from Join Table?

Posted on 2010-03-22
2
Medium Priority
?
364 Views
Last Modified: 2012-05-09
I have 2 tables left outer joined by ID. One is a header and one is a detail. I want return from the select 2 columns, Column#1 is ID, Column #2 is a dynamic column either displaying the 'name' value from the detail(if only 1 detail row and that row has a name value > '') or 'multi' if there are multiple detail rows(that have a name value > '') or just '' if the name value is null.

-- Header
CREATE TABLE [dbo].[a_header](
      [id] [int] NULL
) ON [PRIMARY]
INSERT INTO a_header VALUES(1)
INSERT INTO a_header VALUES(2)
INSERT INTO a_header VALUES(3)
INSERT INTO a_header VALUES(4)
INSERT INTO a_header VALUES(5)
      

-- Detail
CREATE TABLE [dbo].[a_detail](
      [id] [int] NULL,
      [name] [varchar](50) NULL
) ON [PRIMARY]
INSERT INTO a_detail VALUES(1,'susan')
INSERT INTO a_detail VALUES(2,'john')
INSERT INTO a_detail VALUES(2,'mary')
INSERT INTO a_detail VALUES(2,'mark')
INSERT INTO a_detail VALUES(3,'jim')
INSERT INTO a_detail VALUES(3,'paul')
INSERT INTO a_detail VALUES(3,'kathy')
INSERT INTO a_detail VALUES(4,NULL)

--Select * from ....
--(id),(value)
1,'susan'
2,'multi'
3,'multi'
4,''
5,''
0
Comment
Question by:byteboy11
2 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 28286736
SELECT h.id, CASE COUNT(d.[name]) WHEN 0 THEN NULL WHEN 1 THEN MAX(d.[name] ELSE 'multi' END AS [name]
FROM [dbo].[a_header] h LEFT JOIN
    [dbo].[a_detail] d ON h.id = d.id
GROUP BY h.id
0
 

Author Closing Comment

by:byteboy11
ID: 31705831
thank you
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

593 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