CODEWITHSUNDEEP
sqlms-access
c0mrade
c0mrade

Reputation: 87

SQL query showing incorrect results

I am using Microsoft Access and I have this SQL query which does all the relevant joins:

SELECT c.ID
FROM   ((((((((Cars c
               INNER JOIN Offers
                       ON c.ID = Offers.car_id)
              INNER JOIN Users u
                      ON c.owner_id = u.ID)
             INNER JOIN City
                     ON u.city_id = City.ID)
            INNER JOIN Models
                    ON c.model_id = Models.ID)
           INNER JOIN Makes
                   ON Models.make_id = Makes.ID)
          INNER JOIN Type
                  ON Models.type_id = Type.ID) ))
WHERE  ( Offers.decision <> 3 )

In my Cars table I have 1 car and in my offers table I have 3 offers for the same car which have a Offers.decision of 2 and 4, for some reason when I execute the query it shows the same car 3 times like it is going on the basis of 3 offers.

Is their a way to show cars that do not have an Offers.decision of 3?

Upvotes: 0

Views: 68

Answers (2)

BrianAtkins
BrianAtkins

Reputation: 1349

Simplify your query to just:

SELECT        c.ID
FROM            Cars c INNER JOIN
                     Offers ON c.ID = Offers.car_id

WHERE (Offers.decision <> 3)

Upvotes: 1

Glorfindel
Glorfindel

Reputation: 22631

You should not use a join but a subquery:

SELECT * FROM Cars WHERE ID NOT IN (SELECT DISTINCT car_id FROM Offers WHERE decision <> 3)

Upvotes: 1

Related Questions