Make an INNER JOIN using the IF

Question

Make an INNER JOIN using the IF

Navigation

#1 by (6 votes)

2

I'm doing an INNER JOIN from the Combo Table with the Product Combo Table as I show below ...

    SELECT C.SchoolID, C.ComboID, P.Active, P.Name, P.ProductID
    FROM sales.Combo AS C
    INNER JOIN commons.Stock AS S ON C.SchoolID = S.SchoolID AND C.ProductID = S.ProductID 
    INNER JOIN commons.Product AS P ON C.SchoolID = P.SchoolID AND C.ProductID = P.ProductID
    WHERE        (C.SchoolID = 01)

It returns me for example the following values:

SchoolID___|ComboID___|Active___|Produto

01_________|01________|True______|01

01_________|01________|False_____|02

01_________|02________|True______|01

01_________|02________|True______|05

I am trying to modify this my Query so that if 1 of the Combo products is as Active = False it does not select the ComboID that has this product = false

I tried to use the IF but I did not succeed ...

sql if select

asked by anonymous 16.04.2014 / 18:55

1 answer

Is it possible to make a file upload system without using PHP? Design tool that exports to .xib or .storyboard

score 6 · Accepted Answer

One of the ways to solve this case is to use a View / Subselect:

SELECT C.SchoolID, C.ComboID, P.Active, P.Name, P.ProductID
    FROM sales.Combo AS C
    INNER JOIN commons.Stock AS S ON C.SchoolID = S.SchoolID AND C.ProductID = S.ProductID 
    INNER JOIN commons.Product AS P ON C.SchoolID = P.SchoolID AND C.ProductID = P.ProductID
    WHERE        (C.SchoolID = 01) 
     AND C.COMBOID NOT IN (SELECT DISTINCT C.COMBOID FROM sales.Combo AS C
     INNER JOIN commons.Stock AS S ON C.SchoolID = S.SchoolID AND C.ProductID =  S.ProductID
     INNER JOIN commons.Product AS P ON C.SchoolID = P.SchoolID AND C.ProductID = P.ProductID
     WHERE (C.SchoolID = 01) AND (P.ACTIVE = FALSE))