0

I have 3 tables in SQLServer that I want to join so I can count the number of times each SUBJ_NAME occurs for the ITEM_IDs in the LOAN table.

CREATE TABLE SUBJT (
    SUBJ_ID CHAR(4) PRIMARY KEY,
    SUBJ_NAME VARCHAR(25) NOT NULL,
    SUBJ_DESCRIPT VARCHAR(500)
)
CREATE TABLE ITEM_SUBJECT (
    ITEM_ID CHAR(8),
    SUBJ_ID CHAR(4),
    PRIMARY KEY (ITEM_ID, SUBJ_ID),
    FOREIGN KEY (ITEM_ID) REFERENCES ITEM,
    FOREIGN KEY (SUBJ_ID) REFERENCES SUBJT
)
CREATE TABLE LOAN (
    LOAN_ID INT PRIMARY KEY,
    PAT_ID INT NOT NULL,
    ITEM_ID CHAR(8) NOT NULL,
    VOL_ID INT NOT NULL,
    ITEM_OUTD DATE,
    ITEM_RTD DATE,
    ITEM_REN INT
    FOREIGN KEY (PAT_ID) REFERENCES PATRON,
    FOREIGN KEY (ITEM_ID) REFERENCES ITEM,
    FOREIGN KEY (VOL_ID) REFERENCES VOLUNTEER
)

I joined the tables with this query:

SELECT a.ITEM_ID, c.SUBJ_NAME
FROM LOAN AS a
JOIN ITEM_SUBJECT AS b ON a.ITEM_ID = b.ITEM_ID
JOIN SUBJT AS c ON b.SUBJ_ID = c.SUBJ_ID
ORDER BY SUBJ_NAME

But how do I add the count?

0

This should do it:

SELECT   a.ITEM_ID, 
         c.SUBJ_NAME,
         COUNT_BIG(c.SUBJ_NAME) AS COUNT_SUBJ_NAME
FROM     LOAN AS a
JOIN     ITEM_SUBJECT AS b
    ON a.ITEM_ID = b.ITEM_ID
JOIN     SUBJT AS c
    ON b.SUBJ_ID = c.SUBJ_ID
GROUP BY a.ITEM_ID. c.SUBJ_NAME;
0

I had to change your tables a little bit. Check if you can that the foreign keys relate to columns exactly the same data type to avoid conversions.

CREATE TABLE SUBJT (
    SUBJ_ID CHAR(4) PRIMARY KEY,
    SUBJ_NAME VARCHAR(25) NOT NULL,
    SUBJ_DESCRIPT VARCHAR(500)
)

--DROP TABLE ITEM

CREATE TABLE ITEM
(
    ITEM_ID CHAR(8) PRIMARY KEY,
    ITEM_NAME VARCHAR(25) NOT NULL,
    ITEM_DESCRIPT VARCHAR(500)
)


CREATE TABLE ITEM_SUBJECT (
    ITEM_ID CHAR(8),
    SUBJ_ID CHAR(4),
    PRIMARY KEY (ITEM_ID, SUBJ_ID),
    CONSTRAINT FK_01  FOREIGN KEY(ITEM_ID) REFERENCES ITEM(ITEM_ID),
    CONSTRAINT FK_02 FOREIGN KEY (SUBJ_ID) REFERENCES SUBJT(SUBJ_ID)
)


CREATE TABLE LOAN (
    LOAN_ID INT PRIMARY KEY,
    PAT_ID INT NOT NULL,
    ITEM_ID CHAR(8) NOT NULL,
    VOL_ID INT NOT NULL,
    ITEM_OUTD DATE,
    ITEM_RTD DATE,
    ITEM_REN INT
    --FOREIGN KEY (PAT_ID) REFERENCES PATRON,
    FOREIGN KEY (ITEM_ID) REFERENCES ITEM
    --FOREIGN KEY (VOL_ID) REFERENCES VOLUNTEER
)

-- i did it like this:

SELECT a.ITEM_ID, c.SUBJ_NAME
,count_ITEM_ID=count_big(a.ITEM_ID) as 
FROM LOAN AS a
INNER JOIN ITEM_SUBJECT AS b ON a.ITEM_ID = b.ITEM_ID
INNER JOIN SUBJT AS c ON b.SUBJ_ID = c.SUBJ_ID
GROUP BY a.ITEM_ID, c.SUBJ_NAME
ORDER BY C.SUBJ_NAME

make sure you have the right indexes, try to get rid of that sort operator:

enter image description here

0

If you want to see how many subj_names are there for a given item_id, you can go like this:

SELECT   a.ITEM_ID, 
         COUNT(c.SUBJ_NAME) AS COUNT_SUBJ_NAME
FROM     LOAN AS a
JOIN     ITEM_SUBJECT AS b
    ON a.ITEM_ID = b.ITEM_ID
JOIN     SUBJT AS c
    ON b.SUBJ_ID = c.SUBJ_ID
GROUP BY a.ITEM_ID

However, in this case, you will only see an ITEM_ID and count of SUBJ_NAMEs. Example below:

ITEM_ID    COUNT_SUBJ_NAME
  1             3
  2             2
  3             1

If you would like to retain information about SUBJ_NAMEs you can use window aggregate function:

SELECT   a.ITEM_ID, 
         c.SUBJ_NAME,
         COUNT(c.SUBJ_NAME) OVER (PARTITION BY a.ITEM_ID) AS COUNT_SUBJ_NAME
FROM     LOAN AS a
JOIN     ITEM_SUBJECT AS b
    ON a.ITEM_ID = b.ITEM_ID
JOIN     SUBJT AS c
    ON b.SUBJ_ID = c.SUBJ_ID

This would preserve detail information (no GROUP BY clause), but still give you a count of subj_names for item_id. Example below:

ITEM_ID  SUBJ_NAME  COUNT_SUBJ_NAME
  1          a            3
  1          b            3
  1          c            3
  2          a            2
  2          b            2
  3          a            1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.