Link to home
Create AccountLog in
Avatar of Richard Korts
Richard KortsFlag for United States of America

asked on

SQL Select multiple tables

See attached; I have two tables with all the same columns EXCEPT one.

I'm trying to do this:

SELECT * from task, cl_task c where c.cid = 0 order by tgroup, category, subcategory, subsubcategory

I get:

#1052 - Column 'tgroup' in order clause is ambiguous

Of course tgroup is in both tables, as is category, subcategory, etc.

Do I have to use union or join or ??
cl-task-table.jpg
task-table.jpg
Avatar of Bill Prew
Bill Prew

Yes, the problem is that both tables contain a column tgroup, and since you did a SELECT *, both of those are in the results. You need to tell it which table you want the column from in the ORDER BY, perhaps like this.

Just curious, if all the columns are the same except one, do you want to be joining them together somehow? As it stands now I think you will get a Cartesian product as your result, which means the first row in the the task table will be joined with every row in the cl_task table, then the second row in the task table will be joined with every row in the cl_task table, etc. Is that what you really want?

select * 
  from task t, cl_task c 
  where c.cid = 0 
  order by t.tgroup, t.category, t.subcategory, t.subsubcategory

Open in new window

~bp
#1052 - Column 'tgroup' in order clause is ambiguous
That's because you have a column named tgroup in the table task and another column named also tgroup in the table cl_task

Of course tgroup is in both tables, as is category, subcategory, etc.
Then you should tell the query which column 'tgroup' (from which table) to use in the ORDER BY clause:
SELECT * from task, cl_task c 
where c.cid = 0 
order by c.tgroup, category, subcategory, subsubcategory

Open in new window

Avatar of Richard Korts

ASKER

I do not thing those solutions will work.

The situation is that the table "task" is the master table of tasks provided with the app.

The individual client (customer) (thus the nomenclature cl_task) can create their own tasks; in the instance I am referencing, I want ALL tasks in BOTH tables returned in ONE result set. I don't care about the column cid being in the result set; it is only in the cl_task table. I just need it to select any for the specific client logged in.

All of the items with the same tgroup (as well as the other order by items) must be bunched together.

The cid column in the cl_task table is the ID of the specific client. In MANY cases, there will be NO tasks in the cl_task table; but there will be for some.

I'm trying to avoid building a "work" (temporary) table; I will if there is no way to do it in SQL.

Thanks
Can you share a small set of sample data for each table, maybe a few rows, and then also share what the results are you want?  It sounds like you want to JOIN and GROUP BY potentially, but I'd like to better understand exactly what the data will look like and the results desired.

~bp
To billprew,

See attached. I inadvertently misnamed the task table (rable-task)

The result set should contain the tasks br301 & br301 together with br13, br10 & br7, At the moment the sample portion of cl_task is the ENTIRE table.

Thanks
cl-task-table.jpg
rable-task.jpg
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account