1

Topic: SOS! Help to understand with a problem

Greetings, children!
Help, please to understand as to solve a problem!
I a beginner  on sampling SQL of requests, not all become clear at once, therefore I ask you about the help.
Problem conditions such:
Select all Employees who did not make any orders before July 8th 1996 (including);
My vague request is that
SELECT LastName, FirstName
FROM Employees JOIN Orders ON Employees. EmployeeID = Orders. EmployeeID
WHERE OrderDate <= ' 1996-07-08 ' also I understand, what it is necessary to apply function COUNT on a condition (who did not make any orders) but I do not understand as to apply? A subquery to do?
More low the link where it is possible to look at communications of tables
https://www.w3schools.com/sql/trysql.as … select_all

2

Re: SOS! Help to understand with a problem

nikav123 wrote:

who did not make any orders before July 8th 1996

"There is no person, there is no problem."
There is no order - there is no order date (OrderDate is null) - leave COUNT in rest.

3

Re: SOS! Help to understand with a problem

nikav123;
1)
SELECT LastName, FirstName
FROM Employees LEFT JOIN Orders ON Employees. EmployeeID = Orders. EmployeeID
WHERE OrderDate <= ' 1996-07-08 ' and Orders. EmployeeID is null
2)
SELECT LastName, FirstName
FROM Employees
WHERE not exists (select 1 from Orders where Employees. EmployeeID = Orders. EmployeeID and OrderDate <= ' 1996-07-08 ')
ps
If Google correctly translated
Select all employees who did not do orders till July, 8th, 1996 (including);
.....
stax

4

Re: SOS! Help to understand with a problem

Stax;
Oh
... OrderDate <= date ' 1996-07-08 '...
....
stax

5

Re: SOS! Help to understand with a problem

Stax, on your request there are no results any...
It has been added and date, LEFT, and Orders. EmployeeID is null

6

Re: SOS! Help to understand with a problem

Viewer, your help also does not work (((

7

Re: SOS! Help to understand with a problem

database SAID - no result

8

Re: SOS! Help to understand with a problem

nikav123 wrote:

Viewer, your help also does not work (((

To me laziness to look structure of tables so it there was not a help and only a hint.

9

Re: SOS! Help to understand with a problem

Clearly)

10

Re: SOS! Help to understand with a problem

SELECT * FROM Employees
WHERE NOT EXISTS (
SELECT * FROM Orders
WHERE Employees. EmployeeID = Orders. EmployeeID and DATE (Orders. OrderDate) <= DATE ("1996-07-08")
);

MySQL such MySQL...

11

Re: SOS! Help to understand with a problem

Stax wrote:

oh

Stax, oh here in respect of meditation over a difference between ON and WHERE in OUTER JOIN. Meditate.
SY.

12

Re: SOS! Help to understand with a problem

Dshedoo, I thank, there is a result))) and why it is impossible to beat a variant  is null?

13

Re: SOS! Help to understand with a problem

nikav123 wrote:

Dshedoo, I thank, there is a result))) and why it is impossible to beat a variant  is null?

SELECT Employees. EmployeeID
FROM Employees
LEFT JOIN
Orders
ON Employees. EmployeeID = Orders. EmployeeID
AND
OrderDate <= date ' 1996-07-08'
WHERE Orders. EmployeeID is null
/

For example:

SQL> with employees as (
2 select level EmployeeID from dual connect by level <= 4
3);
4 orders as (
5 select 1 EmployeeID, date ' 1996-07-01 ' OrderDate from dual union all
6 select 1, sysdate from dual union all
7 select 2, date ' 1996-07-01 ' from dual union all
8 select 3, sysdate from dual
9)
10 SELECT Employees. EmployeeID
11 FROM Employees
12 LEFT JOIN
13 Orders
14 ON Employees. EmployeeID = Orders. EmployeeID
15 AND
16 OrderDate <= date ' 1996-07-08'
17 WHERE Orders. EmployeeID is null
18 /
EMPLOYEEID
----------
4
3
SQL>

SY.

14

Re: SOS! Help to understand with a problem

nikav123 wrote:

Stax, on your request there are no results any...
It has been added and date, LEFT, and Orders. EmployeeID is null

