0

Following is the schema of my Tables:

  1. Marks_Table (SubjectCode,TypeID,MarksObtd)
  2. Subjects_Table (SubjectCode, Subject)
  3. Types_Table (TypeID, Type)

I want to Select the Marks_Table table with SubjectCode and TypeID replaced with appropriate Subject and Type.

I wrote the following query:

SELECT [Subject] , [Type] , MarksObtd 
FROM [Marks_Table] 
INNER JOIN [Subjects_Table] 
   ON [Marks_Table].SubjectCode = [Subjects_Table].SubjectCode 
INNER JOIN [Types_Table] 
  ON *XXXXXXXXXXXXXXX*.TypeID = [Types_Table].TypeID

What should I write in place of XXXXXXXXXXXXXXX ? What should be the name of the table that has not been created yet?

It can be done in two queries, but is it possible to do this like above? and how?

  • 1
    Based on your schema, the last join condition can be ON [Marks_Table].TypeId = [Types_Table].TypeID. You can use the same table reference multiple times. Unless I've misunderstood you, this is a basic join that will be described in any introductory text or tutorial about SQL. – James L Sep 6 '14 at 11:35
  • Thankyou! In this case it works well. But suppose there are 10 rows in each table. Only 5 rows satisfies the first join condition. Then, after the second Join, wouldn't it ignore the fact that only 5 satisfied the previous Join, and will Join based on all the original 10 rows? – Himanshu Arora Sep 6 '14 at 11:40
  • Because it's an inner join, only rows that matched all the join conditions would be returned. Sorry, but I think you really need to study SQL for a while. You'll get a better understanding that way. – James L Sep 6 '14 at 11:45
  • 2
    (Why are you calling your tables Thing_Table? Isn't Thing enough? Also please get in the habit of using schema prefix and properly qualifying all columns.) – Aaron Bertrand Sep 7 '14 at 1:43
  • And why are some (table and column) names inside [brackets] and some not? – ypercubeᵀᴹ Jun 2 '16 at 14:09
1

try this

SELECT [Subject] , [Type], MarksObtd 
  FROM [Marks_Table] 
  INNER JOIN [Subjects_Table] 
  ON [Marks_Table].SubjectCode = [Subjects_Table].SubjectCode 
  INNER JOIN [Types_Table] 
  ON [Marks_Table].TypeID = [Types_Table].TypeID

As james added comment, I think it is a simple join and you need to change [TypeID] in select clause to [Type]

  • ooops! That was unintentional, thank you for pointing out. – Himanshu Arora Sep 6 '14 at 12:02
-3

Check this out;

SELECT [subject_id],[subject_name] 
FROM [relationship_table] ON subject_id = subject_id 
    JOIN [subject] ON [subject_id] = [relationship_table_id] 
WHERE subject_id = [Any_key]
  • 2
    There are issues with SQL syntax here, this just won't compile. – Andriy M Jun 2 '16 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.