Hi guys

I have 3 tables.

Table 1:

ID  Name  Description

1  ABc      xyz

2  ABC      XYZ

Table 2:

RoleID   Role

1         Admin

2         QA

Table 3:

ID   RoleID  Time

1     1         09:14

2     1         09:15

1     2         09:16

Now I want all the records which belongs to RoleID 1 but if same ID is belongs to RoleID 2 than i don't want that ID.

From above tables ID 1 belongs to RoleID 1 and 2 so i don't want ID 1.

Please give me query for this example. 

Thank you.

Hi Parth,

Please see below sample.

DECLARE @TABLE1 TABLE(ID INT,  Name VARCHAR(10),  Description VARCHAR(10));
INSERT INTO @TABLE1 VALUES
(1  ,'ABc',      'xyz'),
(2  ,'ABC',      'XYZ');

DECLARE @TABLE2 TABLE(RoleID INT,  Role VARCHAR(10))
INSERT INTO @TABLE2 VALUES
(1,         'Admin'),
(2,         'QA');

DECLARE @TABLE3 TABLE(ID INT,  RoleID VARCHAR(10),TIME TIME);
INSERT INTO @TABLE3 VALUES
(1,     1  ,     '09:14'),
(2,     1  ,     '09:15'),
(1,     2  ,     '09:16');

SELECT * FROM @TABLE1 t1 JOIN @TABLE3 t3 ON t1.id=t3.id 
						 JOIN @TABLE2 t2 ON t3.RoleID=t2.RoleID
WHERE t3.RoleID=1 AND NOT EXISTS(SELECT 1 FROM @TABLE3 WHERE ID=T3.ID AND RoleID=2)

If you have any question, feel free to let me know.

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. 

There is no generic "id" in RDBMS. TIME is a data type is SQL, not a column name. And it is always "hh:mm:ss" not just hours and minutes. Whre is the DRI in your third (non-) table?  You do not even know that row are not records! You have been told basic definition and not learned it. 

We are getting tied of doing your homework for you; we need to report you to your school so they can expel you for plagiarism. 

SELECT t1.ID,t1.Name,t1.Description,t2.Role
FROM Table1 t1
INNER JOIN (
SELECT ID,MAX(RoleID) AS RoleID
FROM Table3 
GROUP BY ID
HAVING MAX(RoleID) = 1
)t3
ON t3.ID = t1.ID
INNER JOIN Table2 t2
ON t2.RoleID = t3.RoleID