Multiple Tables, same column name

tedxas

I have two tables:

TABLE_A
personid
jobid

TABLE_B
personid
jobid

These tables will hold a person's ID and the job id of a particular job in which he/she is interested.

I want to rank the top 5 most requested jobs.

So I need to select jobid from both tables and rank the jobid's from most-to-least requested.

Here's my query. I can get it to run on a single table, but I can't get it to include both:

SELECT jobid ,count(jobid) AS rank FROM TABLE_A,TABLE_B GROUP BY jobid ORDER BY rank DESC LIMIT 5

I get the following error:

MySQL said: Column: 'jobid' in field list is ambiguous.

Many thanks in advance!

Anon

seemingly not enough sql

to be clear of which field ist meant, the name of the database has to be added;
more exactly: adress this by database.field
SELECT table_a.jobid ,count(table_a.jobid) AS rank FROM TABLE_A,TABLE_B GROUP BY table_a.jobid ORDER BY rank DESC LIMIT 5

as always, no time yet for testing ...;-)

tedxas

Yes -- but how to I collect all the jobid from both tables and combine the COUNT() statements as RANK and output the data in descending order based on RANK?

Many thanks again!

Anon

You need to specify the table as well, e.g. table_a.jobid.

I don't understand your database design. What's the difference between table_a and table_b? I would assume that you would only need one table for associating job with person.

Anon

Why have you broken this up in two tables? I would include table_a and table_b in one table.

tedxas

table_a is a list of people that are already employees (with their own id's) while table_b is a list of people outside our organization (who are assigned temporary id's). I cannot keep them all in the same database because of the conflicting id structure.

Anon

What a pain! Your join gets you into a cartesian product which isn't what you want! You don't even want a join here. What do you plan on joining on?

The count itself isn't difficult, you would be summing the two different counts as rank, and ordering by that. I would take a look at the ids - can you assign them a valid id or include an additional column in you table_a? As hokey as it sounds, I would assign the temporary ids in a new column in table_a.

tedxas

Because the tables have identical column names, I only need to specify the particular table I need within a script instead of writing scripts to access each table. Since the scripts in question use sessions, that table definition carries across the session. Very handy, actually.

So maybe I should run a separate select for each table, read the results into separate arrays and then somehow combine and output the data?

Anon

you haven't mentioned which DB you are using. Why does it matter? because MySQL doesn't support UNION SELECT which is really waht you want here I think. You would basically write twi select statements then put UNION before the second. Works nicely in other DBs.

An alternative is to read the contents of two result sets into a multidimensional array. Then you would have to do some major sorting and counting... what a pain.

I agree with "c" - if you are using MySQL, then you need to find a way to make this one table. You could simply have an additional column that indicates whether the ID is temp or perm and you're off!

Anon

Yes UNION is what is wanted, but Mysql (the database in use I believe) doesn't support it. Bummer....

tedxas

Gang --

As you both surmised, I am using MySQL. I think I'll follow your advice and try to combine the data into a single table. Thanks for the help!