Mulitple Table Query Help

**Ralph Freshour** · Jul 16 '05, 11:41 PM

Re: Mulitple Table Query Help

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.f r> wrote:
[color=blue]
>Ralph Freshour wrote:[color=green]
>> 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 ".[/color]
>
><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 :
>[color=green]
>> 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:[/color]
>
>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[/color]

Mulitple Table Query Help

Mulitple Table Query Help

Comment