quote:

---

Originally posted by sqlmaster555112

Suppose you have two tables one named employee table and other named job table in employee you have fields as empid ,name ,address,[phone no]
in job table you have empid,jobid,salary
suppose you wanna find is the empids with same salary
then this can be done by first
(select empid,salary from employee a inner join job b on a.empid=b.empid
group by empid,salary)

this query will provide us with unique combinations of empid and salary Right.

Now what we want is to find the empids that are present in the employee table but not in the above table So

(Select a.empid from employee a inner join
(select empid,salary from employee a inner join job b on a.empid=b.empid
group by empid,salary) as b on a.salary=b.salary
)

If you try to put this into your data this may solve the issue if not please let me know....Thanks


@sqlmaster

http://www.intelcs.com/SQL_Server_Consultant.aspx

---

quote:

---

Originally posted by tech_1

Sure except, i dont know how it will really be like. but an example of what I believe:

Objective: find devices with the same Identifier

there is a table where "Attributes" are stored. These attributes contain a form of a key/value presence. Example: Key would be "Identifier" and value would be...the value


ID: 1
Key: "Identifier"
Value: "0001"
DeviceID (FK): 1


ID: 2
Key: "Identifier"
Value: "0001"
DeviceID (FK): 6

ID: 3
Key: "Identifier"
Value: "0002"
DeviceID (FK): 4

ID: 4
Key: "Identifier"
Value: "0002"
DeviceID (FK): 8

ID: 5
Key: "Identifier"
Value: "0062"
DeviceID (FK): 54



so as you can see, there are duplicates there for the key "Identifier" and the value of 0002 and 0001. I want to get these results

---

SELECT t.*
FROM tbl t 
inner join (select key,value
            from tbl
            group by key,value
            having count(*) >1)t1
on t1.key = t.key
and t1.value = t.value