Solved

SQLite  Query needed

Posted on 2013-05-27
8
379 Views
Last Modified: 2013-05-27
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
0
Comment
Question by:DaveThomasPilot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 

Author Comment

by:DaveThomasPilot
ID: 39199900
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
 
LVL 25

Expert Comment

by:chaau
ID: 39199913
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
 

Author Comment

by:DaveThomasPilot
ID: 39199922
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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

Author Comment

by:DaveThomasPilot
ID: 39199934
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
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 39199939
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
 

Author Comment

by:DaveThomasPilot
ID: 39199959
Is that different than what I posted, or did you post pretty much the same time I did?
0
 
LVL 25

Expert Comment

by:chaau
ID: 39199963
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39199965
>>Maybe there's an easier way
nope: no easier way.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question