2 Tables 1 Result, a tricky SQL

Question

After many years lurking stackoverflow, I finally have the need to come to you dev gurus in search for a little bit of help.

Context:

I'm a developer that is playing with information that I extract from SAP. I'm extracting the info and I'm creating two tables in memory using H2 database.

My tables look like this:

CREATE TABLE USERS 
(
     SID VARCHAR(255), 
     SYSID VARCHAR(5), 
     MANDT VARCHAR(3), 
     BNAME VARCHAR(255), 
     GLTGV DATE, 
     GLTGB DATE, 
     USTYP VARCHAR(2), 
     LOCNT VARCHAR(3), 
     UFLAG VARCHAR(3), 
     TRDAT DATE, 
     LTIME VARCHAR(255), 
     CLASS VARCHAR(255), 
     PWDCHGDATE DATE, 
     PROFILE VARCHAR(255)
)

CREATE TABLE ROLES 
(
     SID VARCHAR(255), 
     SYSID VARCHAR(5), 
     MANDT VARCHAR(3), 
     UNAME VARCHAR(255), 
     AGR_NAME VARCHAR(255)
)

Do not worry about the definitions, I'm just "dummyfing" them for the sake of simplicity.

One user can have none or many roles. How do you tie them together?

Well SYSID, MANDT and BNAME from table USERS have to match the content of the ROLES table, columns SYSID, MANDT and UNAME (all of the same type)

So I have created this query

SELECT DISTINCT
    t1.SID, 
    t1.SYSID, 
    t1.MANDT, 
    t1.BNAME, 
    t1.GLTGV, 
    t1.GLTGB, 
    t1.USTYP, 
    t1.LOCNT, 
    t1.UFLAG, 
    t1.TRDAT, 
    t1.LTIME, 
    t1.CLASS, 
    t1.PWDCHGDATE, 
    t1.PROFILE
FROM 
    USERS AS t1, 
    ROLES AS t2 
WHERE 
    t1.SYSID = t2.SYSID AND 
    t1.MANDT = t2.MANDT AND 
    t1.BNAME = t2.UNAME AND
    (t2.AGR_NAME = "ZTEST_ROLE")

which works well as long as I only query by one ROLE.

To query for more than one role, I do

SELECT DISTINCT
    t1.SID, 
    t1.SYSID, 
    t1.MANDT, 
    t1.BNAME, 
    t1.GLTGV, 
    t1.GLTGB, 
    t1.USTYP, 
    t1.LOCNT, 
    t1.UFLAG, 
    t1.TRDAT, 
    t1.LTIME, 
    t1.CLASS, 
    t1.PWDCHGDATE, 
    t1.PROFILE
FROM 
    USERS AS t1, 
    ROLES AS t2 
WHERE 
    t1.SYSID = t2.SYSID AND 
    t1.MANDT = t2.MANDT AND 
    t1.BNAME = t2.UNAME AND
    (t2.AGR_NAME = "ZTEST_ROLE" OR t2.AGR_NAME = "ZTEST_ROLE2")

It also works. You would use this query if you want to get info from all the users that either have ZTEST_ROLE or ZTEST_ROLE2 as roles.

Now, here is my problem, I cannot make it to work if I want to select all the users that have both roles ZTEST_ROLE and ZTEST_ROLE2. my query always return 0 rows

Here is the query that I'm using

SELECT DISTINCT
    t1.SID, 
    t1.SYSID, 
    t1.MANDT, 
    t1.BNAME, 
    t1.GLTGV, 
    t1.GLTGB, 
    t1.USTYP, 
    t1.LOCNT, 
    t1.UFLAG, 
    t1.TRDAT, 
    t1.LTIME, 
    t1.CLASS, 
    t1.PWDCHGDATE, 
    t1.PROFILE
FROM 
    USERS AS t1, 
    ROLES AS t2 
WHERE 
    t1.SYSID = t2.SYSID AND 
    t1.MANDT = t2.MANDT AND 
    t1.BNAME = t2.UNAME AND
    (t2.AGR_NAME = "ZTEST_ROLE" AND t2.AGR_NAME = "ZTEST_ROLE")

I have been playing with this for a while and I do not see where my error is

For convenience and in case you want to try this online, I have been using https://sqliteonline.com/

I appreciate your help and my apologies for the long post

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, May 29 '20 at 19:52
Does this answer your question? [Select values that meet different conditions on different rows?](https://stackoverflow.com/questions/477006/select-values-that-meet-different-conditions-on-different-rows) — philipxy, May 29 '20 at 22:54
This is a faq. Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. To pin down your problem via [mre]: Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) Say what you expect & why. — philipxy, May 29 '20 at 22:59

