Saturday, 31 August 2013

MySQL query displaying more data than expected

MySQL query displaying more data than expected

I have MySql database connected to my Java app. My users can choose meals,
category of meals, quantity, write note and order that meals. I store that
orders in table usluga_hrana. At the moment I am working only with meals
service but I have other services too, like drink service, wake up service
and others. Thats why I need one more table for all orders from different
services and its called narudzba. Now I need to display values of all
these atributes in one query: broj_sobe (room number, table narudzba),
id_narudzba (id order, table narudzba), naziv_kategorija (category name,
table kategorija_jela), naziv_hrane (name of meal, table naziv_jela),
kolicina (quantity, table usluga_hrana), napomena (note, table
usluga_hrana), datum_vrijeme (date and time, table usluga_hrana) and
izvrseno (done, table narudzba). Problem is that all these atributes are
in different tables and when I execute my query, it displays me multiple
values of orders, meals etc.
My tables are connected this way:
PK id_usluga (table usluga) is FK id_usluga in table narudzba
PK id_usluga (table usluga) is FK id_usluga in table usluga_hrana PK
id_kategorija (table kategorija_jela) is FK id_kategorija in table
usluga_hrana
PK id_hrana (table naziv_jela) is FK id_hrana in table usluga_hrana PK
id_kategorija (table kategorija_jela) is FK id_kategorija in table
naziv_jela
Here is album of my tables and result of my query with multiple values:
http://imgur.com/a/6grPN
Here is album with rest of my tables: http://imgur.com/a/sFPie
Here is my query:
SELECT n.broj_soba, n.id_narudzba, kj.naziv_kategorija, nj.naziv_hrane,
us.kolicina,
us.napomena, us.datum_vrijeme, n.izvrseno
FROM narudzba n
JOIN usluga u ON n.id_usluga = u.id_usluga
JOIN usluga_hrana us ON u.id_usluga = us.id_usluga
JOIN naziv_jela nj ON us.id_jela = nj.id_jela
JOIN kategorija_jela kj ON nj.id_kategorija = kj.id_kategorija
GROUP BY n.id_narudzba, us.id_usluga_hrana

No comments:

Post a Comment