SQL Conditional Query

Question

I have an SQL query that I am trying that I am sure is easy, but I am not that well versed so I can't quite figure it out. Wasn't even sure how to word the question. Anyway here is what I am looking at:

I have a table that has the following columns: Hostname, Path, Filename, Filesize

It is essentially a directory listing of a number of computers (Hostnames).

What I want to get is a list of distinct Hostnames where neither of two paths exist for that host. For example, grab all Hostnames that do not have a corresponding C:\users\Jeff or C:\users\Mary directory. If they have one of the two, omit. Only return them if neither of these directories exist.

Any help would be much appreciated. Thanks!!

Please show what you've tried, we're not here to do your homework for you. You have to make an attempt, then we'll show you where you went wrong, and that's how you learn. — Barmar
– Barmar, Commented Dec 8, 2014 at 22:09
Also, please add a tag for the RDBMS you're using. Different databases provide different ways to do this kind of query. — Barmar
– Barmar, Commented Dec 8, 2014 at 22:10
Even if you have troubles with the SQL statement, show us the table architecture already. That is something we're all used to see, and I think it's more visible then. Now all the queries posted have all kind of different naming of columns and table. — tvCa
– tvCa, Commented Dec 8, 2014 at 22:25

Martin Smith · Accepted Answer · 2014-12-08 22:15:41Z

4

One way

SELECT HostName
FROM YourTable
GROUP BY HostName
HAVING COUNT(CASE WHEN Path IN ('C:\users\Jeff',
                                'C:\users\Mary') THEN 1 END) =0;

edited Dec 8, 2014 at 22:15

answered Dec 8, 2014 at 22:10

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mr. Llama · Accepted Answer · 2014-12-08 23:05:08Z

2

I would recommend splitting this into two parts:

Generate the list of all host names.
Generate the list of all host names that contain a path you don't want.

Then, use MINUS (which automatically removes duplicates) to get the unique results:

SELECT hostname
FROM table
MINUS
SELECT hostname
FROM table
WHERE path IN ('/search/path/one', '/search/path/two')

You can also use an anti-join instead of a MINUS, but I'll leave that up to you.

edited Dec 8, 2014 at 23:05

answered Dec 8, 2014 at 22:09

Mr. Llama

21k3 gold badges70 silver badges121 bronze badges

5 Comments

DJMcCarthy12 Over a year ago

Excellent. I can see the use of MINUS being very useful. Didn't know it existed (as I said, very new to SQL) - Thanks a bunch!

Martin Smith Over a year ago

MINUS/EXCEPT remove duplicates so the DISTINCTs are not needed.

Mr. Llama Over a year ago

@MartinSmith - Good point. The second DISTINCT isn't required. If the hostname appears multiple times in the second query, it will still only be removed once.

Martin Smith Over a year ago

Neither are required. MINUS (like UNION) doesn't return duplicates,

Mr. Llama Over a year ago

@MartinSmith - Huh, today I learned. I thought MINUS and UNION suppressed duplicates between the two inputs, I didn't realize it deduped the inputs themselves.

Multisync · Accepted Answer · 2014-12-08 22:10:42Z

1

select distinct hostname from your_table t1
where not exists (select 1 from your_table t2 
                  where t2.hostname = t1.hostname 
                    and t2.path in ('C:\users\Jeff', 'C:\users\Mary'));

answered Dec 8, 2014 at 22:10

Multisync

8,8271 gold badge20 silver badges20 bronze badges

Collectives™ on Stack Overflow

SQL Conditional Query

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related