January 3, 2021 at 2:46 pm
Hello Community,
I have the following logic:
Find customers who didn't place orders from 2019-02-01 to 2019-03-01. Output customer's first name.
I am working with the following dataset:
CREATE TABLE #tmpTable (
id int,
first_name varchar(50),
last_name varchar(50),
city varchar(50),
address varchar(50),
phone_number varchar(50),
id int,
cust_id int,
order_date date,
order_quantity int,
order_details varchar(50),
order_cost int)
INSERT #tmpTable VALUES
(3,'Farida','Joseph','Florida','3153 Rhapsody Street','813-368-1200',1,3,CONVERT(DATETIME, '2019-03-04', 120),1,'Coat',100),
(3,'Farida','Joseph','Florida','3153 Rhapsody Street','813-368-1200',2,3,CONVERT(DATETIME, '2019-03-01', 120),1,'Shoes',80),
(3,'Farida','Joseph','Florida','3153 Rhapsody Street','813-368-1200',3,3,CONVERT(DATETIME, '2019-03-10', 120),1,'Skirt',30),
(7,'Jill','Michael','Florida','','813-297-0692',4,7,CONVERT(DATETIME, '2019-02-01', 120),1,'Coat',100),
(7,'Jill','Michael','Florida','','813-297-0692',5,7,CONVERT(DATETIME, '2019-03-10', 120),1,'Shoes',80),
(15,'Mia','Owen','Hawaii','','808-640-5201',6,15,CONVERT(DATETIME, '2019-02-01', 120),2,'Boats',100),
(15,'Mia','Owen','Hawaii','','808-640-5201',7,15,CONVERT(DATETIME, '2019-01-11', 120),3,'Shirts',60),
(15,'Mia','Owen','Hawaii','','808-640-5201',8,15,CONVERT(DATETIME, '2019-03-11', 120),1,'Slipper',20),
(15,'Mia','Owen','Hawaii','','808-640-5201',9,15,CONVERT(DATETIME, '2019-03-01', 120),2,'Jeans',80),
(15,'Mia','Owen','Hawaii','','808-640-5201',10,15,CONVERT(DATETIME, '2019-03-09', 120),3,'Shirts',50),
(5,'Henry','Jackson','Hawaii','','808-601-7513',11,5,CONVERT(DATETIME, '2019-02-01', 120),1,'Shoes',80),
(12,'Eva','Lucas','Arizona','4379 Skips Lane','301-509-8805',12,12,CONVERT(DATETIME, '2019-01-11', 120),3,'Shirts',60),
(12,'Eva','Lucas','Arizona','4379 Skips Lane','301-509-8805',13,12,CONVERT(DATETIME, '2019-03-11', 120),1,'Slipper',20),
(4,'William','Daniel','Colorado','','813-368-1200',14,4,CONVERT(DATETIME, '2019-02-01', 120),1,'Shoes',80),
(4,'William','Daniel','Colorado','','813-368-1200',15,4,CONVERT(DATETIME, '2019-01-11', 120),3,'Shirts',60),
(3,'Farida','Joseph','Florida','3153 Rhapsody Street','813-368-1200',16,3,CONVERT(DATETIME, '2019-04-19', 120),3,'Shirts',50),
(7,'Jill','Michael','Florida','','813-297-0692',17,7,CONVERT(DATETIME, '2019-04-19', 120),1,'Suit',150),
(15,'Mia','Owen','Hawaii','','808-640-5201',18,15,CONVERT(DATETIME, '2019-04-19', 120),1,'Skirt',30),
(15,'Mia','Owen','Hawaii','','808-640-5201',19,15,CONVERT(DATETIME, '2019-04-19', 120),2,'Dressess',200),
(12,'Eva','Lucas','Arizona','4379 Skips Lane','301-509-8805',20,12,CONVERT(DATETIME, '2019-01-11', 120),1,'Coat',100)
SELECT * FROM #tmpTable
My solution attempt is as follows:
SELECT
customers.first_name
FROM (SELECT
orders.cust_id
FROM dbo.orders
WHERE orders.order_date BETWEEN '2019-02-01' AND '2019-03-01') SubQuery
,dbo.customers
INNER JOIN dbo.orders
ON customers.id = orders.cust_id
WHERE customers.id NOT IN (SubQuery.cust_id)
GROUP BY customers.first_name
However, the query is incorrect.
The correct result should like the following:
FirstName
John
Emma
Liam
Mark
Eva
Jack
Mona
Lili
Justin
Frank
Can someone take a look at my code and let me know where I'm going wrong?
January 3, 2021 at 2:59 pm
Untested, but should get you closer.
select c.FirstName
from dbo.customers c
where not exists (select 1 from dbo.orders o where o.cust_id = c.id and o.order_date BETWEEN '20190201' AND '20190301')
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 3, 2021 at 3:04 pm
Can I also point out, that I'm trying to achieve the result without using LEFT JOIN
January 3, 2021 at 3:08 pm
Can I also point out, that I'm trying to achieve the result without using LEFT JOIN
If you are referring to my code, that's called a SEMI JOIN and is one of the most efficient ways of solving this type of problem.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 3, 2021 at 3:11 pm
SSC Guru
Thanks for reaching out.
Thats great
Can you let me know why my code won't work
January 3, 2021 at 5:30 pm
The table #tmpTabl specifies the column 'id' twice. Presumably the second 'id' is the unique identifier of the 'orders' table so here it's renamed as 'o_id'.
drop table if exists #tmpTabl;
go
CREATE TABLE #tmpTable (
id int,
first_name varchar(50),
last_name varchar(50),
city varchar(50),
address varchar(50),
phone_number varchar(50),
o_id int,
cust_id int,
order_date date,
order_quantity int,
order_details varchar(50),
order_cost int)
INSERT #tmpTable VALUES
(3,'Farida','Joseph','Florida','3153 Rhapsody Street','813-368-1200',1,3,CONVERT(DATETIME, '2019-03-04', 120),1,'Coat',100),
(3,'Farida','Joseph','Florida','3153 Rhapsody Street','813-368-1200',2,3,CONVERT(DATETIME, '2019-03-01', 120),1,'Shoes',80),
(3,'Farida','Joseph','Florida','3153 Rhapsody Street','813-368-1200',3,3,CONVERT(DATETIME, '2019-03-10', 120),1,'Skirt',30),
(7,'Jill','Michael','Florida','','813-297-0692',4,7,CONVERT(DATETIME, '2019-02-01', 120),1,'Coat',100),
(7,'Jill','Michael','Florida','','813-297-0692',5,7,CONVERT(DATETIME, '2019-03-10', 120),1,'Shoes',80),
(15,'Mia','Owen','Hawaii','','808-640-5201',6,15,CONVERT(DATETIME, '2019-02-01', 120),2,'Boats',100),
(15,'Mia','Owen','Hawaii','','808-640-5201',7,15,CONVERT(DATETIME, '2019-01-11', 120),3,'Shirts',60),
(15,'Mia','Owen','Hawaii','','808-640-5201',8,15,CONVERT(DATETIME, '2019-03-11', 120),1,'Slipper',20),
(15,'Mia','Owen','Hawaii','','808-640-5201',9,15,CONVERT(DATETIME, '2019-03-01', 120),2,'Jeans',80),
(15,'Mia','Owen','Hawaii','','808-640-5201',10,15,CONVERT(DATETIME, '2019-03-09', 120),3,'Shirts',50),
(5,'Henry','Jackson','Hawaii','','808-601-7513',11,5,CONVERT(DATETIME, '2019-02-01', 120),1,'Shoes',80),
(12,'Eva','Lucas','Arizona','4379 Skips Lane','301-509-8805',12,12,CONVERT(DATETIME, '2019-01-11', 120),3,'Shirts',60),
(12,'Eva','Lucas','Arizona','4379 Skips Lane','301-509-8805',13,12,CONVERT(DATETIME, '2019-03-11', 120),1,'Slipper',20),
(4,'William','Daniel','Colorado','','813-368-1200',14,4,CONVERT(DATETIME, '2019-02-01', 120),1,'Shoes',80),
(4,'William','Daniel','Colorado','','813-368-1200',15,4,CONVERT(DATETIME, '2019-01-11', 120),3,'Shirts',60),
(3,'Farida','Joseph','Florida','3153 Rhapsody Street','813-368-1200',16,3,CONVERT(DATETIME, '2019-04-19', 120),3,'Shirts',50),
(7,'Jill','Michael','Florida','','813-297-0692',17,7,CONVERT(DATETIME, '2019-04-19', 120),1,'Suit',150),
(15,'Mia','Owen','Hawaii','','808-640-5201',18,15,CONVERT(DATETIME, '2019-04-19', 120),1,'Skirt',30),
(15,'Mia','Owen','Hawaii','','808-640-5201',19,15,CONVERT(DATETIME, '2019-04-19', 120),2,'Dressess',200),
(12,'Eva','Lucas','Arizona','4379 Skips Lane','301-509-8805',20,12,CONVERT(DATETIME, '2019-01-11', 120),1,'Coat',100)
SELECT * FROM #tmpTable
Then the code. Well, imo the place to start is always the FROM clause because it's evaluated first. Issues (in no particular order)
So what could the query look? Maybe something like this
select distinct c.first_name
from dbo.customers c
where not exists (select 1
from dbo.orders o
where c.id=o.cust_id
and o.order_date between '2019-02-01'
and '2019-03-01');
It selects the unique first names from the 'customers' table where not a single order was placed by that cust_id between the 2 test dates.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 3, 2021 at 5:56 pm
Hi Steve,
I'm getting the error here where c.cust_id=o.cust_id with your code
January 3, 2021 at 6:29 pm
I think maybe it should be 'where c.id=o.cust_id' 🙂
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 4, 2021 at 6:23 pm
Can I also point out, that I'm trying to achieve the result without using LEFT JOIN
Curious, what issues will a LEFT JOIN cause?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 4, 2021 at 8:12 pm
carlton 84646 wrote:Can I also point out, that I'm trying to achieve the result without using LEFT JOIN
Curious, what issues will a LEFT JOIN cause?
Duplicates - requiring DISTINCT or GROUP BY to remove????
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 4, 2021 at 9:00 pm
You don't seem to know the table must have a key. By definition, not as an option. Furthermore, we cannot be all NULLs. Basically, you posted a very badly designed deck of punch cards and you seem to think that everything is a string of 50 characters and you have two columns, both named the vague and useless "id", instead of "<something in particular>-id".
You really need to take the time to learn how to design a schema instead of just throwing out garbage like this. Once you learn the basics, you will simply write good schemas without any thought about it. Here's an attempt at fixing up what you posted, and normalizing it.
CREATE TABLE Customers
(customer_nbr CHAR(16) NOT NULL PRIMARY KEY,
customer_first_name VARCHAR(35) NOT NULL,
customer_last_name VARCHAR(35) NOT NULL,
city_name VARCHAR(35) NOT NULL,
street_address VARCHAR(35) NOT NULL,
state_code CHAR(2) NOT NULL,
zip_code CHAR(5) NOT NULL CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]')
customer_phone_nbr CHAR(35) NOT NULL
CHECK(customer_phone_nbr LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][09]))
);
Looking at your points score, you've probably been using SQL for maybe a few weeks at most. Please notice this table deals the set of one and only one kind of entity, that the name field. Follow U.S. Postal Service rules about label with, and look at the column names. The post fixes like "_code", "_name", etc. are called attribute properties. They are what make something specific instead of vague and general. Notice the use of check constraints to guarantee that data elements are formatted properly.
The remaining columns in your non-table seem to belong to an order. However, getting order details into a character string is insane. The order details are separate entities by themselves and would require another table. So let's try and fix that.
CREATE TABLE Orders
(order_nbr CHAR(10) NOT NULL PRIMARY KEY,
customer_nbr CHAR(16) NOT NULL REFERENCES Customers ON DELETE CASCADE,
order_date DATE DEFAULT CURRENT TIMESTAMP NOT NULL,
order_qty INTEGER NOT NULL CHECK (order_qty > 0) ,
order_gtin CHAR(15) NOT NULL REFERENCES Inventory (gtin) ON DELETE CASCADE,
unit_cost DECIMAL (10,2) NOT NULL);
The GTIN is a universal code for "global trade identification number, of items. The important thing here is notice the use of the references clauses. This is called a relational database because things are related. Notice the use of check constraints to ensure data integrity. Currency amounts are expressed as a decimal data type, not as integers. Likewise, dates are done as a temporal data type (usually DATE) and not converted into strings for display. That was 1960s.COBOL and it has no place in SQL. real SQL programmers use cast () and not the old Sybase convert ().
I'm not going to copy over your insertions. For what it's worth, using just the word INSERT is a proprietary thing that SQL Server got from the original Sybase product. The ANSI/ISO syntax is INSERT INTO. While it will work, it just says that you don't know what you're doing and that you only talk and a local dialect. Another giveaway, is the way you arranged your select statement. You laid it out the way we would have done this with postcards.
SELECT C.customer_first_name
FROM Customers AS C, Orders AS O
WHERE C.cust_nbr = O.cust_nbr
AND O.order_date BETWEEN '2019-02-01' AND '2019-03-01';
You are actually making this has to be.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 4, 2021 at 9:10 pm
Michael L John wrote:carlton 84646 wrote:Can I also point out, that I'm trying to achieve the result without using LEFT JOIN
Curious, what issues will a LEFT JOIN cause?
Duplicates - requiring DISTINCT or GROUP BY to remove????
Something like should give no duplicates(unless there's duplicates in the customers table). May or may not perform better than a not exists.
select c.*
from dbo.customers c
LEFT OUTER JOIN dbo.orders o
ON c.id=o.cust_id
and o.order_date between '2019-02-01'
and '2019-03-01'
WHERE o.cust_id IS NULL
January 4, 2021 at 9:48 pm
Jeffrey Williams wrote:Michael L John wrote:carlton 84646 wrote:Can I also point out, that I'm trying to achieve the result without using LEFT JOIN
Curious, what issues will a LEFT JOIN cause?
Duplicates - requiring DISTINCT or GROUP BY to remove????
Something like should give no duplicates(unless there's duplicates in the customers table). May or may not perform better than a not exists.
select c.*
from dbo.customers c
LEFT OUTER JOIN dbo.orders o
ON c.id=o.cust_id
and o.order_date between '2019-02-01'
and '2019-03-01'
WHERE o.cust_id IS NULL
I was guessing at the OP's reasoning...not that it would actually have any duplicates.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 5, 2021 at 12:44 pm
Hi Jeffrey,
It wouldn't cause an issue.
The challenge was just to achieve the result without using LEFT JOIN.
January 8, 2021 at 12:08 am
SELECT C.first_name
FROM dbo.customers AS C
WHERE ID NOT IN
(
SELECT CustomerID
FROM dbo.orders AS O
WHERE O.order_date
BETWEEN '2019-02-01' AND '2019-03-01'
);
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply