Sql beginner question

Question

0.00/5 (No votes)

See more:

Tables:
Country table
District table
Sales table

Query:
I want to retrieve the districts which has no sales.

My query:

SQL

Select countyid, districtid, sales
From country c, district d, sales s
Where c. countryid  =d.districtid
And d.districtid = s. Salesid 
Where salesid not in ()
Group by countryid

What I have tried:

I’m not sure what syntax should I use to do this.

Posted 11-Jul-22 11:56am

Sql-beginner

Updated 12-Jul-22 10:58am

Sean Ewington

v2

Add a Solution

Comments

[no name] 11-Jul-22 19:05pm

(1) Group by country, district and sales (2) then group (original data) by country, district (3) report set 2 NOT IN set 1.

2 solutions

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 · Answer 1 · 2022-07-11T22:16:00

Solution 2

The simplest option would probably be a NOT EXISTS clause:

SQL

SELECT
    d.districtid,
    c.countryid
FROM
    district As d
    INNER JOIN country As C
    ON c.countryid = d.countryid
WHERE
    Not Exists
    (
        SELECT 1
        FROM sales As s
        WHERE s.districtid = d.districtid
    )
;

Joins (SQL Server) - SQL Server | Microsoft Docs[^]
EXISTS (Transact-SQL) - SQL Server | Microsoft Docs[^]

Posted 11-Jul-22 22:16pm

Richard Deeming

Comments

Sql-beginner 12-Jul-22 8:34am

@richard I executed the query and got empty results set. I want to display countryid, districtid and sales in select statement and retrieve only the results set which has no sales.

Richard Deeming 12-Jul-22 9:42am

If you got empty results, then there are no matching rows in your data. You need to examine your data to find out why.

We can't do that for you, since we don't have access to your database.

Sql-beginner 12-Jul-22 11:11am

@Richard, I did the joins right now and I’m getting the query results srt. But takes 11 seconds to load the data. Is there a way to tune this?

Richard Deeming 12-Jul-22 11:13am

Probably. But since we can't see your tables, your indexes, your data, or your execution plan, we can't tell you how.

You could try sharing the execution plan via Paste The Plan - Brent Ozar Unlimited®[^], in case there's anything obviously wrong.

Sql-beginner 12-Jul-22 11:33am

Select d.district, d.districtname, c.countryid
From district as d
Inner join Country as c
On c.countryid = d.countryid
Where not exists (
Select 1
From sales as s
Where s.districtid = d.districtid)

Sql-beginner 12-Jul-22 8:43am

When I do a join, it should be from primary key of table1 to primary key of table 2 right? Or is it ok to join primary key = foriegn key.
Countryid Id primary key of country table.
Countryid is foreign key of district table.
Should I connect using foreign or primary to primary. Sorry for these silly questions.

Richard Deeming 12-Jul-22 9:41am

No, definitely not. You join based on the columns which should be equal between the two tables.

Unless you're saying your countries only ever have a single district, and the district ID is always equal to the country ID, then the condition shown in your question is completely wrong.

OriginalGriff · Answer 2 · 2022-07-11T18:27:00

Solution 1

I'd suggest that you start by looking at SQL Joins[^] - they combine related data together into a single query.

And what do you think this clause does?

SQL

WHERE salesid NOT IN ()

Posted 11-Jul-22 18:27pm

OriginalGriff