MySQL Join

[Todd W]

Hey guys here's what I want to do.

I have two tables each with different data.

I want to be able to search table 1 and table 2 at the same time based on the user search string.

Table 2 has "more" tables than table 1 and I don't want those to be included in the search or join, and the same is True for Table 1.

1. i`m not sure if I can join a table that isn't identical.

2. There are search options that the user may choose that are not found in table 2... this is fine as long as it doesn't "cause an error". (I could even do if $var then don't do the join for table 2 but it would be easier if it just didn't cause an error).

Is this to vague?

Thanks guys/gals.

-Todd

[MarkB]

Easy to do.

Say table1 has 'name, email'

Say table2 has 'order, orderdate'

And both have 'userstring'

Just use:


SELECT name, email, order, orderdate FROM table1 LEFT JOIN table2 on table1.userstring = table2.userstring

That should work :)

[Todd W]

I guess I am still confused because table 1 and table 2 will both have a bunch of the same columns, and I need to do things such as where name='bob', city='chicago', for BOTH tables.

And then order by inputdate for both tables.

And Someone might select for instance address='blahblah' and that's only in table 1, which doesn't matter if I get no records from table2 I just don't want it to crash.

The query is built when the user selects the options to search and types in the values they want so I cold add the stuff for the second table - but right now I`m trying to grasp the exact way to do that.

Thanks
-Todd

[The New Guy]

do SELECT table1.city as fcity, table2.city as scity etc.

[Todd W]

do SELECT table1.city as fcity, table2.city as scity etc.

Ok - and if table2.___Whatever___ doesn't exist I wont build that into the query. Now i`m getting somewhere I think :)

[Todd W]

do SELECT table1.city as fcity, table2.city as scity etc.

Looked at this post again and realized this wont wrok when displaying the data.

First, Last
City, State Zip

Well if I have to do $fcity or $scity depending on where the data came from it's a problem :mad:

[MarkB]

What was wrong with what I offered? If you need a WHERE statement, you just tack it onto the end (and if tables share names, then you do as The New Guy said and use WHERE table1.name='whatever' AND table2.name='whateverelse'

[Todd W]

What was wrong with what I offered? If you need a WHERE statement, you just tack it onto the end (and if tables share names, then you do as The New Guy said and use WHERE table1.name='whatever' AND table2.name='whateverelse'

Nothing is wrong, I just need to make sure it works for what I want.

So by doing something like this.

SELECT name, email, order, orderdate FROM table1 LEFT JOIN table2 on table1.userstring = table2.userstring

You are joining the tables based on the usertring... being table1 has the data for the user based on userstring, and so does table 2. CorrecT?

If this is correct, this is not how the database/tables are setup and wyh I am confused I think.

Table 1 Contains a bunch of data about a bunch of people
Table 2 Contains a bunch of data about a bunch of DIFFERENT people.

I`m looking for a way to search both tables with 1 query, some column names are different but that's no problem "I don't think".

Right now my query works fine because it just uses table1 and users build their query based on only table 1 options. When they build the query in the future it willstill only have table 1 options (table2 has same optipons and maybe a few more that ownt be searched).

There is 1 column in common and it's "dlocation" so that whne I display the results and someone clicks the link for more info it knows exactly where to get the data from, as dlocation is which table it is in.

Does this make more sense?
Would your idea still work?

[MarkB]

If the two tables contain data not related to each other, then just do:

SELECT table1.user, table1.something, table2.somethingelse, table2.wotsit FROM table1, table2 WHERE table1.userstring='userstring' AND table2.userstring='userstring'

Maybe not the neatest code, but i'm sure it would work.