Link to home
Start Free TrialLog in
Avatar of Camillia
CamilliaFlag for United States of America

asked on

Anyway to make these 2 SQL statements into one?

This is SQL 2014

I can combine these 2 sql statements but one returns one row per contactId. The other returns one or more than one row per contactId. So I'll end up with multiple rows per contact Id when the other SQL has more than one row.

I only want one row per contactId but have a column that shows more than one row if there's more than one row from the SQL. Is this doable?

create table #contacts
  (Id int,
   firstname varchar(10),
   lastname varchar(100),
   InstId int
  )
 
insert into #contacts
  select 20830, 'test1','test1_lastname',5419
 
insert into #contacts
  select 21357, 'test2','test2_lastname',5932
 
create table #Ins
(id int,
AudistId int,
distName varchar(10)
)
 
insert into #ins
select 5419,4,'Dist1'
 
insert into #ins
select 5932,3,'Dist2'
 
 
create table #ConGroups
(ContactId int,
GroupId int)
 
insert into #ConGroups
select 20830, 7
 
insert into #ConGroups
select 21357, 7
 
insert into #ConGroups
select 21357, 34
 
 
create table #gro
(Id int,
distId int,
name varchar(100)
)
 
insert into #gro
select  7,4,'group name 1'
 
insert into #gro
select  34,2,'group name 2'
 
--***this gets one or more rows per contact Id. Can this be combined with SQL below....still bring back one row per contact ID but also have a column to show this data...but only one row per contact Id
 
select  g.id gid ,i.AudistId as districtID ,g.name, c.Id as contactId
from #contacts  c
inner join #Ins i on c.InstId = i.Id
inner join #ConGroups cg on cg.ContactId = c.id
inner join #gro g on g.Id = cg.GroupId
 
 --*** this gets one row per contactId 
select
c.Id as Id,
c.firstName,
c.lastname
 
from #Contacts c
inner join #Ins i on i.Id = c.InstId

Open in new window

Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

How you want it ...Expected output?
Avatar of Camillia

ASKER

Run the sample example I have above. The results of  this
select  g.id gid ,i.AudistId as districtID ,g.name, c.Id as contactId
from #contacts  c
inner join #Ins i on c.InstId = i.Id
inner join #ConGroups cg on cg.ContactId = c.id
inner join #gro g on g.Id = cg.GroupId

Open in new window


to be a column for the result of the second SQL. They have ContactId in common.
Hi Camillia,
Please try this..

;WITH CTE AS
(
	select
	c.Id as Id,
	c.firstName,
	c.lastname
	from #Contacts c	
	inner join #Ins i on i.Id = c.InstId
)
,CTE1 AS
(
	select  g.id gid ,i.AudistId as districtID ,g.name, c.Id as contactId
	from #contacts  c
	inner join #Ins i on c.InstId = i.Id
	inner join #ConGroups cg on cg.ContactId = c.id
	inner join #gro g on g.Id = cg.GroupId
)
SELECT a.* , STUFF 
                ((
					SELECT CONCAT(', ' , b.districtID , ', ' , b.gid , ', ' , b.name )
					FROM CTE1 b
					WHERE ( a.Id = b.contactId )
					ORDER BY b.contactId
					FOR XML PATH('') 
					) ,1,2,'') 
                AS UOM
FROM CTE a

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
Id          firstName  lastname                                                                                             UOM
----------- ---------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20830       test1      test1_lastname                                                                                       4, 7, group name 1
21357       test2      test2_lastname                                                                                       3, 7, group name 1, 3, 34, group name 2

(2 row(s) affected)

Open in new window

Hope it helps!
I read somewhere I need to use Stuff. Let me try. Thanks
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India 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
yes this could help with my other question. I'll try it
SOLUTION
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
I only want one row per contactId but have a column that shows more than one row if there's more than one row from the SQL.
Can you give some sample data for the following select?
select  g.id gid ,i.AudistId as districtID ,g.name, c.Id as contactId
from #contacts  c
inner join #Ins i on c.InstId = i.Id
inner join #ConGroups cg on cg.ContactId = c.id
inner join #gro g on g.Id = cg.GroupId

Open in new window

Yes, that bring back 2 rows for contactId 21357. That's because (looking at my first post above), there are 2 rows for it in #ConGroups table.

I'll try the SQ when I get to work today.
there are 2 rows for it in #ConGroups table.
Do you need both to be returned or only one? If only one, which one should be returned?
Both should be returned.  The output should be like what the other ee posted
ID: 41964793
The output should be like what the other ee posted
If your question is already answered then you should close it to avoid new Experts to come and comment in something that has been solved.
If not answered then please let us know what still missing.
Ok. I did post I'm going to try that solution. I'll try and see how it works and close.

I thought you're going to offer another another way of doing it. I'll close soon.