I've been thinking about this problem for awhile, and can't seem to
come up with a valid answer. I've got two tables, Surveys and
Instructors. When a survey is completed there are one or two
instructors associated with it (no surveys with zero, and no surveys
with more than two). I'd like to take the data from both tables and
join them to display the results. Right now I accomplish displaying
all surveys with two instructors with the following SQL command:
SELECT S.PK_SurveyID, S.Name, S.CQ1, S.CQ2, S.CQ3, S.CQ4,
N1.Name as I1Name, I1.IQ1 as I1IQ1, I1.IQ2 as I1IQ2,
N2.Name as I2Name, I2.IQ1 as I2IQ1, I2.IQ2 as I2IQ2
FROM Surveys S
LEFT JOIN Instructors I1 ON S.PK_SurveyID = I1.FK_SurveyID
LEFT JOIN Names N1 ON I1.FK_NameID = N1.PK_NameID
LEFT JOIN Instructors I2 ON S.PK_SurveyID = I2.FK_SurveyID
LEFT JOIN Names N2 ON I2.FK_NameID = N2.PK_NameID
WHERE
I1.FK_NameID <I2.FK_NameID AND
I1.PK_Instructo rID IN (SELECT MIN(PK_Instruct orID) FROM Instructors
GROUP BY FK_SurveyID)
ORDER BY S.PK_SurveyID;
The problem is that if there is only one instructor associated with the
survey it doesn't show up in these results, which is correct per the
query, but I need a way to get all the results and join them
appropriately. Anybody have any ideas? I was thinking of querying the
Surveys table, and then for each surveyID, querying the Instructors
table, but I can't decide how to match those results correctly.
-Sean 3 2090
UNION clause
something like this:
select colA as FirstName,
colB as LastName,
instructorID1 as InstructorID
instructorName as InstructorName
from tableA
LEFT Join on tableA.Instruct orID1 = Instructors.Ins tructorID
WHERE stuff
union
select colA as FirstName,
colB as LastName,
instructorID2 as InstructorID,
instructorName as InstructorName
from tableA
LEFT Join on tableA.Instruct orID2 = Instructors.Ins tructorID
WHERE stuff
group by 1,2 -- column orders you want to group by instead of name
order by stuff
you get the idea...
--
Robbe Morris - 2004-2006 Microsoft MVP C#
I've mapped the database to .NET class properties and methods to
implement an multi-layered object oriented environment for your
data access layer. Thus, you should rarely ever have to type the words
SqlCommand, SqlDataAdapter, or SqlConnection again. http://www.eggheadcafe.com/articles/..._generator.asp
"ssims" <se*******@gmai l.comwrote in message
news:11******** **************@ j44g2000cwa.goo glegroups.com.. .
I've been thinking about this problem for awhile, and can't seem to
come up with a valid answer. I've got two tables, Surveys and
Instructors. When a survey is completed there are one or two
instructors associated with it (no surveys with zero, and no surveys
with more than two). I'd like to take the data from both tables and
join them to display the results. Right now I accomplish displaying
all surveys with two instructors with the following SQL command:
SELECT S.PK_SurveyID, S.Name, S.CQ1, S.CQ2, S.CQ3, S.CQ4,
N1.Name as I1Name, I1.IQ1 as I1IQ1, I1.IQ2 as I1IQ2,
N2.Name as I2Name, I2.IQ1 as I2IQ1, I2.IQ2 as I2IQ2
FROM Surveys S
LEFT JOIN Instructors I1 ON S.PK_SurveyID = I1.FK_SurveyID
LEFT JOIN Names N1 ON I1.FK_NameID = N1.PK_NameID
LEFT JOIN Instructors I2 ON S.PK_SurveyID = I2.FK_SurveyID
LEFT JOIN Names N2 ON I2.FK_NameID = N2.PK_NameID
WHERE
I1.FK_NameID <I2.FK_NameID AND
I1.PK_Instructo rID IN (SELECT MIN(PK_Instruct orID) FROM Instructors
GROUP BY FK_SurveyID)
ORDER BY S.PK_SurveyID;
The problem is that if there is only one instructor associated with the
survey it doesn't show up in these results, which is correct per the
query, but I need a way to get all the results and join them
appropriately. Anybody have any ideas? I was thinking of querying the
Surveys table, and then for each surveyID, querying the Instructors
table, but I can't decide how to match those results correctly.
-Sean
That will give me all of the records I need, but not in the format I'm
trying to get. Allow me to try to simplify.
Surveys - PK_SurveyID, CQ1, CQ2, CQ3, CQ4
Instructors - PK_InstructorID , FK_SurveyID, IQ1, IQ2, IQ3, IQ4
For every Survey records there can be one or two Instructor records.
If I JOIN the Instructors table twice to Surveys I only get those
Survey records with two Instructors. I need a way to join the tables
with a result structure of:
PK_SurveyID, CQ1, CQ2, CQ3, CQ4, PK_InstructorID , IQ1, IQ2, IQ3, IQ4,
PK_InstructorID , IQ1, IQ2, IQ3, IQ4
and results of:
1, 5, 5, 5, 5, 1, 3, 3, 3, 3, 2, 4, 4, 4, 4
2, 5, 5, 5, 5, 3, 2, 2, 2, 2, NULL, NULL, NULL, NULL, NULL
3, 5, 5, 5, 5, 4, 1, 1, 1, 1, 5, 2, 2, 2, 2
I don't know if this is possible through SQL alone, or if I have to do
some complex reading of one table and then reading of another table
with the results from the first query and then reading the whole
shebang into another array or dataadapter or whatever...
-Sean
After doing some additional reading on joins and a few hours of trial
and error I came up with a solution to my problem:
SELECT *
FROM Surveys
LEFT OUTER JOIN Instructors I1 ON I1.FK_SurveyID = PK_SurveyID
LEFT OUTER JOIN Instructors I2 ON I1.FK_NameID <I2.FK_NameID AND
I2.FK_SurveyID = PK_SurveyID
WHERE I1.PK_Instructo rID IN (SELECT MIN(PK_Instruct orID) FROM
Instructors GROUP BY FK_SurveyID)
-Sean This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Steve Hall |
last post by:
Folks,
My secnario involves two tables - ObservationRegister, and Person.
ObservationRegister contains most of the "useful" fields, including
the UserID of the person that raised the record, and the UserID of the
person to whom the record was assigned for action. I need to write a
query to return all values in the ObservationRegister record, but
instead of returning the UserIDs, I need to look up the actual name,
by looking up the name...
|
by: Andreas Lauffer |
last post by:
I changed from Access97 to AccessXP and I have immense performance
problems.
Details:
- Access XP MDB with Jet 4.0 ( no ADP-Project )
- Linked Tables to SQL-Server 2000 over ODBC
I used the SQL Profile to watch the T-SQL-Command which Access ( who
creates the commands?) creates and noticed:
|
by: Arni Snorri Eggertsson |
last post by:
Hi
I am trying to design an IO subsystem for my SQL Server and for that I
need to try and predict IO activity on each table in my MSSQL
Database. My idea is to move the hottest tables into special disk
subsystem away from the less hotter tables. So far I have gathered
that we have three tables more hot than the others but I have no
feeling on ratio on how hot each is and how much activity is on the
less hotter tables. I need to...
|
by: kieran |
last post by:
Hi,
I have the following sql statement. I originally had the statement
with two INNER JOINS but in some situations was getting an error so
changed the last INNER JOIN to a LEFT OUTER JOIN (as is seem below).
This seemed to work but i am unsure why and would like to know in case
it falls over again. Why did the two INNER JOINS not work, and am I
correct to use the LEFT OUTER JOIN in this context.
As you can see the table 'tblStaff1_2'...
|
by: sumGirl |
last post by:
Hi all. We have a mix of informix and mssql server and I want to know
if something we do in informix has an analogous feature in MSSQL. We
can define a "row type" in informix, like so:
create row type name_1(fname char(20),lname char(20));
The when we create any table that includes a first and last name, we
do so using this row type like so:
create table sometable(name name_1, some column,...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 query, some how? An easy and obvious way, that
is; I can think of string processing hacks ;)
Thanks...
|
by: Lyle Fairfield |
last post by:
I have a table: Schools,
and another table: Teachers
as one teacher may be assigned to more than one school I link these with a
third table: SchoolTeachers
I index (unique) this linking table on SchoolID and TeacherID to prevent
assigning a teacher to a school more than once.
So far, so good.
|
by: ThePhenix |
last post by:
Hi everybody,
I have recently been doing a conversion for my boss for a access 2000 database (both front and backend) to a MSSQL backend.
I finished the conversion today (as quite a lot of the code / queries ran slow due to access running the queries locally rather than on the server). And tested it on my and my boss's machine with no problems so he gave the go ahead to update everybody to our new mssql 2000 backend with the modified...
|
by: ruchika mahajan |
last post by:
Hi all...MSSQL serevr 2000 Trace Event Column ObjectType gives value 17 for object table for events Object:Created and Object:deleted.Can anyone tell me all the possible values for MSSQL 2000 Trace Event Column Object Type as is given for MSSQL server 2005 in the following link.
http://msdn2.microsoft.com/en-us/library/ms180953.aspx
|
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
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 captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |