OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Querry returning multiple results

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
dniezby
OOo Enthusiast
OOo Enthusiast


Joined: 01 May 2004
Posts: 102

PostPosted: Sat Jun 25, 2011 12:05 am    Post subject: Querry returning multiple results Reply with quote

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

Code:
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`


It's supposed to check the status of a person's subscription then return the results of all of the active members. It does that but it repeats each record like 20 times.

It will return something like:

Username | Name | Staus

Code:
Admin | Dave | Active
Admin | Dave | Active
Admin | Dave | Active
Admin | Dave | Active
Admin | Dave | Active
Admin | Dave | Active
user1|Joe|Active
user1|Joe|Active
user1|Joe|Active
user1|Joe|Active


Any idea on what I did wrong as I obviously don't have 20 records for each member. I've had this issue before. Just don't remember how I fixed it.

Thanks in advance.
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Sat Jun 25, 2011 3:55 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
dniezby
OOo Enthusiast
OOo Enthusiast


Joined: 01 May 2004
Posts: 102

PostPosted: Sat Jun 25, 2011 6:22 am    Post subject: Reply with quote

It is equal to the number of active users in the subscr table
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sat Jun 25, 2011 7:31 am    Post subject: Reply with quote

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. Smile

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

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. Smile

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. Smile

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group