Mysql query WHERE in one table, but not in another

Question

EDIT: this did it:

  SELECT DISTINCT profileid 
FROM profilesrelevation 
WHERE profileid NOT IN (
  SELECT profileid FROM profiles
)

I need to get the profile values ​​that exist in the navigation table, but not in the profile table

Profile table

has 8107 different "profileid" values, whereas a profile table has 8380 different "profile" values ​​...

profiles.profileid
profilesrelevation.profileid

select * from profiles, profilesrelevation 
where profilesrelevation.profileid != profiles.profileid  

not working how?

Answer 1

You will need to use the set:

SELECT DISTINCT profileid 
FROM profilesrelevation 
WHERE profileid NOT IN (
  SELECT profileid FROM profiles
)

This selects all rows / columns from table:profilesrelevationwhere the profileidrow is not also in table:profiles:)

Updated: include distinct, because it will not be displayed in the table profilesrelevationuniquely.

Answer 2

LEFT JOIN/IS NULL

   SELECT pr.*
     FROM PROFILESREVELATION pr
LEFT JOIN PROFILES ON p.profileid = pr.profileid
    WHERE p.profileid IS NULL

NOT EXISTS

SELECT pr.*
  FROM PROFILESREVELATION pr
 WHERE NOT EXISTS(SELECT NULL
                    FROM PROFILES p
                   WHERE p.profileid = pr.profileid)

NOT IN

SELECT pr.*
  FROM PROFILESREVELATION pr
 WHERE pr.profileid NOT IN (SELECT p.profileid
                              FROM PROFILES p)

LEFT JOIN IS NULL MySQL, . , NOT IN NOT EXISTS .

Answer 3

SELECT
    profilesrelevation.profileid
FROM
    profilesrelevation
    LEFT JOIN profiles ON profilesrelevation.profileid = profiles.profileid
WHERE
    profiles.profileid IS NULL

(You can use SELECT DISTINCT.)