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_name = personal.member_name ".
"AND basics.member_name = photos.member_name ".
"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_name = personal.member_name ".
"AND basics.member_name = photos.member_name ".
"AND basics.member_name = online.member_name ".
"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
<bdesth.nospam@removeme.free.fr> 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_name = personal.member_name ".
>> "AND basics.member_name = photos.member_name ".
>> "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_name = personal.member_name ".
>> "AND basics.member_name = photos.member_name ".
>> "AND basics.member_name = online.member_name ".

>
><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.databases would have been a better place IMHO.
>
>Bruno