help to solve this query

Question

I have a two table Users && Roles

Roles contains following columns
Roleid, Rolename,InboxEnabled

Users contains
Userid,Region,Branchid,Roles

The Data in the Roles Table is
Admin -Admin -y
Enduser-EndUser-n
Dev -Developer-y
Network-Network-y

The data in the users table is
Ravi-North-Delhi-Admin,Dev,Network,
Raju-North-Delhi-Dev,Enduser,

select Roleid from Roles where Inboxenabled ='y' will return me Admin and Dev roles

when i pass this query as a condition to users table i have to get output as Ravi

Anyone please help me to solve this query

i

Answer 1

Have you analysed your Condition before posting ,it is not possible to select only ravi because you have given Inboxenabled for  Admin,Dev as Y,
How can the query select based on this condition Ravi only

Answer 2

create table Roles(Roleid varchar(20), RoleName Varchar(20),InboxEnabled Varchar(20))
create Table Users(Userid varchar(20), Region Varchar(20), Branchid Varchar(20),Roles Varchar(20))

insert into users 
select 'ravi','northDelhi','Admin','Network' union
select 'raju','northDelhi','dev','enduser' 

insert into Roles
select 'ADmin','admin','y' union 
select 'Enduser','EndUser','n' union
select 'Dev','Developer','y' union
select 'Network','Network','y'

select userid from users where Branchid in (select Roleid from Roles where Inboxenabled ='y')

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 3

>> I have a two table Users && Roles <<

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. From the narrative and fragments you did post, it looks likes you use flags in SQL! I hope not.

CREATE TABLE Roles
(role_id CHAR(5) NOT NULL PRIMARY KEY,
 role_name VARCHAR(25) NOT NULL,
  ..);

CREATE TABLE Users
(user_id CHAR(5) NOT NULL PRIMARY KEY,
 region_nbr INTEGER NOT NULL,
 branch_id INTEGER NOT NULL,
 ..);

You had “roles” as a column in the users table; That is both impossible and absurd.
 
Please try again with DDL and correct relational design.  If you will be polite, peopel will help you if they can. But it looks like you might need much more help on basic RDBMS than you can get in a  forum.

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL