Member Avatar for J3vr0N

Hi, I have a question regarding a SQL query. I am a complete noob, so please do not make fun of me.

Here is a schema that I created:

JOURNALS(j_num, j_name, j_subject_area, chief_editor_r_ssn)
ISSUES(j_num, i_num, i_date, i_num_pages)
RESEARCHERS(r_ssn, r_name, r_address, r_phone, r_institution)
ARTICLES(art_num, art_title, art_area_topic, art_abstract, j_num_submitted,date_submitted, j_num_published, i_num_published)
AREA_EDITS(j_num, r_sssn)
WRITES(r_ssn, art_num)
REVIEWS(r_ssn, art_num)
RESEARCH_SPECIALTIES(r_spec_name)
RRS(r_ssn, r_spec_name)

It's pretty simple. My question is regarding the linking of the primary keys. Is it necessary to link the primary keys if I were doing something like: The number of journals for which researcher ‘Albert Einstein’ is the chief editor?

The SQL query I came up with is:

SELECT count (*), j.j_num
FROM journals as J, Researchers as R, Area_edits as AE
WHERE J.chief_editor_r_ssn=R.r_ssn
AND R.r_name="Albert Einstein"
AND R.r_ssn=AE.r_ssn
AND J.j_num=AE.j_num;

Is it necessary to include the last 2 steps, or are the unneeded?

Edited by kvprajapati because: Add [code] tags. Encase your code in: [code] and [/code] tags.
  • 2 Contributors
  • 2 Replies
  • 220 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by J3vr0N

Dani AI

Generated

Short answer: the extra join to AREA_EDITS is not required for the count asked for, and is correct. Two important caveats follow: (1) mixing an aggregate like COUNT(*) with a non-aggregated column (e.g. j.j_num) requires GROUP BY and can produce confusing results; (2) adding unnecessary joins can either filter out rows (if using INNER JOIN) or inflate counts (if the joined table has multiple rows per journal).

A compact, safe pattern that avoids those problems is:

SELECT COUNT(DISTINCT j.j_num) AS journal_count
FROM JOURNALS j
JOIN RESEARCHERS r ON j.chief_editor_r_ssn = r.r_ssn
WHERE r.r_name = 'Albert Einstein';

Notes and pitfalls:

  • If matching by name is not guaranteed unique, match by r_ssn instead. Names can produce accidental duplicates.
  • Including AREA_EDITS with an inner join will remove journals that have no area_edits row; if the intention is to keep such journals, use a LEFT JOIN instead.
  • If multiple AREA_EDITS rows exist per journal, an inner join will multiply rows and thus inflate a plain COUNT(*); COUNT(DISTINCT j.j_num) or GROUP BY j.j_num fixes that.
  • In application code, pass the researcher identifier as a parameter (do not concatenate raw input) and ensure foreign-key columns used in joins are indexed for performance.

Summary: for the asked count, stick to JOURNALS + RESEARCHERS. Add AREA_EDITS only when data from it is actually needed, and be explicit about join type and de-duplication.

Member Avatar for baki100

Unneeded; because you require j_num from the journals tables you are already linking the table with researchers table. That join should suffice to return the information you required. If you wanted to return a value from the Area_Edits table then the second join would be necessary, the last join is completely unnecessary.

Member Avatar for J3vr0N

Unneeded; because you require j_num from the journals tables you are already linking the table with researchers table. That join should suffice to return the information you required. If you wanted to return a value from the Area_Edits table then the second join would be necessary, the last join is completely unnecessary.

Thank you very much for your help. That's what I figured, I was just unsure.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.