• Register
0 votes
143 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

Please log in or register to answer this question.

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 5 views
5 views
Problem: I want to do a Full Outer Join in MySQL. Is this possible? Is a Full Outer Join supported by MySQL?
asked Apr 25 sumaiya simi 43.9k points
0 votes
1 answer 33 views
33 views
Problem: How to get multiple counts with one SQL query? Thanks
asked Mar 9 ummesalma 29.2k points
0 votes
1 answer 9 views
9 views
Problem: I want to do a Full Outer Join in MySQL. Is this possible? Is a Full Outer Join supported by MySQL?
asked Apr 30 ochena 23.3k points
0 votes
1 answer 5 views
5 views
Problem: Let's say I want to do an INNER JOIN between two entities Foo and Bar: @Query("SELECT * FROM Foo INNER JOIN Bar ON Foo.bar = Bar.id") List<FooAndBar> findAllFooAndBar(); Is it possible to force a return type like this? public class FooAndBar { ... didn't work either. If this isn't possible, how should I cleanly construct a compatible return type that includes all fields for both entities?
asked 5 days ago jamuna1 30.2k points
0 votes
1 answer 6 views
6 views
Problem: Here is the link to the table and detailed description of question 11 on SQLZOO Join: sqlzoo.net/wiki/The_JOIN_operation. The question is asking 'For every match involving 'POL', show the matchid, date and the number of goals scored.' Below is my SQL code SELECT ... why here I have to group by both matchid and mdate instead of just one of them? Thank you in advance for your kind help.
asked Apr 25 muktaa 34.6k points
0 votes
1 answer 2 views
2 views
Problem: Is anyone available to assist me and resolve this issue? What is inner join and outer join in sql?
asked 5 days ago Ifra 36.4k points
0 votes
1 answer 8 views
8 views
Problem: What is the difference between union and join in sql.
asked Mar 14 Ethan ross 2.7k points
0 votes
1 answer 3 views
3 views
Problem: I am trying to reset password for root user and I have successfully executed below commands service mysql stop Created and saved a text file /home/me/mysql-init containing the following statement on a single line. SET PASSWORD FOR 'root'@'localhost' = ... Aborting I am using Mysql Version 5.6 and following the given documentation dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html
asked Apr 26 sumaiya simi 43.9k points
0 votes
1 answer 60 views
60 views
Problem: What would be the right query for this one > write a query to display the names of customers who have ordered more than 2 times.
asked Feb 28 Azlan2020 2.4k points
0 votes
1 answer 4 views
4 views
Problem: Please can anyone tell me the right procedure to do it? Right and full outer joins are not currently supported
asked Apr 26 Ifra 36.4k points