CODEWITHSUNDEEP
sqlpostgresqlselectjoin
yulai
yulai

Reputation: 761

SQL Queries from multiple tables

I am taking a beginner's course in SQL, and have been playing around with some queries. One thing I don't really understand is how to "properly" query multiple tables, that is; compare values from two or more tables.

For instance, I have a table called Student, holding the username, name, date of birth, and major (just the code. For instance, CS would stand for "Computer Science") of a particular person. I chose to make the username a primary key.

I also have another table called Major holding the major code (such as CS) as a primary key, and the entire major name. For instance, "CS" = "Computer Science", NS = "Neuroscience", etc.

Now, suppose I want to find the name of a major, given a student's username. Following is the imagined pseudocode for this query:

1) In the Student table: Provided the username, check what the major of that particular person is. 

select majorcode from Student where username='aUserName';

Doing so correctly gives me the major code.

2) In the Major table: Find the title of the major provided the code. 

select majorTitle from Major where majorcode='theMajorCode';

Combinded, I write: 

select majorTitle from Major where majorcode=(select majorcode from Student where username='aUserName');

However, now suppose I want BOTH the title of the major (from the Major table) as well as the name of the student (from the Student table).

Any advice on how to do this?

Upvotes: 0

Views: 75

Answers (1)

hbn
hbn

Reputation: 1956

You'll need a join. Something like this - note that any rows in Student that have a majorcode not in Major, or vice-versa, will not be included. If that's not what you want, look into outer joins.

SELECT majorTitle, username
FROM Student s
JOIN Major m ON s.majorcode = m.majorcode

You can of course add a WHERE clause to that query. Reference tables using the aliases ("s" for Student, "m" for Major) to avoid ambiguity.

Upvotes: 3

Related Questions