Mulitple Table Query Help

[Ralph Freshour]

I'm not sure the follow multiple table query is the right way to do
what I need to do although it seems to be working:

$php_SQL = "SELECT * ".
"FROM basics, personal, photos ".
"WHERE basics.member_n ame = personal.member _name ".
"AND basics.member_n ame = photos.member_n ame ".
"AND basics.account_ creation_date >= DATE_SUB(NOW(),
INTERVAL 30 DAY)";

I primarily need to return a resultset for all member_names (they are
index key and unique) filtered for the last 30 days on the
basics.account_ creation_date - this 30 day thing is working fine.

I need to access various other table fields and display this data on
the web page - member_name is the common key for all tables - this all
seems to be working fine as I have 8 test records and can manually
track and see that it is working - the problem is that I need to add a
4th table and in doing so, the resultset breaks and returns just one
record - I am adding an online table with a field called is_online
which is set to 'yes' if the member is online but the following query
returns just the one record of the online member:

$php_SQL = "SELECT * ".
"FROM basics, personal, photos, online ".
"WHERE basics.member_n ame = personal.member _name ".
"AND basics.member_n ame = photos.member_n ame ".
"AND basics.member_n ame = online.member_n ame ".
"AND basics.account_ creation_date >= DATE_SUB(NOW(),
INTERVAL 30 DAY)";

It seems to me this should be an easy thing to just add the 4th table
but since this breaks the query now I'm wondering if this query
structure is even built right?

Any help would be greatly appreciated...

[Ralph Freshour]

Thanks for the comments -

1. Yes I figured out that my online table was wrong, it needed all
member_names in it - as you pointed out - that was indeed why it was
resulting in one row - so I got that fixed and it works as expected
now. Previously I was using that online table in another way and
changed the way I use it and didn't realize it needed all the other
member names in it.

2. Yes, I wasn't sure about my post being in here - sometimes help is
given in php (although not in this case) on mysql issues - but thanks
for the comment and thanks for the table help.
On Wed, 03 Sep 2003 02:05:44 +0200, Bruno Desthuilliers
<bd***********@ removeme.free.f r> wrote:

Ralph Freshour wrote:

I'm not sure the follow multiple table query is the right way to do
what I need to do although it seems to be working:

$php_SQL = "SELECT * ".
"FROM basics, personal, photos ".
"WHERE basics.member_n ame = personal.member _name ".
"AND basics.member_n ame = photos.member_n ame ".
"AND basics.account_ creation_date >= DATE_SUB(NOW(),
INTERVAL 30 DAY)";

I primarily need to return a resultset for all member_names (they are
index key and unique)
filtered for the last 30 days on the
basics.account_ creation_date - this 30 day thing is working fine.

I need to access various other table fields and display this data on
the web page - member_name is the common key for all tables - this all
seems to be working fine as I have 8 test records and can manually
track and see that it is working - the problem is that I need to add a
4th table and in doing so, the resultset breaks and returns just one
record - I am adding an online table with a field called is_online
which is set to 'yes' if the member is online but the following query
returns just the one record of the online member:

$php_SQL = "SELECT * ".
"FROM basics, personal, photos, online ".
"WHERE basics.member_n ame = personal.member _name ".
"AND basics.member_n ame = photos.member_n ame ".
"AND basics.member_n ame = online.member_n ame ".

<OT>
This line above tells the DB to inner-join basics and online on the
member_name field. So if there is only one record in online, it is quite
normal that you only get this record in your resultset.

I guess that you've got corresponding records in personal and photos for
all records in basics, else you would have spot the problem sooner.

Now the result may not be what you expect, but as we dont know for sure
what you expect, it's hard to tell you how you could get it.

BTW, could it be possible that there is a little mistake in your db schema ?

You state that :

I am adding an online table with a field called is_online
which is set to 'yes' if the member is online but the following query
returns just the one record of the online member:

If your schema is something like :
basics(*member _name*, ...)
online(*member _name*, yes_no)

then it's broken. You should have a field 'online' in basics, and no
online table table.

Or did I miss something ?
</OT>

Oh, and BTW, your question is a bit off-topic here, since it has nothing
to do with php !-)

comp.databas es would have been a better place IMHO.

Bruno