byteboy11
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,''
-- 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER