anyName anyName - 2 years ago 52
MySQL Question

Multiple table query with wrong rows

I want to get a query from two tables but the resulting query has more rows than it should be.

My statement is:

SELECT * FROM deal D, item I WHERE D.dealstate = 'ACCEPTED' AND
((D.owner = 'UserName1') OR
(D.itemID = I.itemID AND I.owner = 'UserName1')) AND
D.deadline < NOW()

With this statement I want to get a user's active deals. Deal table has
owner (the requester), dealID, itemID, creationdate, dealstate, deadline, explanation
and Item table has
owner (the owner of item), itemID, itemname, description

The problem is that I want a query with one result. Because 'UserName1' has only 1 deal. However i get a query with 10 rows, each of items matched with the deal. I thought using "JOIN" but if the user is not the owner of item, this ITEM table won't be used. Then, what should i do?

I hope I'm clear enough.

Answer Source

Your query doesn't join correctly the two tables because D.itemID = I.itemID isn't always true in what you wrote. Try something like

SELECT * FROM deal D, item I WHERE D.dealstate = 'ACCEPTED' AND D.itemID = I.itemID AND ((D.owner = 'UserName1') OR ( I.owner = 'UserName1')) AND D.deadline < NOW();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download