How do I alter an existing column and add a foreign key which is aPrimary key to a table? - Mailing list pgsql-sql

As noted again below you seems to have missed the meaning of:

FROM tutor_subject, s_tutor

The meaning of that is indeed fundamental SQL; if you simply missed it that happens but if you haven’t learned that you should, as advised, elsewhere, read or re-read introductory materials that teach “FROM tbl1, tbl2” joins amongst other concepts that at worse could probably use a refresher. 

If you continue to learn sql via mailing list can you please at least follow the conventions others here have been using.  Namely to quote only the relevant portions of the text you are replying to and to add your comments for the reply AFTER the material you quoted, NOT BEFORE.  I.e., Bottom-post instead of Top-post.  It also seems like your mail client isn’t distinguishing the quoted material from your reply.  Having it do that better would be a big help too.

The suggestion to use this list instead of the admin list has been adequately explained elsewhere.

On to the point here: You’ve provided a model but still haven’t told us, in your own prose (i.e., not SQL) what it is you are trying to accomplish here.  You have a TUTOR table (not sure why you went with an s_ prefix) and a tutor_subject table.  You are lacking a SUBJECT table where subject_id is the PK.  You then lack stated questions that those three tables are capable of answering.

As for Ron’s query, it is correct but regardless you still really have not told us what you are trying to do, show us what you did, and explain in detail what parts specifically are confusing between your attempt and your expectations and you will get good help.  Continue to be vague and the answers you get will either be confusing, unhelpful, or wrong as you’ve experienced.

The canonical way to obtain the answer to the query: provide me a list of tutors, along with the zipcodes, who tutors the subject X, listing each tutor only once.

SELECT s_tutor.tutor_id, s_tutor.zipcode

FROM s_tutor

WHERE EXISTS (

SELECT 1

FROM tutor_subject

WHERE tutor_subject.tutor_id = s_tutor.tutor_id

AND tutor_subject.subject_id = ‘X’

)

Using an actual join, as Ron, did requires (in addition to fixing his typo) removing the duplicates that are introduced due to the join on tutor_subject.

SELECT DISTINCT tutor_id, s_tutor.zipcode

FROM s_tutor

JOIN tutor_subject USING (tutor_id) — personally learn and use the more expressive join clauses instead of shoving all of the relations into FROM and polluting the WHERE clause with join conditions; and apparently in. the “FROM tutor_subject, s_tutor” expression that Ron wrote you either didn’t see the “, s_tutor” or understand what it meant.  It, combined with “WHERE (tutor_subject.tutor_id = s_tutor.tutor_id)” forms the exact same INNER JOIN shown here (with the exception of this form only outputting tutor_id once)

WHERE tutor_subject.subject_id = ‘X’;

The DISTINCT is actually not required for a single subject because a single tutor_id can only be linked to a given subject_id once so no duplicates will appear.  But if there is more than one subject_id a single tutor that teaches both will generate two output matches when you probably only want the one (again, I’m making an assumption here because you have not stated your question).

A more useful way to accomplish the above in the face of multiple subject_id values is:

SELECT s_tutor.tutor_id, s_tutor.zipcode, array_agg(tutor_subject.subject_id) AS matched_subjects

FROM s_tutor

JOIN tutor_subject USING (tutor_id)

WHERE tutor_subject.subject_id IN (‘X’,’Y’,’Z’)

GROUP BY 1, 2;

That only matters if you wish to know which conditions matched; otherwise the simple DISTINCT works or, preferred, the semi-join/EXISTS form of the query so only s_tutor records are returned regardless of how many matching records EXISTS in the subquery containing tutor_subject).

Tutor_subject: FOREIGN KEY (tutor_id) REFERENCES s_tutor (tutor_id)

A given zipcode matches with a given tutor_id on the tutor_subject table because that tutor_id IS THE EXACT SAME ID as is found on s_tutor.  That said it only works walking from tutor_subject since two different tutor_id values can have the same zipcode but a single tutor_id can only have one zip code (i.e., many-to-one relationship)

Your output query really wants to include tutor_id, outputting zipcode by itself would largely be pointless though I could see using it as a grouping key and showing count(tutor_id)...

David J.