How connect two select?

Question

I have a problem with select from two table.

I use filtr from date, when I used NOT IN but didn't work, because data to result not in second table. I want display outfits which is free in these date and outfits which not in table rent.

Structure of base in the picture:

SELECT o.Id, o.Name, c.Name, o.Description,o.Price, r.Date1, r.Date2, r.Return 
FROM Outfit o INNER JOIN
     Category c
     ON o.Category = c.Id INNER JOIN
     Rent r
     ON o.Id = r.OutFit 
WHERE (myfiltrdate NOT BETWEEN r.Date1 AND r.Date2) OR
      r.Return IS NOT NULL

Now my result are only record/outfit which are Rent table, but I want this result and Outfit which are not in table Rent

Answer 1

You may try a left join -

SELECT o.Id, o.Name, c.Name, o.Description,o.Price, r.Date1, r.Date2, r.Return 
FROM Outfit o LEFT JOIN
     Category c
     ON o.Category = c.Id INNER JOIN
     Rent r
     ON o.Id = r.OutFit 
WHERE (myfiltrdate NOT BETWEEN r.Date1 AND r.Date2) OR
      r.Return IS NULL

Answer 2

I think you want LEFT JOINs and filtering. Something like this:

SELECT o.Id, o.Name, c.Name, o.Description, o.Price,
       r.Date1, r.Date2, r.Return 
FROM Outfit o LEFT JOIN
     Category c
     ON o.Category = c.Id LEFT JOIN
     Rent r
     ON o.Id = r.OutFit AND
        (myfiltrdate BETWEEN r.Date1 AND r.Date2)
WHERE r.Return IS NULL;

Answer 3

Change your query to left join and filter the dates like this:

    SELECT o.Id, o.Name, c.Name, o.Description,o.Price, r.Date1, r.Date2, r.Return 
    FROM Outfit o 
    INNER JOIN Category c ON o.Category = c.Id 
LEFT JOIN Rent r ON o.Id = r.OutFit
    WHERE  (myfiltrdate < r.Date1 AND myfiltrdate > r.Date2) OR
  r.Return IS NOT NULL

I don't have clear if Return is a date which the outfit is returned or is a boolean but I suspect that the condition must be

And r.Return IS NOT NULL