distinct records from joined tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

distinct records from joined tables

Author

Topic

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-08-03 : 04:15:14

Hi,
I have the following three tables

Persons(
pers_id int identity,
pers_name varchar,
pers_address varchar,
...)

Groups(
grp_id int identity,
grp_name varchar)

GroupsToPersons(
gTp_id int identity,
gTp_persID int,
gTp_grpID int,
gTp_isActive tinyint)

One person can be member of 0,1 or more groups. The assosiations are help in GroupsToPersons and there are always (for each person) that many records in GroupsToTalbes as groups in Groups (gTp_isActive is 1 if the person belongs to the group or 0 if it doesn't belong to it)

I want to write a query that will return one single record for each person followed by one (and only one ,doesn't matter which) group that it belongs to. If the person doesn't belong to a group, an empty string can be returned.
Is it possible?

ps: I am trying to avoid writing a stored procedure.
thx

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-03 : 05:09:10

is this what you want? somehow i doubt it's that simple...
select pers_id, ...
from Persons P inner join GroupsToPersons GTP on (P.PersId = GTP.grp_ID)
where gtp_isActive = 1
union all
select max(gTp_persID), ...
from Persons P inner join GroupsToPersons GTP on (P.PersId = GTP.grp_ID)
where gtp_isActive = 0
group by gTp_persID, ...

why not use a stored procedure? it's a good thing... :))

Go with the flow & have fun! Else fight the flow :)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-03 : 05:11:14

Hi
This should do the trick:

SELECT
p.pers_id,
p.pers_name,
p.pers_address,
g.grp_id,
g.grp_name
FROM
Persons AS p
LEFT JOIN
(
SELECT
gtp.gTp_persID AS pers_id,
MIN(gtp.gTp_grpID) AS grp_ID
FROM
GroupsToPersons AS gtp
GROUP BY
gtp.gTp_persID
) AS gtp
JOIN Groups AS g
ON gtp.grp_ID = g.grp_id
ON p.pers_id = gtp.pers_ID

The derived table in the middle returns a single row for each group to person association via a group by and aggregate function.
The left join to this derived table ensures that you return people who don't belong to any groups as well as those who do.
I'd suggest using consistent naming for your key columns (i.e. change gTp_persID to pers_id, etc.) It'll save you a lot of headaches in the long run!
Also, why don't you want to write a stored proc?

Mark

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-03 : 05:12:34

You just slipped in there spirit1 while I was still typing mine up!

Mark

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-03 : 05:26:06

:))) now we must wait and see if that's what he acctualy wants... :)))

Go with the flow & have fun! Else fight the flow :)

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-08-03 : 06:03:33

thx both for your replies
the one from mwjdavidson is more close to what i want. Let me explain : let's suppose that table persons have three records (eg: dev45 , mwjdavisdson and spirit 1) , groups have 2 records (eg : administrators, developers) and mwjdavidson is both a developer and administrator, spirit1 is just developer and dev45 is none; then i would like to have the following result
------------------
spirit 1 , developer
mwjdavidson , developer (or administrator, anyone would be fine)
dev45 , (empty string)

the query by mwjdavidson almost does the trick. The only problem is that it will return an arbitrary group for the persons that do not belong to any group ( so in the previous example dev45 could be a developer or an administrator)

about the stored proc.... i would also opt for it but the resultset is returned to a vb datagrid placed in an inherited form.
the base form has 'built in' capabilities for filling the grid if we pass a select statement to it ... to use a stored proc i have to enhance it .... hopefully in the near future;)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-03 : 07:52:51

Hi
I'm a bit confused... In my solution, anyone not belonging to any group will return NULL (which could equally be empty string via an ISNULL) in any columns from the Groups table as there will be no row returned on the left side of the join. How would it return an arbitrary group?
I've just tested it with your data and it seems to do exactly what you want.

Mark

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-03 : 07:54:43

sorry - a little correction to my previous post. I meant that there would be no row returned on the RIGHT side of the join!

Mark

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-08-03 : 08:18:36

In GroupsToPersons table there is also a column gTp_isActive. The table holds a record for every combination of persons and groups an column gTp_isActive declares whether a person belongs to a group : if for example dev45 (pers_id = 3) is an administrator (grp_Id = 2) (and only that) there are two rows in GroupsToPersons
======
gTp_persId , gTp_grpID, gTp_isActive
--------------------------------------
3 , 1, 0 (not a member,thus=0)
3 , 2, 1 (a member,thus=1)

if, dev45 does not belong to any group, there will still be 2 records in GroupsToPersons
=======
gTp_persId , gTp_grpID, gTp_isActive
--------------------------------------
3 , 1, 0 (not a member,thus=0)
3 , 2, 0 (not a member,thus=0)

So, since is active is not taken into account, the query always returns a group_id...

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-08-03 : 08:21:12

ok, i think i found the missing part

SELECT
p.pers_id,
p.pers_name,
p.pers_address
g.grp_id,
g.grp_name
FROM
Persons AS p
LEFT JOIN
(
SELECT
gtp.gTp_persID AS pers_id,
MIN(gtp.gTp_grpID) AS grp_ID
FROM
GroupsToPersons AS gtp
where gtp.gtp_isactive = 1 <-----------------
GROUP BY
gtp.gTp_persID
) AS gtp
JOIN Groups AS g
ON gtp.grp_ID = g.grp_id
ON p.pers_id = gtp.pers_ID

it seems to be working fine now,
thx a lot mark:)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-03 : 08:43:46

No worries Dev. Sorry for the omission - gotta learn to read and reread before posting, but you've gotta get in there quick on this forum!!

Mark

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-03 : 08:55:30

just a thought though... why do you have persons that are not in groups in there?
why is gtp.gtp_isactive in there? wouldn't it be simpler that those that are not in the group aren't in the table?

Go with the flow & have fun! Else fight the flow :)

dev45
Yak Posting Veteran

54 Posts

Posted - 2004-08-03 : 09:19:04

good question spirit1 :)
when i started developing a few base forms (on vb.net) so that the rest of the team could use them (by inheritence) i faced the following problem :
if person A was in group A, and group B (etc) everything went smoothly. If ,after making assosiations between persons and groups, i added another group (table : groups) ... this one was not visible on the form where assosiation where taking place (since there was no record for it in groupsToPersons)

Due to restrictions of time (ring any bells?) i had to find a quick way (obviously not the best) to show all groups..... and that's how isActive came into my life;)
Unfortunately, i still haven't found the time (or perhaps the mood) to make some major modifications....
i guess i have to delve into the code and change a few join expressions... (amongst us : these where my very very first projects in vb.net and the code is not what someone would call readable ;) )
cheers

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-03 : 09:38:17

yeah i remember how that was like.... unreadable code... lousy sql...no time...
times change :))

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources