Problem when relating 4 tables with left join

3

I have a problem with SQL. I want to make the list of 4 tables (user, appuserFuncitonRelation, organizations, GroupCommerce) where the organization is related to groupCOmmerce and appuserFunctionRelation is related to both and the other has the user id. What I want to do is bring the GroupCOmmerce that the user does not have. How can you do that?

/* Create DB */
Use master;
GO
IF DB_ID (N'JoinsDB3') IS NOT NULL
DROP database JoinsDB3;

CREATE DATABASE JoinsDB3;
GO
Use JoinsDB3;
GO

/*User
ID|Description
1 |admin

Organization
ID|Description
1 |TestOrg
2 |TestOrg2

CommerceGroup
ID|Organization|Description
1 |1 |TestGC
2 |1 |TestGC2
3 |1 |TestGC3
4 |2 |TestGC4

AppUserFunctionsRelation
User|organization|CommerceGroup 
1 |1 |1*/

CREATE TABLE Organizacion(
[ID] [int] NOT NULL,
[Description] [nvarchar](100) NOT NULL);

GO



INSERT INTO [Organizacion] ([ID], [Description])
VALUES (1, 'TestOrg'),(2, 'TestOrg2'); 
GO


CREATE TABLE [User](
[ID] [int] NOT NULL,
[Description] [nvarchar](100) NOT NULL);

GO

INSERT INTO [User] ([ID], [Description])
VALUES (1, 'admin'); 
GO

CREATE TABLE CommerceGroup(
[ID] [int] NOT NULL,
[Organization] [int] NOT NULL,
[Description] [nvarchar](100) NOT NULL);
GO

INSERT INTO [CommerceGroup] ([ID], [Organization], [Description])
VALUES (1, 1, 'TestCG'),(2, 1, 'TestCG2'),(3, 1, 'TestCG3'),(4, 1, 'TestCG4'); 
GO

CREATE TABLE AppUserFunctionsRelation(
[User] [int] NOT NULL,
[Organization] [int] NOT NULL,
[CommerceGroup]  [int] NOT NULL);
GO

INSERT INTO AppUserFunctionsRelation ([User], [Organization], [CommerceGroup])
VALUES (1, 1, 1); 
GO

/*QUERY*/
SELECT CommerceGroupID  FROM (SELECT 
   [AppUserFunctionsRelation].[user] as userID,
   [CommerceGroup].Organization,
   [CommerceGroup].ID AS CommerceGroupID ,[CommerceGroup].Description AS CommerceGroupDescription
   FROM [AppUserFunctionsRelation]
right JOIN [CommerceGroup] ON CommerceGroup.Organization = AppUserFunctionsRelation.Organization AND AppUserFunctionsRelation.[CommerceGroup] = [CommerceGroup].ID) as Temp
Where Temp.userID IS NULL

I have that but it does not work, thanks.

    
asked by federico santamaria 27.03.2018 в 14:23
source

4 answers

2

If I'm not mistaken, it's about doing this:

select distinct u.ID,  a1.organization, a1.CommerceGroup 
from [User] u, AppUserFunctionsRelation a1
where a1.CommerceGroup  not in (select a2.CommerceGroup  from 
AppUserFunctionsRelation a2 where a2.JobId = u.ID)

Basically this query gets all the users with all the commerceGroup that they do not have.

I am convinced that it can be made more efficient but this is the first solution that is normally applied. The next step, as you have said very well, would be to work with joins and surely a having next to group by

    
answered by 27.03.2018 в 15:30
0

If I did not understand correctly, what you need is to perform the filter against User + Organization , so the latter should be included in the filter, in addition to the user.

Taking your last query edited as a reference, and only in theory, this occurs to me:

DECLARE @User int = 1;
SELECT
    @User,
    o.ID,
    o.[Description],
    cg.Id,
    cg.[Description]
FROM 
    CommerceGroup cg INNER JOIN Organizations o
    ON cg.Organization = o.Id
WHERE
    cg.Organization + ';' + cg.Id NOT IN
    (
        SELECT a.organization + ';' + a.commercegroup 
        FROM AppUserFunctionsRelation a
            WHERE a.[User] = @User 
    )
GO
    
answered by 27.03.2018 в 16:21
0
DECLARE @User int = 1;
SELECT  
    o.[Description],
    cg.ID,
    cg.[Description]
FROM 
    CommerceGroup cg
    INNER JOIN Organization o ON cg.Organization = o.ID
        AND EXISTS (SELECT 1 FROM AppUserFunctionsRelation a 
            WHERE o.ID = a.Organization AND a.[User] = @User)
WHERE NOT EXISTS (SELECT 1 FROM AppUserFunctionsRelation a 
    WHERE cg.ID = a.CommerceGroup AND cg.Organization = a.Organization AND a.[User] = @User)
GO

I could get it: D precisely that is the query I need that works very well, but I do not know if it can be improved with join instead of not existing

    
answered by 27.03.2018 в 16:25
0

If I did not get it wrong, this could help you:

select *
       from [User] u
       cross join CommerceGroup c
       where c.id not in (select CommerceGroup
                            from AppUserFunctionsRelation
                            where [User] = u.id)

For each User we make a Cartesian product with the CommerceGroup and we only return those that were not in AppUserFunctionsRelation .

    
answered by 27.03.2018 в 17:19