• Register
0 votes
87 views

Problem :

I have written the typical MyPersons table and I have also written the MyOrders table and it is defined in a particular way so that I can do the JOIN query like the below one to return the MyOrders for all of the MyPersons.

SELECT MyPersons.LastName, MyPersons.FirstName, MyOrders.OrderNo
FROM MyPersons
INNER JOIN MyOrders
ON MyPersons.id=MyOrders.Person_id

Now my question is, how should I write the query that must return me all MyPersons with NO MyOrders?

I must write this using Mysql.

7 5 2
3,870 points

2 Answers

0 votes

Solution :

To get the desired output you can use LEFT JOIN and IS NULL as shown in the below query:

SELECT     MyPersons.LastName, MyPersons.FirstName
FROM       MyPersons
LEFT JOIN  MyOrders ON MyPersons.id = MyOrders.Person_id
WHERE      MyOrders.Person_id IS NULL;

Now you will get the result of the left join which will always contains all of the records from the "left" table (MyPersons), even if your join-condition does not find any matching record in your "right" table (MyOrders). And when there is no match, the columns of your "right" table will be NULL in your result set.

OR

You can try the below query it should work... there is always  more than one way to do it.

select * from MyPersons where MyPersons.id not in (select MyPersons_id from MyOrders)

 

9 7 4
38,600 points
0 votes

Solution:

You may want to employ LEFT JOIN and IS NULL:

SELECT     Persons.LastName, Persons.FirstName
FROM       Persons
LEFT JOIN  Orders ON Persons.id = Orders.Person_id
WHERE      Orders.Person_id IS NULL;

The result of a left join constantly holds all records of the "left" table (Persons), even in case the join-condition does not trace any matching record in the "right" table (Orders). At the time there is no match, the columns of the "right" table will NULL in the result set.

This must work. theres more than one method to do it.

select * from persons where person.id not in (select person_id from orders)

Only for fulfillment, here is the not exists version:

select * from persons p 
where not exists
(select null from orders o where o.person_id = p.id)

Approach: Employing sub-query and NOT IN clause

Algorithm

In case we have a list of customers who have ever ordered, it will be facile to know who never ordered.

We can employ the following code to obtain such list.

select customerid from orders;

Then, we can employ NOT IN to query the customers who are not in this list.

MySQL

select customers.name as 'Customers'
from customers
where customers.id not in
(
    select customerid from orders
);

One of the most usual solutions to this issue is by employing the SQL JOIN clause. You can employ the LEFT OUTER JOIN to solve this issue, as displayed below:

SELECT C.Name FROM Customers C LEFT JOIN Orders O ON C.Id = O.CustomerId WHERE O.CustomerId is NULL

Read more: https://javarevisited.blogspot.com/2016/01/sql-exists-example-customers-who-never-ordered.html#ixzz6O2CbX1wD

At the time you link two tables in SQL employing a LEFT OUTER JOIN, then a big table will be made with NULL values in the column which don't subsists in another table.

For example, the big table will have four columns C.Id, C.Name, O.Id, and O.CustomerId, for Customers who have never ordered anything, the O.CustomerId will be NULL.

Many programmers practice the mistake of employing != in JOIN condition to resolve this problem, with the assumption that if = returns matching rows, then != will return those ids which are not subsistent in another table. 

In case you struggle to perceive join, then I point out you take a look at Jose Portilla's fantastic SQL course The Complete SQL Bootcamp on Udemy. His teaching method is amazing and you will perceive joins in no time.

Anyhow, this issue is really a fantastic example of how and when to employ EXISTS clause:

SELECT C.Name FROM Customers C WHERE NOT EXISTS (SELECT 1 FROM Orders O WHERE C.Id = O.CustomerId)

Read more: https://javarevisited.blogspot.com/2016/01/sql-exists-example-customers-who-never-ordered.html#ixzz6O2DCsNKN

This is a correlated subquery, where the inner query will execute for every row of the outer query, and just those customers will be returned who have not ordered anything.

However, the most easy solution is by using NOT IN Clause.

SELECT A.Name FROM Customers A WHERE A.Id NOT IN (SELECT B.CustomerId FROMs Orders B)

Read more: https://javarevisited.blogspot.com/2016/01/sql-exists-example-customers-who-never-ordered.html#ixzz6O2DQWRKz

 

10 6 4
31,120 points

Related questions

0 votes
1 answer 103 views
103 views
Problem: I am little bit confused, which statement is true? In sql server, execute the sp_columns command to list all the columns in a table? True False
asked Feb 19 maddi86 5.4k points
0 votes
1 answer 33 views
33 views
Problem : I have a Stored procedure like below: CREATE PROCEDURE MyProG() BEGIN SELECT * FROM `hs_hr_employee_leave_quota`; END But it gives me the below error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 What does this error mean? What is wrong with my line number 2?
asked Jan 30 jwilliam 3.9k points
1 vote
1 answer 182 views
182 views
Problem : I am very new to SQL Queries. Recently I have joined new team and I am given below task Find the country that has all the vowels and no spaces in its name? I have written below query SELECT country FROM world WHERE country LIKE '%u'       and country LIKE ... of time in writing the correct query but still unable to do it. So I am looking for sql experts help in fixing my above query.
asked Apr 17 stewart 4k points