How to do I get the name of subjects pre-requisites?

Question

Here's a rough sketch. I have a pre-requisite table and subject table. I have a rough idea how I can list the subject code. But I am really unsure on how I can get a query that can list out the name and details of the subject and it's pre requisites.

For example, I would like to write a query that will list out the subjects names and its pre requisite names. So the resultant would come out as (Well I'll do the concatenating texts later): "Introduction to Computer is a pre-requsite of Operating Systems".

I'm just wondering how do I extract the names of subjects off these two tables?

CREATE TABLE subjects (
subject_code VARCHAR(7) NOT NULL CONSTRAINT subject_pk PRIMARY KEY,
subject_name VARCHAR(50) NOT NULL,
subject_details TEXT NOT NULL
);



CREATE TABLE SubjectPrerequisite
(   Primary_Subject_Code        VARCHAR(7) NOT NULL,
    Prerequisite_Subject_Code   VARCHAR(7) NOT NULL,
    CONSTRAINT PK_SubjectPrerequisite PRIMARY KEY (Primary_Subject_Code, Prerequisite_Subject_Code),
    CONSTRAINT FK_SubjectPrerequisite_Primary_Subject_Code FOREIGN KEY (Primary_Subject_Code) REFERENCES Subject (Subject_Code),
    CONSTRAINT FK_SubjectPrerequisite_Prerequisite_Subject_Code FOREIGN KEY (Prerequisite_Subject_Code) REFERENCES Subject (Subject_Code)
)

//EDIT: Here's what I have so far

SELECT  subject_name
FROM SubjectPreRequisite t0
INNER JOIN subjects t1
ON t0.subject_code = s1.prerequisite_subject_code

What you are looking for is string concatenation. There have been numerous discussions here on the topic. The particular method depends on the database you are using. — Gordon Linoff, May 30 '12 at 14:36
The basis is `select some_field from some_table`... What did you tried ? — Seki, May 30 '12 at 14:37
Are you perhaps looking for [Join](http://www.w3schools.com/sql/sql_join.asp)? — Joshua Drake, May 30 '12 at 14:37
please add a tag to your question for the db (vendor/version) you are using — Bohemian, May 30 '12 at 14:38
yeah I do get join, but its really confusing on how to differenciate between those two since they both are unit names and I can't seem to get it right. — nfnmy, May 30 '12 at 14:38
@GordonLinoff: I feel that what the OP is looking for if how to make a (possibly recursive) request to extract the hierarchy of subjects (since a subject may be prerequisite or another than can be prerequisite of another that can...) rather than string concats. — Seki, May 30 '12 at 14:40
So a `subject` has a many to many relationship to prerequsite `subject`s — Jodrell, May 30 '12 at 14:47
its one to many I would say. One subject can have 0 or more pre requisites. So yes it is recursive. — nfnmy, May 30 '12 at 14:50
@nfnmy but, couldn't a subject be q pre-requisite of multiple subjects too? I like to call this a "pigs ear" realtionship — Jodrell, May 30 '12 at 15:13

score 1 · Answer 1 · answered May 30 '12 at 14:40

1

Assuming you want the total list of subject names, do this query:

select subject_name from subjects

Assuming you want the subjects pre requisitites and subject_code has a relation with Primary_Subject_Code, do thus query:

select s.subject_name, r.Prerequisite_Subject_Code
from subjects s
inner join SubjectPrerequisite r on s.subject_code = r.Primary_Subject_Code

And with your concat:

select r.Prerequisite_Subject_Code ' + is a pre-requisite of ' + s.subject_name as 'Pre-Requisites'
from subjects s
inner join SubjectPrerequisite r on s.subject_code = r.Primary_Subject_Code

answered May 30 '12 at 14:40

aF.

64,980
43
135
198

Oh I hate doing self referential tables. Okay so I don't actually want the subject code but I just want the subject names. So that means there are two fields of subject names involved here. One for the subject name and the other is for the prerequisite subject name. So how can I get the name of the pre requisite subject at the same time as the subject itself? I am really confused with this as it's like a recursive thing going on here. – nfnmy May 30 '12 at 14:49
@nfnmy doesn't my query do that? – aF. May 30 '12 at 14:50

score 1 · Answer 2 · edited May 23 '17 at 10:28

I assume (perhaps wrongly) you are looking to concatenate the subject names of the prerequisites into a single row. Below is a SQL Server example of how this can be done:

;WITH Prerequisites AS
(   SELECT  Primary_Subject_Code, Subject_Name
    FROM    SubjectPrerequisite
            INNER JOIN Subjects
                ON Subject_Code = Prerequisite_Subject_Code
)           
SELECT  Subject_Code,
        Subject_Name,
        Subject_Details,
        STUFF(  (   SELECT  ',' + Subject_Name
                    FROM    Prerequisites
                    WHERE   Primary_Subject_Code = Subject_Code
                    FOR XML PATH(''), TYPE
                ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS PrerequisiteList
FROM    Subjects

I have previously given a full explanation of how the XML PATH Method works here. With a further improvement to my answer pointed out here

See also SQL Server - Possible Pivot Solution?

score 0 · Answer 3 · edited May 23 '17 at 12:16

If you just want to extract the pre-prequsite relationships to 1 level of recursion you could do

SELECT
              [original].[subject_code] [OriginalCode]
            , [prereq].[subject_code] [Pre-RequisiteCode]
    FROM
            [subject] [orginal]
        LEFT JOIN
            [SubjectPrerequisite] [spr]
                ON [spr].[Primary_Subject_Code] = [original].[subject_code]
        JOIN
            [subject] [prereq]
                ON [prereq].[subject_code] = [spr].[Prerequisite_Subject_Code]
    ORDER BY
             [OriginalCode]
           , [Pre-RequisiteCode]

If you want to show the recursive chain and concatenate the subjects in some way then a CTE like GarethD's answer is the way to go. However, I suggest doing that with SQL would be wrong for an n-tier application.

score 0 · Answer 4 · answered May 30 '12 at 15:38

0

Think I solved it:

 SELECT t.subject_name + 'is a pre-requisite of' + s.subject_name
 FROM subjects s
 INNER JOIN pre_requisites r ON s.subject_code = r.subject_code
 INNER JOIN subjects t ON t.subject_code = r.pre_requisite_code

answered May 30 '12 at 15:38

nfnmy

157
1
4
14

How to do I get the name of subjects pre-requisites?

4 Answers4