How can i make this work

Question

How can i make this works?

I have 2 tables:

course:
course id,name
prereq:
course id,prereq id

What is the query for this result:

+----------+------------+----------+------------+
|Course ID |Name        |Prereq ID |Prereq Name |
|1         |Intro into B|          |            |
|2         |Biology     |1         |Intro into B|
|3         |Genetics    |1         |Intro into B|
+----------+------------+----------+------------+

Try indenting the table so that the page automatically recognizes it a code. you can preview to make sure you got it right. That will help people read your question. — Isaac Betesh, Nov 22 '15 at 18:42
but the prereq name on the first row is still showing Intro into B — albert moren, Nov 22 '15 at 18:47
Possible duplicate of [How can an SQL query return data from multiple tables](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) — Olatunde Garuba, Nov 22 '15 at 19:40

chris85 · Answer 1 · 2015-11-22T18:56:14.367

0

You should be able to use a left join to pull these two tables together, something like:

Select c.courseid, c.coursename, p.prere1id, p.preqname 
from course as c 
left join prereq as p 
on c.courseid = p.courseid

edited Nov 22 '15 at 18:56

answered Nov 22 '15 at 18:47

chris85

23,846
7
34
51

there's no prereqname , i think the prereq name is using the coursename. – albert moren Nov 22 '15 at 18:57
I don't know your table scheme you didn't include it. In your expected output you have `Prereq Name`, change the column name(s) accordingly? – chris85 Nov 22 '15 at 18:59
the table is there, table course = courseid, coursename, table prereq = courseid, prereqid. – albert moren Nov 22 '15 at 19:04
You have `Prereq Name` in your output... – chris85 Nov 22 '15 at 19:26
yes. but maybe the prereq name output is using the course name by looking the prereq id which is the same as courseid.If the prereq id is 1 then the course id is 1 too so the prereq name is intro into biology. can i use the coursename to do that? – albert moren Nov 23 '15 at 00:49
You should post your whole scheme. It's hard to tell what you currently are doing. The SQL isnt just going to select something that isnt there though. – chris85 Nov 23 '15 at 00:51

score 0 · Accepted Answer · answered Nov 22 '15 at 19:32

0

SELECT c.courseId, c.courseName, p.prereqId, pc.courseName
FROM course as c
     // joins prerequisites
     LEFT JOIN prereq as p on p.courseId=c.courseId
     // joins course data to prerequisites
     LEFT JOIN course as pc on p.prereqId=pc.courseId

answered Nov 22 '15 at 19:32

Gralgrathor

216
1
8

yesss... it works. thank yo very much :))) – albert moren Nov 23 '15 at 00:53

How can i make this work

2 Answers2