Hi folks!

I am not too experienced with SQL as you might understand, and therefore I
would like to get a little help if possible

In a database I have 2 tables, a name table and a roles table.
Let us simlify and say that the name table consists of 2 fields FLD_LOGIN
and FLD_NAME.
The roles table may consist of 2 fields FLD_LOGIN and FLD_ROLE.
The roles table is conntected to the name table via a master/detail relation
on the FLD_LOGIN.
In the roles table there may be zero to f.i. 100 different roles for each
name.

Is it possible via sql to list only the names in the name table that has
both f.i. Role10 and Role60 in the roles table?

Sorry for my poor English

Kind regards
TorB.

TorB.


Something like

SELECT *
FROM nametable
JOIN roletable ON nametable.FLD_LOGIN = roletable.FLD_LOGON
WHERE
roletable.FLD_ROLE = Role10
AND
roletable.FLD_ROLE = Role60

Roy Lambert

Thank you very much, Roy!
Great help !!
Kind regards
TorB.

****

"Roy Lambert" <roy.lambert@skynet.co.uk> skrev i melding
news:349FCCEF-ECB5-4D8A-AEE5-4177E5E67D45@news.elevatesoft.com...
> TorB.
>
>
> Something like
>
> SELECT *
> FROM nametable
> JOIN roletable ON nametable.FLD_LOGIN = roletable.FLD_LOGON
> WHERE
> roletable.FLD_ROLE = Role10
> AND
> roletable.FLD_ROLE = Role60
>
> Roy Lambert

Roy,

That's not going to work because the where condition is always false.
I have another suggestion to TorB. :

SELECT
  *
FROM
  NameTable N
WHERE
  EXISTS(SELECT * FROM RoleTable
         WHERE FLD_LOGIN = N.FLD_LOGIN AND FLD_ROLE = 'Role10' )
  AND
  EXISTS(SELECT * FROM RoleTable
         WHERE FLD_LOGIN = N.FLD_LOGIN AND FLD_ROLE = 'Role60' )

--
Fernando Dias
[Team Elevate]

Here is another way to do the same:

SELECT
  *
FROM
  NameTable
WHERE
  FLD_LOGIN IN ( SELECT FLD_LOGIN FROM RoleTable WHERE FLD_ROLE = 'Role10'
                 INTERSECT
                 SELECT FLD_LOGIN FROM RoleTable WHERE FLD_ROLE = 'Role60' )



--
Fernando Dias
[Team Elevate]

Fernando


Dead right - my brain was in horizontal (column) mode whilst the table was in vertical (row) mode. I, and I suspect many others, keep on falling into that trap.

Roy Lambert

Fernando


The bad news is I just tested yours and it returns nothing either This does

select *
from names
join
(
select distinct r0.login from roles r0
join roles r1 on r1.login = r0.login
where
r0.role <> r1.role
and
(r0.role ='06' or r0.role ='10')
and
(r1.role ='06' or r1.role ='10')

) as z on names.login = z.login

Its a bit complex but it does show the power of ElevateDB


Roy Lambert [Team Elevate]

Roy,

> The bad news is I just tested yours and it returns nothing either

I don't understand where the error is...
May I see the test data you used, please?

--
Fernando Dias
[Team Elevate]

Fernando


No you can't! (it was memory tables

Basically all I did was create two tables each with two varchar(2) columns and populated them with about half a dozen rows each. Nothing complicated but when I ran your code I received a null result.

Roy Lambert

Fernando

>I don't understand where the error is...
>May I see the test data you used, please?

It may have been me copying your code into EDBManager and altering it to use my table and column names altered something vital. At this stage I don't know.

Roy Lambert