Return value only once but value can be from two different columns.

[EM1107]

Good day Guys.

I would like you expertise on my issue.

I have a table that include tree columns.

COl1, Col2, Col3

Col2 can be linked to different tables, Table 1 and Table 2

Lets assume that Table 1 include the name of a user and Table 2 include group name
Sometime the value in COL2 can be either from group or from user.
As it is I can get the data using 4 columns one showing the user and one showing the group
but if I the value belong to a group I see the user as being null or the opposite.

I would like to extract using a query for the 3 columns but only return 3 columns.
Anyone know of a way I can show the 3 column and only show valid value from either tables?

I hope that I am clear enough.

 

[Andrzejek]

Could you show some sample data representing your issue, and the output you expect?


---- Andy

There is a great need for a sarcasm font.

 

[EM1107]

Sure can.

Col1 col2 col3
1 USER1 1000
2 Group1 1000
3 user2 20500
4 Group2 500

So Users name are in table 1 column name
Groups are under table 2 column name Group_name

If I run a join I would join my user name on table 1 and my group on table 2.

select col1, User_name, Group_name, col3
Perform my join

The result is as display here

Col1 User_name, Group_name, Col3
1 user1 NULL 1000
2 NULL Group1 1000
3 user2 NULL 20500
4 NULL Group2 500

So what I want is to be able to return as follow

col1 user or group col3
1 User1 1000
2 Group1 1000
3 user2 20500
4 group2 500

 

[SkipVought]

Hi,

SELECT yt.COL1, yt.COL2 As [User or Group], yt.COL3
From [Your Table] yt, [table1] t1
Where yt.col2=t1.User_Name
UNION ALL
SELECT yt.COL1, yt.COL2, yt.COL3
From [Your Table] yt, [table2] t2
Where yt.col2=t1.Group_Name

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[EM1107]

Thanks I will try that and see if I can make it work.

 

[Olaf Doschke]

You can use COALESCE for that, but I recommend you redesign this.

T-SQL allows to use the same column of one table as foreign key in two constraints to two other tables, but if you do so, referential integrity tests are done for both tables, so a key you store in Col3 would be checked to exist in both user and group table, so it would have double meaning as both and if you had unique keys non overlapping in users and groups so one value can only be either user or group and not both, you couldn't store that as foreign key.

So in short, this is not foreseen, to have a polymorühic association you would need two foreign key fields and define the constraint only one of them is allowed to be non-NULL or better use a parent table to users and groups like in this model:

CREATE TABLE permissibles (ID integer identity(1,1)
, CONSTRAINT PK_permissibles PRIMARY KEY (ID));

CREATE TABLE users (id integer identity(1,1), permissibleID int, name varchar(50)
, CONSTRAINT PK_users PRIMARY KEY (ID)
, CONSTRAINT FK_permissibleuser FOREIGN KEY (permissibleID)     
    REFERENCES permissibles (ID)     
    ON DELETE CASCADE    
    ON UPDATE CASCADE);

CREATE TABLE groups (id integer identity(1,1), permissibleID int, name varchar(50)
, CONSTRAINT PK_groups PRIMARY KEY (ID)
, CONSTRAINT FK_permissiblegroup FOREIGN KEY (permissibleID)     
    REFERENCES permissibles (ID)     
    ON DELETE CASCADE    
    ON UPDATE CASCADE);

CREATE TABLE permissions (id integer identity(1,1), permissibleID int
, CONSTRAINT PK_permissions PRIMARY KEY (ID)
, CONSTRAINT FK_permissible FOREIGN KEY (permissibleID)     
    REFERENCES permissibles (ID)     
    ON DELETE CASCADE    
    ON UPDATE CASCADE);

And then this data and final query:

declare @permissible as int;

insert into [dbo].[permissibles] DEFAULT VALUES;
select @permissible = SCOPE_IDENTITY();
insert into [dbo].[users] values (@permissible, 'user1');

insert into [dbo].[permissibles] DEFAULT VALUES;
select @permissible = SCOPE_IDENTITY();
insert into [dbo].[users] values (@permissible, 'user2');

insert into [dbo].[permissibles] DEFAULT VALUES;
select @permissible = SCOPE_IDENTITY();
insert into [dbo].[groups] values (@permissible, 'group1');

insert into [dbo].[permissibles] DEFAULT VALUES;
select @permissible = SCOPE_IDENTITY();
insert into [dbo].[groups] values (@permissible, 'group2');

insert into [dbo].[permissions] values (1),(2),(3),(4);

select p.*, coalesce(g.name, u.name) as [user or group] from [dbo].[permissions] p 
inner join [dbo].[permissibles] pi on p.permissibleid = pi.id
left join [dbo].[users] u on u.permissibleid = pi.id
left join [dbo].[groups] g on g.permissibleid = pi.id

The permission can be on users or usergroups, so they don't reference users and groups directly but via the permissibles table, to which each user or group has an association. You join through permissibles and find either a user or a group, never both (by the way the keys are generated). Permissibles has no data at all, you might add columns, but it's really just a polymorühic association key table.

The main ingredient is using coalesce, you can also use that in your design, but notice you then can't make use of foreign key constraints as the SQL Server implements them. It would almost be sufficient if permissibles would be a sequence instead of a table, but you can't define constraints to a sequence and join through it.

Bye, Olaf.


PS: You might wonder why having separate user and group primary keys if their permissibleID also is unique by definition. Well, you might have other relationships with only users or only groups and that better goes via direct relationship without going through permissibles.

Now you might use a permissibles.ID in both users and groups, but shouldn't unless you want a group to be that user only. There is no way to constrain a permissibles.ID to be only used in one of the two tables but the way you generate them and use them in only one table. You can and perhaps should also have the users.permissibleID and groups.permissibleID have a unique constraint, too. And obviously, you can also have a traditional association of users with groups by a groupusers table (ID, groupID, userID).


Olaf Doschke Software Engineering

https://www.doschke.name