Query for single value

# Query for single value

• Friday, April 13, 2012 4:28 PM

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?

• Edited by Friday, April 13, 2012 4:28 PM
•

### All Replies

• Friday, April 13, 2012 4:30 PM

Select t1.Computer, T1.Owner

FROM Table1 t1

JOIN Table2 t2 on t1.Computer = t2.computer

WHERE t2.Disk = 'Disk0'

Chuck

• Marked As Answer by Friday, April 13, 2012 7:31 PM
•
• Friday, April 13, 2012 4:33 PM
Moderator

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

• Friday, April 13, 2012 4:37 PM

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

• Edited by Friday, April 13, 2012 4:39 PM
• Marked As Answer by Friday, April 13, 2012 7:31 PM
•
• Friday, April 13, 2012 7:31 PM