CODEWITHSUNDEEP
sqlms-access
Tommaso Iovane
Tommaso Iovane

Reputation: 11

Why am i getting infinite results? should'nt it show me only 1 clienti.ID_Cliente?

i need a query that takes in ID_Offerta as input and returns all Cantieri of the Cliente that is linked with the Offerta. these are the relationships: https://i.gyazo.com/490cd3085c412b7fcc91317d71705ee3.png

this is the query:

SELECT DISTINCTROW Offerte.ID_offerta, Cantieri.ID_cantiere, Clienti.ID_Cliente
FROM Offerte,
     Cantieri,
     Clienti
WHERE (((Cantieri.ID_Cliente) = (SELECT DISTINCT MAX(Cantieri.ID_Cliente)
                                 FROM Cantieri,
                                      Offerte
                                 WHERE Offerte.ID_Cantiere = Cantieri.ID_Cantiere
                                   AND Offerte.ID_Offerta=[ins])));

and this is what i get: https://i.gyazo.com/7526cf11681c3828723e25cd24578c8d.png

thanks and sorry about the pizza and mandolino language

Upvotes: 0

Views: 43

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Use INNER JOIN!!!

SELECT o.ID_offerta, c.ID_cantiere, cl.ID_Cliente
FROM (Offerte as o INNER JOIN
      Cantieri as c
      ON o.ID_Cantiere = c.ID_Cantiere
     ) INNER JOIN
     Clienti as cl
     ON cl.ID_Cliente = c.ID_Cliente
WHERE o.ID_Offerta = [ins];

I removed the DISTINCT, because it is probably not necessary with the correct JOIN. However, if it is still necessary, then include it.

You can probably simplify this query further by removing Clienti; the only column being used is already in Cantiere:

SELECT o.ID_offerta, c.ID_cantiere, c.ID_Cliente
FROM Offerte as o INNER JOIN
     Cantieri as c
     ON o.ID_Cantiere = c.ID_Cantiere
WHERE o.ID_Offerta = [ins];

Upvotes: 0

Eponyme Web
Eponyme Web

Reputation: 976

I believe you have a cartesian product between Offerte, Cantieri and Clienti

SELECT DISTINCTROW Offerte.ID_offerta, Cantieri.ID_cantiere, Clienti.ID_Cliente
FROM Offerte,
     Cantieri,
     Clienti
WHERE (((Cantieri.ID_Cliente) = (SELECT DISTINCT MAX(Cantieri.ID_Cliente)
                                 FROM Cantieri,
                                      Offerte
                                 WHERE Offerte.ID_Cantiere = Cantieri.ID_Cantiere
                                   AND Offerte.ID_Offerta=[ins])))
AND Clienti.ID_Cliente = Cantieri.ID_Cliente
AND Offerte.ID_Offerta=[ins]

But from your initial description I'd go with this

SELECT DISTINCT Offerte.ID_offerta, Cantieri.ID_cantiere, Clienti.ID_Cliente
FROM Offerte,
     Cantieri,
     Clienti
WHERE Offerte.ID_Offerta=[ins]
AND Cantieri.ID_Cantiere = Offerte.ID_Cantiere
AND Clienti.ID_Cliente = Cantieri.ID_Cliente

Upvotes: 1

Related Questions