Camillia
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?
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
How you want it ...Expected output?
ASKER
Run the sample example I have above. The results of this
to be a column for the result of the second SQL. They have ContactId in common.
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
to be a column for the result of the second SQL. They have ContactId in common.
Hi Camillia,
Please try this..
OUTPUT
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
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)
Hope it helps!
ASKER
I read somewhere I need to use Stuff. Let me try. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes this could help with my other question. I'll try it
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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.
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?
ASKER
Both should be returned. The output should be like what the other ee posted
ID: 41964793
ID: 41964793
The output should be like what the other ee postedIf 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.
ASKER
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.
I thought you're going to offer another another way of doing it. I'll close soon.