How do I filter out data from a set of queries?

Question

0.00/5 (No votes)

See more:

I've got 2 queries, A:

SELECT sites.id, sites.name, 
FROM sites 
INNER JOIN domains
ON sites.id = domains.site
WHERE domains.is_deleted = 1

And B

SELECT sites.id, sites.name, 
FROM sites 
INNER JOIN domains
ON sites.id = domains.site
WHERE domains.is_deleted = 0

The first selects all sites that have domains that are deleted. The second selects all sites that have domains that are not deleted. A single site can have multiple domains.

Is there a join where I can remove all of the intersections, so that I am left with a collection of sites that only have all deleted (or not-deleted) domains?

And if there is, where can I find the syntax to query the results of queries?

What I have tried:

I've been trying to read through documents, and asking on other forums. I've mostly been getting... relatively unkind words, and I don't really understand why.

I don't know what I don't know, and I would like to know where to look so that I can learn it.

Posted 7-Jun-21 17:21pm

Sango Dragon

Updated 7-Jun-21 22:17pm

v2

Add a Solution

2 solutions

Solution 1

There is no intersection: domains.is_deleted cannot be both zero and one at the same time!

I think you need to think carefully about exactly what you are trying to do: what you describe cannot happen!

Posted 7-Jun-21 18:58pm

OriginalGriff

Comments

Sango Dragon 8-Jun-21 1:15am

My bad, I didn't make it explicit enough. I edited it in saying that a site can have multiple domains. And so a site can have a domain with 0, and a domain with 1.

OriginalGriff 8-Jun-21 1:28am

Do you want to try showing us sample data so it's clear what is where and what you expect to get? It might save us all going round the loop a coi=uple of dozen times! :D

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2021-06-07T22:17:00

Quote:
I am left with a collection of sites that only have all deleted (or not-deleted) domains?

Your current queries will return duplicate sites, one for each domain in the site.

Change your current queries to:

SQL

/* At least one domain deleted: */
SELECT id, name
FROM sites
WHERE EXISTS
(
    SELECT 1
    FROM domains
    WHERE domains.site = sites.id
    AND domains.is_deleted = 1
);

/* At least one domain not deleted: */
SELECT id, name
FROM sites
WHERE EXISTS
(
    SELECT 1
    FROM domains
    WHERE domains.site = sites.id
    AND domains.is_deleted = 0
);

It should then be easier to see how to achieve your other queries:

SQL

/* All domains deleted: */
SELECT id, name
FROM sites
WHERE NOT EXISTS
(
    SELECT 1
    FROM domains
    WHERE domains.site = sites.id
    AND domains.is_deleted = 0
);

/* No domains deleted: */
SELECT id, name
FROM sites
WHERE NOT EXISTS
(
    SELECT 1
    FROM domains
    WHERE domains.site = sites.id
    AND domains.is_deleted = 1
);