13142

In PostgreSQL how do you join two tables select separate information

Having trouble with joins.

I have a table called subjects

subno subname 30006 Math 31445 Science 31567 Business

I also have a another table called enrollment

subno sno 30009 980008 4134 988880

etc..

how to list subject numbers and subject names for student 9800007 ?

Answer1:

Try this

select * from subjects s left join enrollment e on s.subno = e.subno where sno=9800007

Answer2:

If you want to return zero rows for students without an enrolment, use a LEFT [OUTER] JOIN, eg:

SELECT e.sno, s.subno, s.subname FROM enrollment e LEFT OUTER JOIN subjects s ON s.subno = e.subno WHERE e.sno=988880;

To return no rows for students without enrolments, use an INNER JOIN:

SELECT e.sno, s.subno, s.subname FROM enrollment e INNER JOIN subjects s ON s.subno = e.subno WHERE e.sno=988880;

Note that join order is important for outer joins (RIGHT [OUTER] JOIN and LEFT [OUTER] JOIN - the OUTER keyword is optional) but not for INNER JOIN. For that reason, @swetha's answer has a problem: the join order is reversed if you're looking for information about a student.

See this SQLFiddle

Recommend

  • Delete Blank Rows from Column B
  • Hyperledger, Using Google OAUTH2.0 with a REST server
  • CouchDB Full Text Search
  • SQL query statement
  • MATLAB can't seem to find csaps() in MATLAB 7.10.0 student edition
  • What is 1NF truly?
  • PHP Contact Form not submitting
  • SQL Count. How can I count how many distinct values are in a table when an other two columns are mat
  • Fatal error: Call to a member function fetch() on a non-object?
  • Linked tables and Slicer in excel
  • Angular page doesn't refresh after data is added or removed
  • How to map Request parameter in Spring?
  • Python getting common name from URL using ssl.getpeercert()
  • JQuery .html() remove line break on IE 8
  • Why isn't obj.style.left = “200px”; working in this code?
  • Javascript, Regex - I need to grab each section of a string contained in brackets
  • Floated image with variable width and heading with background image
  • Remove final comma from string in vb.net
  • Installed module is empty
  • how to display data from 1st point on words on y axis for line chart in d3.js
  • Blackberry - Custom EditField Cursor
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Listbox within Listbox and scrolling trouble in Windows Phone 7 Silverlight
  • Disable Enter in editText android
  • Splitting given String into two variables - php
  • NetLogo BehaviorSpace - Measure runs using reporters
  • Is my CUDA kernel really runs on device or is being mistekenly executed by host in emulation?
  • How to check if every primary key value is being referenced as foreign key in another table
  • Which linear programming package should I use for high numbers of constraints and “warm starts” [clo
  • Build own AppleScript numerical error handling
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '
  • python regex in pyparsing
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app
  • How to push additional view controllers onto NavigationController but keep the TabBar?