MySQL select records from one table that don't exist in another table

Question

I have two tables on two different databases.

I wish to select the values from the field MLS_LISTING_ID from the table mlsdata if they do not exist in the table ft_form_8.

There are a total of 5 records in the mlsdata table. There are 2 matching records in the ft_form_8 table.

Running this query, I receive all 5 records from mlsdata instead of 3.

Changing NOT IN to IN, I get the 2 matching records that are in both tables.

Any ideas?

SELECT DISTINCT
    flrhost_mls.mlsdata.MLS_LISTING_ID
FROM
    flrhost_mls.mlsdata
        INNER JOIN
    flrhost_forms.ft_form_8 ON flrhost_mls.mlsdata.MLS_AGENT_ID = flrhost_forms.ft_form_8.nar_id
WHERE
    flrhost_mls.mlsdata.MLS_LISTING_ID NOT IN ((SELECT flrhost_forms.ft_form_8.mls_id))
        AND flrhost_mls.mlsdata.MLS_AGENT_ID = '260014126'
        AND flrhost_forms.ft_form_8.transaction_type = 'listing'

score 2 · Answer 1 · answered May 23 '13 at 15:52

2

SELECT DISTINCT
    flrhost_mls.mlsdata.MLS_LISTING_ID
FROM
    flrhost_mls.mlsdata
where
    flrhost_mls.mlsdata.MLS_LISTING_ID NOT IN (SELECT 
            flrhost_forms.ft_form_8.mls_id
        FROM
            flrhost_forms.ft_form_8)

answered May 23 '13 at 15:52

Faishal

1,493
1
14
23

score 2 · Answer 2 · answered May 23 '13 at 21:39

Your problem:

I wish to select the values from the field MLS_LISTING_ID from the table mlsdata if they do not exist in the table ft_form_8.

is THE classic OUTER JOIN problem

SELECT DISTINCT
  flrhost_mls.mlsdata.MLS_LISTING_ID, flrhost_forms.ft_form_8.MLS_LISTING_ID
 FROM 
   flrhost_mls.mlsdata
 LEFT OUTER JOIN
   flrhost_forms.ft_form_8
 ON
   flrhost_mls.mlsdata.MLS_LISTING_ID=flrhost_forms.ft_form_8.MLS_LISTING_ID
 WHERE
   flrhost_forms.ft_form_8.MLS_LISTING_ID IS NULL;

Which is much simpler than using some complicated subquery nest.

score 0 · Accepted Answer · answered May 23 '13 at 16:05

SELECT DISTINCT
  flrhost_mls.mlsdata.MLS_LISTING_ID
FROM flrhost_mls.mlsdata
INNER JOIN flrhost_forms.ft_form_8 
ON flrhost_mls.mlsdata.MLS_AGENT_ID = flrhost_forms.ft_form_8.nar_id  
WHERE flrhost_mls.mlsdata.MLS_AGENT_ID = '260014126'
  AND flrhost_forms.ft_form_8.transaction_type = 'listing' 
  AND flrhost_mls.mlsdata.MLS_LISTING_ID NOT IN (SELECT b.mls_id FROM flrhost_forms.ft_form_8 b)

MySQL select records from one table that don't exist in another table

3 Answers3