CODEWITHSUNDEEP
mysqlinner-join
Charles Okaformbah
Charles Okaformbah

Reputation: 75

MYSQL Inner join for three to four tables

Please I need to figure out what I am doing wrong. I created this inner join code for mysql. it works but it gives me repeated values like repeating a particular row twice or categoryid twice. each of the tables(users,paymentnotification,monthlyreturns) has the categoryid used to check and display the username(users.pname) from the user table, then check and display those that have made payment from the monthly returns and payment table using the categoryid.

$r="SELECT monthlyreturns.categoryid, monthlyreturns.month, monthlyreturns.quarter, monthlyreturns.year,paymentnotification.amount, users.pname, monthlyreturns.ototal, paymentnotification.payee, status
FROM paymentnotification 
INNER JOIN (monthlyreturns INNER JOIN users ON monthlyreturns.categoryid=users.categoryid) 
ON monthlyreturns.categoryid=paymentnotification.categoryid 
ORDER BY monthlyreturns.categoryid DESC";

Upvotes: 0

Views: 8894

Answers (4)

Bidesh Jana
Bidesh Jana

Reputation: 1

SELECT M.categoryid, M.month, M.quarter, M.year, M.ototal,

   P.amount, P.payee, P.status,

   U.pname

FROM paymentnotification AS P

INNER JOIN monthlyreturns AS M ON P.categoryid = M.categoryid

INNER JOIN users AS U ON M.categoryid = U.categoryid

ORDER BY M.categoryid DESC

Upvotes: 0

Nick Rolando
Nick Rolando

Reputation: 26167

Assuming I understand what you're trying to do, you are not joining your tables properly. Try joining one at a time

SELECT DISTINCT monthlyreturns.categoryid, monthlyreturns.month, monthlyreturns.quarter, monthlyreturns.year,paym entnotification.amount, users.pname, monthlyreturns.ototal, paymentnotification.payee, status
FROM paymentnotification 
INNER JOIN monthlyreturns
ON paymentnotification.categoryid = monthlyreturns.categoryid
INNER JOIN users 
ON monthlyreturns.categoryid = users.categoryid
ORDER BY monthlyreturns.categoryid DESC

Upvotes: 1

BeNdErR
BeNdErR

Reputation: 17929

I don't see any problem.. I get 4 result rows: check this fiddle http://sqlfiddle.com/#!2/165a22/5

this is the query I used:

SELECT m.categoryid, m.month, m.quarter, m.year,p.amount, u.pname, m.ototal, p.payee, m.status
FROM paymentnotification p JOIN monthlyreturns m ON p.categoryid = m.categoryid
JOIN users u ON u.categoryid = m.categoryid
ORDER BY m.categoryid DESC

there are no duplicated rows, just "unique" rows if you consider every column you choose.

Hope it helps

Upvotes: 0

Havenard
Havenard

Reputation: 27854

I think the query you want is more like this:

SELECT b.categoryid, b.month, b.quarter, b.year, a.amount, c.pname, b.ototal, a.payee, status
FROM paymentnotification a
INNER JOIN monthlyreturns b
   ON a.categoryid = b.categoryid
INNER JOIN users c
   ON b.categoryid = c.categoryid
ORDER BY b.categoryid DESC

The way you are doing the correlations doesn't seem clear and may cause problems. Try this one out and see what happens. If its still doing duplicates, perhaps the nature of the data require further filtering.

Upvotes: 2

Related Questions