CODEWITHSUNDEEP
mysqljoin
Aayush
Aayush

Reputation: 1254

MySQL query to retrieve data from tables using inner join

These are the two tables Fruit and Fruit_types.There is a m:n relationship between the two tables so we have the third table fruit_type_fruit which has the primary key from the above two tables. The tables look like this

Fruit
ID    NAME    
1    A
2    B
3    C

Fruit_type
ID    LABEL
1    CITRIC
2    DRUPES
3    UNCATALOGUED

Fruit_type_Fruit
Fruit_id    Fruit_type
1        1
1        2
1        3
2        1
3        3

Problem Statement: Some fruits even though they have a category(i.e label) get label as Uncatalogued.

For ex:-
A gets the following labels : Citric, drupes and uncatalogued. 

    B has    citric ,

    C has    Uncatalogued.

Now I need a query to delete all the records which have a suitable label but still have uncatalogued label too. In the above example

A record which is uncatalogued should be deleted and not

A    Citric and    Drupes neither

C    Uncatalogued.

Upvotes: 3

Views: 196

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166346

How about something like this

SQL Fiddle DEMO

DELETE ftf
FROM fruit_type_fruit ftf
WHERE Fruit_type_ID = 3
AND Fruit_ID IN
    (
            SELECT *
            FROM (
                    SELECT DISTINCT Fruit_ID
                    FROM fruit_type_fruit f 
                    WHERE f.Fruit_type_ID = 3
             ) ss
            WHERE Fruit_ID IN (
                    SELECT *
                    FROM (
                    SELECT DISTINCT Fruit_ID
                    FROM fruit_type_fruit f 
                    WHERE f.Fruit_type_ID <> 3
             ) s)
  )

Upvotes: 2

Related Questions