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
Richard KortsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
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
0
ArgentiCommented:
#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

0
Richard KortsAuthor Commented:
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
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Bill PrewCommented:
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
0
Richard KortsAuthor Commented:
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
0
PortletPaulfreelancerCommented:
:( too much typing; so in summary

you want a UNION ALL (not just union)

select task, title, tgroup, category, subcategory, subsubcategory, ....
from cl_task c
where c.cid = 0

UNION ALL

select task, title, tgroup, category, subcategory, subsubcategory, ....
from task t
-- where <<something ??>>
-- I just need it to select any for the specific client logged in.

order by tgroup, category, subcategory, subsubcategory

each column has to be specified in the same sequence and the data types of each column must be the same (or cast/converted to the same
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.