Flooded with results

Databases
1

Hi,

I have this query, that gives me some data from my Joomla (test) site.
It also performs a check on a user table, to confirm if a user has succesfully been added or not.
For some reason, the more users i add, the more results i get, per user.
For example, I add a user, run the query and it gives me 4 times the same result:

User4
User4
User4
User4

When I add another user, it gives me 5 times the same result:

User4
User4
User4
User4
User5
User5
User5
User5
User5

And so on.

This is the query:

======================================================
SELECT A.FormID,
B.DateSubmitted,
B.SubmissionId as VolunteersID,
B.SubmissionId as RelationCode,

	( SELECT FieldValue 
   			FROM jos_RSFORM_SUBMISSION_VALUES 
      		WHERE FieldName = 'name'
        		AND SubmissionId = B.SubmissionId ) as name, 				
 		( SELECT FieldValue 
       		FROM jos_RSFORM_SUBMISSION_VALUES 
      		WHERE FieldName = 'adress'
        		AND SubmissionId = B.SubmissionId ) as Adress,
	( SELECT FieldValue 
       		FROM jos_RSFORM_SUBMISSION_VALUES 
      		WHERE FieldName = 'PostalCode'
        		AND SubmissionId = B.SubmissionId ) as PostalCode

FROM jos_RSFORM_FORMS A

INNER
JOIN jos_RSFORM_SUBMISSIONS B
ON B.FormId = A.FormId

INNER
JOIN jos_RSFORM_SUBMISSION_VALUES C
ON C.SubmissionId = B.SubmissionId

INNER
JOIN jos_USERS D
on D.name = C.Fieldvalue
AND D.Block = ‘0’

WHERE A.FormId = 2

I would really appreciate some kick into the right direction.
Thanks in advance,

Mesjoggah

2

Try adding DISTINCT to your SELECT query:

SELECT DISTINCT A.FormID,
B.DateSubmitted,
B.SubmissionId as VolunteersID,
B.SubmissionId as RelationCode,

3

Thanks a lot, JeffWalden,
That did the trick !

4

Stellar! Now, keep in mind that (as I’m not a SQL expert) that this may be a band-aid for the problem rather than an efficient solution. What I mean is that there may be an inefficiency with your query itself that is causing the rows to duplicate. Fixing that inefficiency would be the best solution to this problem. Using DISTINCT works, but it may not actually address the problem, only the symptoms. I’m sure someone on here with more SQL knowledge than myself will blow my opinion out of the water. :smiley: