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
MySQL Server

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
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
Argenti

#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

ASKER
Richard Korts

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Bill Prew

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
ASKER
Richard Korts

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
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question