Inner Join Help!!
-
I've adopted the look up table strategy from this tutorial in my access database, but i'm now have a query problem when i try to show records from the look up table. I have a table called Productions with the following fields:
ProductionID, ProductionTitle, ProductionType, ProductionClass, ProductionAward.
The last three all reference the look up table that contains the proper names, but when i run a query i can only ever get one of these columns. The look up table has a LookName table that holds the correct name for each of the above fields. Do i have to create 3 seperate subqueries? Shouldn't it just be:
SELECT Productions.ProductionID, Productions.ProductionTitle, Productions.ProductionType, Productions.ProductionClass, Productions.Award, Ref_Look.LookName
FROM Ref_Look
INNER JOIN
Productions
ON
Ref_Look.LookID = Productions.Award
AND
Ref_Look.LookID = Productions.ProductionClass
AND
Ref_Look.LookID = Productions.ProductionType;
any suggestions will be greatly appreciated!
-
You need multiple joins to look up different rows
SELECT Productions.ProductionID, Productions.ProductionTitle, Productions.ProductionType, Productions.ProductionClass, Productions.Award,
r1.LookName as AwardName,
r2.LookName as ClassName,
r3.LookName as TypeName
FROM Productions p
INNER JOIN Ref_Look r1 ON r1.LookID = p.Award
INNER JOIN Ref_Look r2 ON r2.LookID = p.ProductionClass
INNER JOIN Ref_Look r3 ON r3.LookID = p.ProductionType
Although your original query would return the answers in three rows if you changed the ANDs to ORs, but it is hard to imagine a situation where this is a useful result.
SELECT Productions.ProductionID, Productions.ProductionTitle, Productions.ProductionType, Productions.ProductionClass, Productions.Award, Ref_Look.LookName
FROM Ref_Look
INNER JOIN Productions ON Ref_Look.LookID = Productions.Award
OR Ref_Look.LookID = Productions.ProductionClass
OR Ref_Look.LookID = Productions.ProductionType
-
I looked over your response but i still can't get it to work. I'm using access so the innerjoin syntax is a little different than the syntax that you showed. I tried the second one but only got the same values in all three columns. It showed only the data values not the names. Don't i have to have LookName AS ProductionType for each of the columns that i want to show the name for? I still have three other columns that reference a source from the companies table. They are SourceTItle (being the company where this title is referenced), SourceYear, and SourceDescription. Most of these will be the same value but some are different. The same problem arises with the look names. Whether i seperate my tables from the look up table and have a bunch of reference tables i'm still going to have a messy and long query no? That is why i decided to do it this way. I did happen to combine it in this fashion doing a bunch of queries together but its not very intuitive:
SELECT Productions.ProductionID, Productions.ProductionTitle, Productions.Year, Productions.Description, V_Productions_Type.ProductionType, V_Productions_Class.ProductionClass, V_Productions_Award.Award, V_Source_Title.SourceTitle, V_Source_Year.SourceYear, V_Source_Description.SourceDescription
FROM V_Source_Description, V_Source_Year, V_Source_Title, V_Productions_Award, V_Productions_Class, V_Productions_Type, Productions
WHERE
(V_Productions_Type.ProductionID=Productions.ProductionID)
And (V_Productions_Class.ProductionID=Productions.ProductionID)
And (V_Productions_Award.ProductionID=Productions.ProductionID)
And (V_Source_Title.ProductionID=Productions.ProductionID)
And (V_Source_Year.ProductionID=Productions.ProductionID)
And (V_Source_Description.ProductionID=Productions.ProductionID);
I'd love it if i could just do it with one query referencing those 2 tables as what i'm trying to accomplish is one table that will contain all my data so i can do a search on any field via a form online.
Thanks a million!!!
David
-
Got it thanks!!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply