Link to home
Start Free TrialLog in
Avatar of byteboy11
byteboy11Flag for United States of America

asked on

How to SQL Count rows from Join Table?

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,''
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of byteboy11

ASKER

thank you