How do I alter an existing column and add a foreign key which is a Primary key to a table?

On Sunday, July 21, 2019, Karen Goh <karenworld(at)yahoo(dot)com> wrote:

> Hope that this is the right place to ask about this regarding how Ron
> suggested to do a Select something when it is not inside a table at all. He
> said this is something basic.
>

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.

So hope someone could clarify.
>

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.