Selecting multiple tables

[liquidd]

Hello All,

Ive done a search on multiple table selection and still, nothing seems to be working.

Im not sure if my syntax is correct but here it is:



PHP:--------------------------------------------------------------------------------
$result = mysql_query("SELECT keith.job_num, chris.job_num FROM keith, chris WHERE job_num='$findProj'") or die("nogo" .mysql_error());

--------------------------------------------------------------------------------


I have about 14 tables, one for each employee. All cols in each table are the same.

What I need to do is, from each table, pull all the info from col named job_num where job_num = a passed variable ($findProj).

Ive tried many different variations of the code below and have gotten nothing. The code below produces the following error:
--Column: 'job_num' in where clause is ambiguous --??????

Any help would be great!
Thanks
:confused:

[sicarius]

Well, the issue is more than likely that you need to say:

WHERE keith.job_num='$findProj'

This of course means that it will only look at the job_num field in the keith table.


Honestly though, I don't know what your specs are for this database, but having a separate table for each employee is probably a poor design.

[DNAunion2000]

liquidd: Im not sure if my syntax is correct but here it is:


PHP:--------------------------------------------------------------------------------
$result = mysql_query("SELECT keith.job_num, chris.job_num FROM keith, chris WHERE job_num='$findProj'") or die("nogo" .mysql_error());

--------------------------------------------------------------------------------


/*DNAunion*/ If you get this to work it still might not produce the result you expect. You have no join condition to match up rows from the two tables so the result would be a Cartesian product, where each selected row in the first table is combined with each selected row from the second table. In other words, if the first table had 100 records that satisified the WHERE clause, and the second table also had 100 records that satisfied the WHERE clause, the result set would contain 10,000 rows (100 x 100).

Also, assuming your intention is not to produce a Cartesian product, why would you want the result to contain two columns - Keith's and Chris's job_nums - when only one or the other would be populated in any one resulting row?

I have about 14 tables, one for each employee. All cols in each table are the same.

/*DNAunion*/ To repeat what was already said, unless there is a good reason to do so (which we out here don't know), it seems illogical to have 14 tables with the same structure, one for each employee. Why not just combine them all into one table?