Make an INNER JOIN using IF

Asked

Viewed 1,143 times

2

I am doing an INNER JOIN of the Combo Table with the Product Combo Table as shown 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 using IF but was unsuccessful...

sql select if
  • 1

    You could not add in "AND Active IS TRUE" ?

  • If I add "AND Active IS TRUE" it will continue selecting the Combo because the other Combo product is like "TRUE".

1 answer

6


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

  • It worked like this! VLW...

Browser other questions tagged sql select if

You are not signed in. Login or sign up in order to post.