SQLite Query needed

I need to do a select from a table doing something like this:

select n.name from IDTONAME n, MANYCOLUMNS M where n.id=M.column1

Basically, the values in column1 of the MANYCOLUMNS table are used to find a name from the IDTONAME table.

This would work fine, I only wanted a single column in the result set, but I need several columns, each with the id in the M table used to find the name from the n table.

I tried this:

select (select n.name from IDTONAME n, MANYCOLUMNS M where n.id=M.column1),
          (select n.name from IDTONAME n, MANYCOLUMNS M where n.id=M.column2),

.....

but then even the first column is not correct---I get the same name for each record in the result set instead of the name associated with the id in the column.

Thanks,

Dave Thomas
DaveThomasPilotAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
chaauConnect With a Mentor Commented:
OK, I misunderstood you. In this case you need LEFT JOIN

select n1.name, n2.name, n3.name, etc. 
from MANYCOLUMNS M LEFT JOIN IDTONAME n1 ON n1.id=M.column1
LEFT JOIN IDTONAME n2 ON n2.id=M.column2
LEFT JOIN IDTONAME n3 ON n3.id=M.column3
etc.

Open in new window

0
 
DaveThomasPilotAuthor Commented:
Clarifying, I want a record in the return set for every record in MANYCOLUMNS.  The field values should correspond to the names in the IDTONAME table, as indexed by the values in the MANYCOLUMNS table.
0
 
chaauCommented:
You need to use UNION

select n.name from IDTONAME n, MANYCOLUMNS M where n.id=M.column1
UNION ALL
select n.name from IDTONAME n, MANYCOLUMNS M where n.id=M.column2
UNION ALL
select n.name from IDTONAME n, MANYCOLUMNS M where n.id=M.column3
etc...

Open in new window


If you need to sort it as well, then add ID to the selection and sort by it

select n.name, n.ID from IDTONAME n, MANYCOLUMNS M where n.id=M.column1
UNION ALL
select n.name, n.ID from IDTONAME n, MANYCOLUMNS M where n.id=M.column2
UNION ALL
select n.name, n.ID from IDTONAME n, MANYCOLUMNS M where n.id=M.column3
ORDER BY 2

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
DaveThomasPilotAuthor Commented:
That gives me the data, but two many rows--each union will have the same number of records as MANYCOLUMNS----not what I want.
0
 
DaveThomasPilotAuthor Commented:
I finally found something that I think works...

SELECT
C1.name,
C2.name,
C3.name,
.....

FROM MANY_COLUMNS

LEFT_OUTER_JOIN IDTONAME AS C1
ON MANYCOLUMNS.COLUMN1=C1.NAME
LEFT_OUTER_JOIN IDTONAME AS C2
ON MANYCOLUMNS.COLUMN2=C2.NAME
LEFT_OUTER_JOIN IDTONAME AS C3
ON MANYCOLUMNS.COLUMN3=C3.NAME


...

Maybe there's an easier way, but it seems to work.
0
 
DaveThomasPilotAuthor Commented:
Is that different than what I posted, or did you post pretty much the same time I did?
0
 
chaauCommented:
I have not seen your answer when I was typing mine. Anyway, as you have found the answer yourself, you can request to close the question, or accept your answer as a solution
0
 
PaulCommented:
>>Maybe there's an easier way
nope: no easier way.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.