CODEWITHSUNDEEP
sqlmysql
Woody
Woody

Reputation: 5130

Mysql many to many query

Having a mental block with going around this query.

I have the following tables:

review_list: has most of the data, but in this case the only important thing is review_id, the id of the record that I am currently interested in (int) variant_list: model (varchar), enabled (bool)
variant_review: model (varchar), id (int)

variant_review is a many to many table linking the review_id in review_list to the model(s) in variant_list review and contains (eg):

..  
test1,22
test2,22
test4,22
test1,23
test2,23... etc

variant_list is a list of all possible models and whether they are enabled and contains (eg):

test1,TRUE
test2,TRUE
test3,TRUE
test4,TRUE

what I am after in mysql is a query that when given a review_id (ie, 22) will return a resultset that will list each value in variant_review.model, and whether it is present for the given review_id such as:

test1,1
test2,1
test3,0
test4,1

or similar, which I can farm off to some webpage with a list of checkboxes for the types. This would show all the models available and whether each one was present in the table

Upvotes: 1

Views: 162

Answers (1)

Thomas
Thomas

Reputation: 64635

Given a bit more information about the column names:

Select variant_list.model
    , Case When variant_review.model Is Not Null Then 1 Else 0 End As HasReview
From variant_list
    Left join variant_review
        On variant_review.model = variant_list.model
            And variant_review.review_id = 22

Just for completeness, if it is the case that you can have multiple rows in the variant_review table with the same model and review_id, then you need to do it differently:

Select variant_list.model
    , Case 
        When Exists (
                    Select 1
                    From variant_review As VR
                    Where VR.model = variant_list.model
                        And VR.review_id = 22
                    ) Then 1
        Else 0
        End 
From variant_list

Upvotes: 2

Related Questions