SQL> ed
Wrote file afiedt.buf
1 with EMPLOYEES (EMPLOYEEID, FIRSTNAME) as (
2 select 100, ' Steven ' from dual union all
3 select 101, ' Neena ' from dual union all
4 select 102, ' Lex ' from dual union all
5 select 103, ' Alexander ' from dual)
6, orders (orderId, EmployeeID, OrderDate) as (
7 select 1,100, date ' 1996-07-08 ' from dual union all
8 select 2,101, date ' 1997-07-08 ' from dual union all
9 select 3,102, date ' 1995-07-08 ' from dual union all
10 select 4,102, date ' 1999-07-08 ' from dual)
11 SELECT FirstName
12 FROM Employees LEFT JOIN Orders ON Employees. EmployeeID = Orders. EmployeeID and OrderDate <= date ' 1996-07-08'
13* WHERE Orders. EmployeeID is null
SQL> /
FIRSTNAME
---------
Alexander
Neena
SQL> ed
Wrote file afiedt.buf
1 with EMPLOYEES (EMPLOYEEID, FIRSTNAME) as (
2 select 100, ' Steven ' from dual union all
3 select 101, ' Neena ' from dual union all
4 select 102, ' Lex ' from dual union all
5 select 103, ' Alexander ' from dual)
6, orders (orderId, EmployeeID, OrderDate) as (
7 select 1,100, date ' 1996-07-08 ' from dual union all
8 select 2,101, date ' 1997-07-08 ' from dual union all
9 select 3,102, date ' 1995-07-08 ' from dual union all
10 select 4,102, date ' 1999-07-08 ' from dual)
11 SELECT FirstName
12 FROM Employees
13* WHERE not exists (select 1 from Orders where Employees. EmployeeID = Orders. EmployeeID and OrderDate <= date ' 1996-07-08 ')
SQL> /
FIRSTNAME
---------
Alexander
Neena

.....
stax

15

Re: SOS! Help to understand with a problem

nikav123 wrote:

Dshedoo, I thank, there is a result))) and why it is impossible to beat a variant  is null?

It is possible. Beat)

16

Re: SOS! Help to understand with a problem

SY wrote:

it is passed...
Stax, oh here in respect of meditation over a difference between ON and WHERE in OUTER JOIN. Meditate.
SY.

Corrected (haste to good does not result)
.....
stax

17

Re: SOS! Help to understand with a problem

SY, thanks! Very much helped))
And it is possible to ask still for you to explain to the logician why LEFT JOIN?

18

Re: SOS! Help to understand with a problem

Stax, I thank!!

19

Re: SOS! Help to understand with a problem

nikav123 wrote:

to explain logic why LEFT JOIN?

LEFT OUTER JOIN returns all the line long the left table even if in the right table of an appropriate line is not present. In that case the line of the left table incorporates to hypothetical line of the right table where all fields NULL. Therefore when we do LEFT OUTER JOIN on EmployeeID and OrderDate <= date ' 1996-07-08 ' that for all Employee. EmployeeID for which there are no lines in Orders with such EmployeeID and OrderDate <= date ' 1996-07-08 ' LEFT OUTER JOIN returns NULL for all fields Orders. Then with  WHERE Orders. EmployeeID is NULL we select these lines.
SY.

20

Re: SOS! Help to understand with a problem

One more decision through MINUS:

SELECT Employees. EmployeeID
FROM Employees
MINUS
SELECT EmployeeID
FROM Orders
WHERE OrderDate <= date ' 1996-07-08'
/

SY.

21

Re: SOS! Help to understand with a problem

SY! As for the beginner, me still to train and  from such plan decisions))
Can you still can prompt that I do not so in another already to the task?))) so  with 4 tables how much I truly understand...
Here a condition
Display all customers information who ordered "Tofu" product, order results from highest customer id to lowest
Here my decision
SELECT CustomerName, ContactName, Address, Ci ty, PostalCode, Country
FROM Customers JOIN Orders ON Customers. CustomerID = Orders. CustomerID
WHERE OrderID = (SELECT OrderID FROM OrderDetails JOIN Products ON OrderDetails. ProductID = Products. ProductID WHERE ProductName = ' Tofu ')
ORDER BY CustomerID DESC

22

Re: SOS! Help to understand with a problem

The DB swears: "Error 1: could not prepare statement (1 ambiguous column name: CustomerID)"

23

Re: SOS! Help to understand with a problem

nikav123;
In request

SELECT CustomerName,ContactName,Address,City,PostalCode,Country
FROM Customers JOIN Orders ON Customers. CustomerID = Orders. CustomerID
WHERE OrderID = (SELECT OrderID FROM OrderDetails JOIN Products ON OrderDetails. ProductID = Products. ProductID WHERE ProductName = ' Tofu ')
ORDER BY CustomerID DESC

Column CustomerID, as from table Customers and Orders is used twice
Specify explicitly demanded column in construction ORDER BY.
For example, in the form of ORDER BY Customers. CustomerID desc

24

Re: SOS! Help to understand with a problem

nikav123;

wrote:

Display all customers information who ordered "Tofu" product, order results from highest customer id to lowest

SELECT C.CustomerName,C.ContactName,C.Address,C.City,C.PostalCode,C.Country
FROM CUSTOMERS A C
WHERE EXISTS
(
SELECT 1 FROM PRODUCTS P WHERE P.CUSTOMER_ID=C.CUSTOMER_ID AND
P.ProductName = ' Tofu'
)
ORDER BY C.CUSTOMER_ID DESC