MySQL query to retrieve data from tables using inner join

Question

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.

in short you want only to delete records which is Uncatalogued only? — John Woo
– John Woo, Commented Oct 8, 2012 at 4:26
I want to delete records which have a valid label but are still mentioned as Uncatalogued .Example: A -citric A-Uncatalogued C-Uncatalogued. SO A-Uncatalogued should be deleted as A already has a valid label citrus. C has only one label Uncatalogued and no other valid labels so C should not be deleted. — Aayush
– Aayush, Commented Oct 8, 2012 at 4:30
how it is defined in data structure that A - Uncatalogued ? the best i see from data is that A-Citric, B-Drupes and C-Uncatalogued — zb'
– zb', Commented Oct 8, 2012 at 4:35

Adriaan Stander · Accepted Answer · 2012-10-08 04:39:58Z

2

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)
  )

answered Oct 8, 2012 at 4:39

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL query to retrieve data from tables using inner join

1 Answer 1

SQL Fiddle DEMO

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related