CODEWITHSUNDEEP
mysqlsql
israel
israel

Reputation: 360

Not Equal Search With LeftJoin

I have a query 

Select 
    m.name 

from machines m 
    innerJoin config c ON m.cod = c.cod

--> where ( c.category = 'BIOS' and c.val = 'System' )  
--> and   ( c.category = 'Users' and c.val = 'Jonas' )  

group by c.cod

* MACHINES ---------------- +
| key    | name    | cod    |
* ------ + ------- + ------ +
| 1      | M1      | 23     |
| 2      | M2      | 26     |
| 3      | M3      | 27     |
+ ------ + ------- + ------ +

* CONFIG --------------------------- +
| key    | category | cod   | val    |
+ ------ + -------- + ----- + ------ +
| 1      | BIOS     | 23    | System |
| 2      | Users    | 23    | Jonas  |
| 3      | Users    | 23    | Maria  |
| 4      | BIOS     | 26    | System |
| 5      | Users    | 26    | Jonas  |
| 6      | BIOS     | 27    | System |
| 6      | Users    | 27    | Ana    |
+ ------ + -------- + ----- + ------ +

i got 

* RESULT -- +
| name      |
+ --------- +
| M1        |
| M2        |
+ --------- +

Its Great for 'Equal' search, but i don't know how i do the 'Not Equal' search for this query.

I tried:

Select 
    m.name 

from machines m 
    innerJoin config c ON m.cod = c.cod

--> where ( c.category = 'BIOS' and c.val <> 'System' )  
--> or    ( c.category = 'Users' and c.val <> 'Jonas' )  

group by c.cod

but still getting the Machine 'M1', the field with value 'Maria' match with the filter

i need to do with this filter the result:

* RESULT -- +
| name      |
+ --------- +
| M3        |
+ --------- +

how can i do it ?

Upvotes: 1

Views: 59

Answers (5)

Abdullah Dibas
Abdullah Dibas

Reputation: 1507

Try this:

SELECT name FROM machines 
WHERE name NOT IN (
SELECT m.name FROM machines m    INNER JOIN config c ON m.cod = c.cod WHERE ( c.category = 'BIOS' and c.val = 'System' )  AND ( c.category = 'Users' and c.val = 'Jonas' ) GROUP BY c.cod)

Upvotes: 2

xQbert
xQbert

Reputation: 35333

Working SQL FIDDLE

Since you need the paired value to have 2 records for the system I'd use this for the equal

This does assume that a category/cod/val is unique in Config.

SELECT m.name
FROM machines m
INNER JOIN config c
  on c.cod = m.cod
WHERE (( c.category = 'BIOS' and c.val = 'System' )  
   OR ( c.category = 'Users' and c.val = 'Jonas' ))
GROUP BY m.name
having count(*) =2 ;

and this for the not equal... note the 2 simply changes if you have a more "OR" critiera

SELECT m.name
FROM machines m
INNER JOIN config c
  on c.cod = m.cod
WHERE (( c.category = 'BIOS' and c.val = 'System' )  
   OR ( c.category = 'Users' and c.val = 'Jonas' ))
GROUP BY m.name
having count(*) <>2 ;

Upvotes: 0

Dario
Dario

Reputation: 2723

Your posted query has a repeated mistake in it: category and val come from table c, not m.

Then, it seems to me that what you want is to discard those records which refer to a machine for which other records exist with your condition. Unfortunately, this cannot be done with conditions on records alone, so it’s no use negating conditions. You have to do something like this:

SELECT DISTINCT name 
  FROM machines 
 WHERE name NOT IN (
     SELECT m.name       
       FROM machines m 
       INNER JOIN config c USING (cod)
      WHERE ( c.category = 'BIOS' AND c.val = 'System' )  
        AND ( c.category = 'Users' AND c.val = 'Jonas' )
 )

Upvotes: 1

Rajendra
Rajendra

Reputation: 191

Try the following script :

    SELECT M.Name FROM Machines AS M LEFT JOIN CONFIG AS C ON M.Cod=C.Cod 
    WHERE  NOT ( 
    (M.Category ='BIOS' AND M.Val='System') AND
    (M.Category ='Users' AND M.Val='Jonas') 
    )

NOT keyword is easy solution for your query. i also did left join instead inner, you can ignore it if not require.

Upvotes: 0

LONG
LONG

Reputation: 4620

Select 
    m.name 

from machines m 
    innerJoin config c ON m.cod = c.cod

where NOT (
( m.category = 'BIOS' and m.val = 'System' )  
 and ( m.category = 'Users' and m.val = 'Jonas' )
) 

group by c.cod

Upvotes: 1

Related Questions