Querry returning multiple results

[dniezby]

I created a query and got it to work but for some reason it reports each record like 20 times?

[keme]

Probably a cross join, i.e. joining two tables without a join condition (field relationship).
Do you get each line repeated the same number of times?
If so, is that equal to the number of records in the jos_users table?

[dniezby]

It is equal to the number of active users in the subscr table

[Sliderule]

dniezby:

Below is the SQL code you wrote from your first post. I am entering it as 'raw text' rather than surround by by code delimiters, so I could add some color for emphasis.

= = = = = = = = = = = = = = = = = = = = = = = = = = =

SELECT
`jos_users`.`username`,
`jos_users`.`name`,
`jos_acctexp_subscr`.`status`

FROM `freeroll_main`.`jos_acctexp_subscr` AS `jos_acctexp_subscr`,
`jos_users` AS `jos_users`

WHERE `jos_acctexp_subscr`.`status` = "Active"

ORDER BY `freeroll_main`.`jos_users`.`username`

= = = = = = = = = = = = = = = = = = = = = = = = = = =

Problem:Now, you are telling your database engine, that you only want to bring back records `status` = "Active" . BUT, since you did not tell the database engine how the two tables are related:

1. `freeroll_main`.`jos_acctexp_subscr` AS `jos_acctexp_subscr`
   
2. `jos_users` AS `jos_users`

Solution: You MUST include as a part of the WHERE statement, HOW to relate the two tables. You have NOT included in your first comment, the field names from your tables, NOR, how they should be related, I am just giving a MADE up example. The code I am adding ( without knowing your field names ) is in dark red below:

= = = = = = = = = = = = = = = = = = = = = = = = = = =

SELECT
`jos_users`.`username`,
`jos_users`.`name`,
`jos_acctexp_subscr`.`status`

FROM `freeroll_main`.`jos_acctexp_subscr` AS `jos_acctexp_subscr`,
`jos_users` AS `jos_users`

WHERE `jos_acctexp_subscr`.`status` = "Active"
and `jos_acctexp_subscr`.`subscr_ID` = `jos_users`.`users_ID`

ORDER BY `freeroll_main`.`jos_users`.`username`

= = = = = = = = = = = = = = = = = = = = = = = = = = =

Explanation: WithOUT inclduing the relationship between your two tables in the WHERE clause, for each record it finds WHERE `jos_acctexp_subscr`.`status` = "Active", it will INCLUDE all the records in the other table.

IMPORTANT NOTE: If you are using the GUI ( Graphic User Interface ) to create your Query ( under Tasks, Create Query in Design View... ) . . . after you have selected your Tables, in the UPPER HALF . . . draw the LINK between the appropriate fields in your tables by clicking on the field from your first table, holding down the left mouse button, and, dragging the line to the appropriate field on the second table.

For a GRAPHIC . . . see the link below ( from 2007 ) by Sliderule:

http://www.oooforum.org/forum/viewtopic.phtml?t=51739

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.