Mau5 · Answer 1 · 2020-05-29T19:13:25.237

0

You cant have both at the same time

(t2.AGR_NAME = "ZTEST_ROLE" and t2.AGR_NAME = "ZTEST_ROLE2")

here you are saying t2.AGR_NAME = "ZTEST_ROLE" must be true while also t2.AGR_NAME = "ZTEST_ROLE2" must be true on the same row.

you could do

(t2.AGR_NAME = "ZTEST_ROLE" and (select AGR_NAME from ROLES where uname = t1.bname and agr_name = "ZTEST_ROLE2") = "ZTEST_ROLE2"))

but i doubt this is the best way to do it. kinda hacky

Here is your example

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=18052895bcd6bea155ef01c957a06ff7

edited May 29 '20 at 19:13

answered May 29 '20 at 18:32

Mau5

54
6

After adapting my query, I still do not get results, but I will keep trying this route. I generate the SQL query dynamically so it will certainly be tricky :-) Thanks for your time! – Nicholas Mawic May 29 '20 at 19:01
when i used your tables above and made sure to have one user in both roles it worked. Are you sure that a user has both roles in your table? What i gave you is a simple check if they have "ZTEST_ROLE" do a select to see if they also have "ZTEST_ROLE2" – Mau5 May 29 '20 at 19:03
i added an example for you @NicholasMawic – Mau5 May 29 '20 at 19:13

score 0 · Answer 2 · answered May 29 '20 at 22:46

I think that a solution like this is what you are looking for.. I might be done in diffrent ways but I think you get the idea.

SELECT DISTINCT 
    t1.SID, 
    t1.SYSID, 
    t1.MANDT, 
    t1.BNAME, 
    t1.GLTGV, 
    t1.GLTGB, 
    t1.USTYP, 
    t1.LOCNT, 
    t1.UFLAG, 
    t1.TRDAT, 
    t1.LTIME, 
    t1.CLASS, 
    t1.PWDCHGDATE, 
    t1.PROFILE
FROM (
    SELECT DISTINCT
        t1.SID, 
        t1.SYSID, 
        t1.MANDT, 
        t1.BNAME, 
        t1.GLTGV, 
        t1.GLTGB, 
        t1.USTYP, 
        t1.LOCNT, 
        t1.UFLAG, 
        t1.TRDAT, 
        t1.LTIME, 
        t1.CLASS, 
        t1.PWDCHGDATE, 
        t1.PROFILE,
        case when t2.AGR_NAME = "ZTEST_ROLE" then 1 else 0 end as t2t1,
        case when t2.AGR_NAME = "ZTEST_ROLE2" then 1 else 0 end as t2t2
    FROM 
        USERS AS t1, 
        ROLES AS t2 
    WHERE 
        t1.SYSID = t2.SYSID AND 
        t1.MANDT = t2.MANDT AND 
        t1.BNAME = t2.UNAME AND
        (t2.AGR_NAME = "ZTEST_ROLE" or t2.AGR_NAME = "ZTEST_ROLE")
    ) as tmp
    WHERE t2t1 = 1 and t2t2 = 1

score 0 · Answer 3 · answered May 30 '20 at 04:04

You could use two exists subqueries, one for each role:

select u.*
from users u
where
    exists (
        select 1
        from roles r 
        where 
            r.ysid = u.sysid
            and r.mandt = u.mandt
            and r.uname = r.uname
            and r.agr_name = 'ZTEST_ROLE'
    ) 
    and exists (
        select 1
        from roles r 
        where 
            r.ysid = u.sysid
            and r.mandt = u.mandt
            and r.uname = u.bname
            and r.agr_name = 'ZTEST_ROLE2'
    )

Also it seems quite convoluted, it should be an efficient option, especially if you have an index on roles(sysid, mandt, uname, agr_name).

Another solution is to filter with an aggregate query:

select u.*
from users u
where
    exists (
        select count(*)
        from roles r 
        where 
            r.ysid = u.sysid
            and r.mandt = u.mandt
            and r.uname = r.uname
            and r.agr_name in ('ZTEST_ROLE', 'ZTEST_ROLE2')
    )  = 2

The upside of the second approach is that it is easier to extend to check for more roles at once: you just need to expand the list on the right side of the in operator, and accordingly increment the target count.

2 Tables 1 Result, a tricky SQL

3 Answers3