#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3

    Many to Many ( selecting the not in middle )


    Hi.

    I have a many-to-many table with lists that hold customers

    -------------- -------------- ---------------------
    list customers listCustomers
    -------------- -------------- ---------------------
    id id list_id
    name name customer_id
    -------------- -------------- -----------------------

    Now i need to have a table on the left which holds the names of the people that are in the listCustomers.. this is working

    Code:
    	SELECT * FROM werknemers 
    	INNER JOIN lijstWerknemers 
    	ON lijstWerknemers.werknemer_id = werknemers.id
    	GROUP BY werknemers.id');
    but finding the ones not in the middle list doesn't seem possible for me tried all the different joins

    Any help? thanks!
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    make a left join and then select the customers with lijstWerknemers.werknemer_id IS NULL. Or use a subquery with NOT IN or with NOT EXISTS.

    No idea which one is the most efficient.
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

    Why cant I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    120
    Rep Power
    10
    Originally Posted by Jacques1
    Hi,

    make a left join and then select the customers with lijstWerknemers.werknemer_id IS NULL. Or use a subquery with NOT IN or with NOT EXISTS.

    No idea which one is the most efficient.
    Left join usially faster
    Code:
    SELECT * 
    FROM werknemers 
    	left JOIN lijstWerknemers 
    	ON lijstWerknemers.werknemer_id = werknemers.id
    	GROUP BY werknemers.id
    GROUP BY werknemers.id in your case does not make sense.
    you have select *, and group by only one field, so it will not eluminate any dups... if it is just for keep records with same werknemers.id together use order by it takes less resources

IMN logo majestic logo threadwatch logo seochat tools logo