Query for single value

Question

We have the followin two tables:

Table1:

Computer| Owner
Comp1     | User1
Comp2     | User2
...

Table2:

Computer| Disk
Comp1     | Disk0
Comp1     | Disk1
Comp2     | Disk0
Comp3     | Disk0
Comp3     |Disk1

I'm newby to T-SQL so I need your help to make query for creating the table based on two ones. The table must contain only the computers which have only disk0. For example:

Table3:

ComputerersWithDisk0 | Owner
Comp2                        | User2

How to do it?

Answer 1

Select t1.Computer, T1.Owner

FROM Table1 t1

JOIN Table2 t2 on t1.Computer = t2.computer

WHERE t2.Disk = 'Disk0'

---

Chuck

Answer 2

select T1.Computer as [ComputersWithDisk0], T1.Owner FROM T1 INNER JOIN T2 ON T1.Computer = T2.Computer

GROUP BY T1.Computer, T1.Owner

HAVING MAX(T2.Disk) = 'Disk0' and MIN(T2.Disk) = 'Disk0'

-----------------------------------

The above will select all computers that have only Disk0 and nothing else. The problem itself is called 'Relational Division' and you may find this blog post http://sqlblog.com/blogs/peter_larsson/archive/2010/06/30/relational-algebra.aspx helpful.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

One more method

DECLARE @Table1 TABLE(Computer VARCHAR(20),Owner VARCHAR(20))
INSERT INTO @Table1
VALUES('Comp1','User1'),('Comp2','User2')

DECLARE @Table2 TABLE(Computer VARCHAR(20),Disk VARCHAR(20))
INSERT INTO @Table2
VALUES('Comp1','Disk0'),('Comp1','Disk1'),
('Comp2','Disk0'),
('Comp3','Disk0'),('Comp3','Disk1')


SELECT T2.Computer,Owner
FROM @Table2 T2
JOIN @Table1 T1
ON T2.Computer = T1.Computer
AND T2.Disk = 'Disk0'
AND NOT EXISTS(SELECT * 
				FROM @Table2 T 
				WHERE Computer = T2.Computer 
					AND Disk = 'Disk1')

---

Abdallah El-Chal, PMP

Answer 4

Thanks everyone for the answers. They are very helpful!