Hi I am having difficulty writing an SQL query to do what I want:
I have 1 table with 2 columns, 'id' and 'name':
tbl_names:
id name
-- ----
1 Bob
2 Jeff
3 Fred
4 Joe
5 Bill
I then have another table which contains several fields which hold id's
from the above table:
tbl_output:
id person1 person2 person3
-- ------- ------- -------
1 2 3 1
2 5 4 3
I need a query that will return the names for the specified id from
tbl_output.
If I have just one 'person' field in tbl_output I would do it with an
inner join like this:
SELECT name from tbl_names
INNER JOIN tbl_names on tbl_names.id = tbl_output.pers on
WHERE tbl_output.id = ?
but I can't figure it out when theres multiple fields to be joined from
the same table...e.g I want to specify tbl_output.id = 1, and it give me:
person1 person2 person3
------- ------- -------
Jeff Fred Bob
Any ideas?
Cheers,
Ben. 3 8790
Ben Willcox wrote: but I can't figure it out when theres multiple fields to be joined from the same table...e.g I want to specify tbl_output.id = 1, and it give me:
person1 person2 person3 ------- ------- ------- Jeff Fred Bob
SELECT n1.name as person1, n2.name as person2, n3.name as person3
from tbl_names t inner join tbl_output p on t.id = p.id
inner join tbl_names n1 on p.person1 = n1.id
inner join tbl_names n2 on p.person2 = n2.id
inner join tbl_names n3 on p.person3 = n3.id
where t.id = ?
Supply the value "1" for the "?" parameter.
Regards,
Bill K.
Bill Karwin wrote: but I can't figure it out when theres multiple fields to be joined from the same table...e.g I want to specify tbl_output.id = 1, and it give me:
person1 person2 person3 ------- ------- ------- Jeff Fred Bob
SELECT n1.name as person1, n2.name as person2, n3.name as person3 from tbl_names t inner join tbl_output p on t.id = p.id inner join tbl_names n1 on p.person1 = n1.id inner join tbl_names n2 on p.person2 = n2.id inner join tbl_names n3 on p.person3 = n3.id where t.id = ?
Supply the value "1" for the "?" parameter.
Thanks Bill that worked perfectly in my test database. However when I
tried to modify it for the live app with a slightly different dataset,
it didn't work straight away. I belive this is because the records in my
tbl_names table start at an id of around 700, therefore the first JOIN
cannot link the two id fields together.
I'm still unsure why that first join is required, or how it works, as
there is no relationship between the two id fields? - is it just to
limit the output to 1 record?
Anyway, I managed to get around it by removing the first join, which
cause the query to return lots of identical records (with the data I
want), and adding LIMIT 1 to just give me one of them. Do you think this
is the best way to do it?
Thanks again for your help,
Ben.
Ben Willcox wrote: I'm still unsure why that first join is required, or how it works, as there is no relationship between the two id fields? - is it just to limit the output to 1 record?
Ah, I was confused about the query you were trying to achieve. Yes,
you're right to take out the first join.
Anyway, I managed to get around it by removing the first join, which cause the query to return lots of identical records (with the data I want), and adding LIMIT 1 to just give me one of them. Do you think this is the best way to do it?
That works, but you can also use "SELECT DISTINCT ..."
Regards,
Bill K. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: James |
last post by:
Can anyone please shed some light on the following...
I have a framework that uses dynamically created tables, named using an
incremental "attribute set ID", as follows:
attrdata_1
attrdata_2
attrdata_3
etc, etc...
|
by: Christopher Harrison |
last post by:
Is there a way to store an indefinite number of keys in one field and
self join them? Imagine, for example, you have a table of users with a
"friends" column. Say user 1 is friends with users 9, 7, 24 and 6; then
would it be possible to store this
array/list/whatever-you-want-to-call-it into user 1's friends cell and
extract the join in a...
|
by: dskillingstad |
last post by:
I've been struggling with this problem for some time and have tried
multiple solutions with no luck.
Let me start with, I'm a novice at Access and I'm not looking for
someones help to design my database,just help in getting me pointed in
the right direction.
I have a database with 8 tables, which from what I have read, cannot be
linked...
|
by: rayone |
last post by:
Hi folks.
I need advice.
2 options, which do you think is the better option to
display/retrieve/report on the data.
Keep in mind reporting (Crystal), SQL Performance, VB Code, usability,
architecture.
Case 1: On a web page I would like to render a dropdown list
|
by: Steve C |
last post by:
Hi,
I'm having problems constructing a nested join. It's quite complex, so
here's a simplfied example of the problem. Any thoughts on what I'm
doig wrong - or if I've got the whole approach wrong are welcome.
I've two tables :-
one is a contact table contacting name, addresses etc. Three of the
fields represent users - 'created by',...
| |
by: veaux |
last post by:
Question about joins in queries. I have 2 tables with a field called
"ID".
Table 1
Rec1 = Jan12FredFlintstone
Rec2 = Feb01WilmaRubble
Table 2
Rec1 = Jan12BarneyRubble
Rec2 = Mar03SamSlate
|
by: Shizbart |
last post by:
MS Access 97
Beginner/Moderate Level User
I am trying to create a Database to track Workouts in MS Access 97.
I have one Table named Workouts that contains the following Fields: Workout Code (e.g: U100), WorkoutActivity (e.g.: Bench Press: Flat Bench Straightbar) (note: this is the Primary Key of this Table), and Target Area ...
|
by: paquer |
last post by:
Ok'
I have 2 tables.
The first being my main table where each record has a 'FigureID' txt
box. (Indexed - No duplicates)
The second being my sub table where reporting is entered for these
Figures. Sometimes more than one at a time. I have 4 'FigureID' txt
boxes on this subtable.(All Indexed - Duplicates OK)
When I set the Master/Child...
|
by: Apple001 |
last post by:
Hi all!
I am having trouble with joining multiple rows into one row. I will appreciate any help.
For columns in the query, I have: invID(autot number), entryDate, invDate, vendor, invoiceAmount from table named tblInvoice, and building, account, percent (allocation in percentage for each building) from table named tblAllocation.
This is...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |