[thelist] Mysql/PHP Searching WAS: Sql join problem

partyarmy partyarmy at gmail.com
Thu Dec 9 16:33:55 CST 2004


Ok, tried what people submitted. Nothing worked as expected. So maybe
a clarification is in order.

I have two tables, one is a members table which holds personal
information about users registered on my site. The other table is a
profile table which conatins the registered users profile data.

In the profile table, I have a member_id field which relates to the
index of my members table.

Now, the part i'm having trouble with is searching through both those
tables at once. The members table for email_address, username,
location and gender; and the profile table for keywords in the profile
data.

Possible searches could be, all females in the us, or all males who
like hockey as a hobby, all users with fart in there email_address and
whose favorite movies include little mermaid and fieval goes west,
etc...

Is there a way to do this search without making a temporary table?
I've tried reading tutorials on mysql joins, but havn't found one
dumbed down enough.

Any help is greatly appreciated.

Thanks, Codi Lechasseur




On Fri, 10 Dec 2004 08:20:55 +1300, Paul Bennett <Paul.Bennett at eyede.com> wrote:
> or (mysql):
> CREATE TABLE tablename AS (YOUR SQL STATEMENT HERE )
> 
> bearing in mind that if you have a join with two table which share the
> same field names (eg: 'id' is on both tables) you will have to rename
> the field for one of them in the sql statement.
> eg: select table1.id, table2.id as t2_id from table1 left join table2 on
> (conditions here)
> 
> or have I just made the issue more foncusing?
> 
> Bring Back RUDY!!!
> 
> 
> 
> Brian Cummiskey wrote:
> 
> >> you mention merging-  the only way to do that is by use of a
> >> temporary table Depending on the sql server (MS, MySQL, etc) the
> >> create statements are different.
> >
> >
> > but basically, you'd:
> >
> > create table #blah (member_id varchar(50))
> >
> > then,
> >
> > insert into #blah
> > select member_id from members
> >
> > insert into #blah
> > select member_id from profile
> > where member_id not in (select member_id from #blah)
> >
> >
> > then, select * from blah.
> >
> > HTH
> 
> 
> --
> Paul Bennett
> Web Technologies Developer
> Eyede International Limited
> Level 1, 5 Bouverie Street
> PETONE
> PO Box 31 012
> Lower Hutt
> Phone     +64 4 939 8764
> Fax      +64 4 939 6719
> www.eyede.com
> 
> 
> 
> 
> --
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>


More information about the thelist mailing list