![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
![]() |
Tue, Apr 7 2009 8:34 AM | Permanent Link |
"TorB." | 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. |
Tue, Apr 7 2009 8:47 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Tue, Apr 7 2009 11:21 AM | Permanent Link |
"TorB." | 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 |
Tue, Apr 7 2009 12:37 PM | Permanent Link |
Fernando Dias ![]() | 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] |
Tue, Apr 7 2009 12:49 PM | Permanent Link |
Fernando Dias ![]() | 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] |
Tue, Apr 7 2009 1:40 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Tue, Apr 7 2009 1:50 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | Fernando
The bad news is I just tested yours and it returns nothing either ![]() 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] |
Tue, Apr 7 2009 2:28 PM | Permanent Link |
Fernando Dias ![]() | 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] |
Wed, Apr 8 2009 3:00 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Wed, Apr 8 2009 3:14 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |