-1

I am trying to compile a list of "None Sellers", I am getting a list of distinct seller_id's and comparing that to the "list of sellers" aka account_manager_sellers using the NOT IN clause, but this is not working for me.

Table plans:

+----+-----------+
| id | seller_id |
+----+-----------+
|  1 |      1001 |
|  2 |      1002 |
|  3 |      1002 |
|  4 |      1001 |
|  5 |      1005 |
+----+-----------+

Table account_manager_sellers:

+------+--------------+
|  id  | persons_name |
+------+--------------+
| 1001 | name_1       |
| 1002 | name_2       |
| 1003 | name_3       |
| 1004 | name_4       |
| 1005 | name_5       |
+------+--------------+

Expected Result:

+------+--------------+
|  id  | persons_name |
+------+--------------+
| 1003 | name_3       |
| 1004 | name_4       |
+------+--------------+


SELECT DISTINCT(p.seller_id) FROM plans p 
WHERE p.seller_id NOT IN (
    SELECT a.id FROM account_manager_sellers a
)

This snippet is running but not returning any results.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Peter Bennett
  • 3
  • 3
  • Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) Try create a sample in http://rextester.com – Juan Carlos Oropeza May 08 '18 at 15:27
  • Based on the info you provided, the syntax of the query should work. So this leaves several possibilities -- that account_manager_sellers.id is not the same as seller.id, or that the 2 lists of id's are exactly the same. You should investigate by running each query independently and looking at them. I suspect that account_manger_sellers might have a seperate foreign key that is named seller_id possibly. – gview May 08 '18 at 15:30
  • `I don't think sample data is required.` well if you know everything then why are you asking here? The query looks ok, so the problem is the data, or you are query the wrong fields – Juan Carlos Oropeza May 08 '18 at 15:31
  • @JuanCarlosOropeza updated with sample data. – Peter Bennett May 08 '18 at 15:36
  • btw, `database` is a collection of tables and functions. Your examples are `Tables` – Juan Carlos Oropeza May 08 '18 at 15:48
  • 1
    Possible duplicate of [SQL - find records from one table which don't exist in another](https://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) – Paul Spiegel May 08 '18 at 15:49

1 Answers1

2

Are you doing things backwards?

SQL DEMO:

SELECT  a.id, persons_name 
FROM account_manager_sellers a 
WHERE a.id NOT IN (
    SELECT DISTINCT p.seller_id FROM plans p
);

OUTPUT

|   id | persons_name |
|------|--------------|
| 1003 |       name_3 |
| 1004 |       name_4 |
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • After quite a while, this returns: MySQL returned an empty result set (i.e. zero rows). (Query took 137.6775 seconds.) – Peter Bennett May 08 '18 at 15:53
  • You need create proper index for your table to improve performance. Provide the create table ddl so we can give you suggestion. Also I create a working demo using your sample data and is returning your desire output. So the problem is your data. – Juan Carlos Oropeza May 08 '18 at 15:56
  • Ok I have managed to make it work, however, you asked me if I was doing things backwards, maybe yeah, however, think about it like this. The `plans` database will have over 100,000 records, where the `account_manager_sellers` or `a_m_s` for short has only around 5000 records, surely it would be wise to get the DISTINCT values from the `plans` database to compare against the `a_m_s` database? – Peter Bennett May 09 '18 at 08:39
  • Ywa, because that is what you requirement need – Juan Carlos Oropeza May 09 '18 at 17:57