MYSQL关系和查询问题

User has many items but items have many categories.

I'm looking to display a table in the user account that finds the number of items for each item:

-------------------------------
| Item Name | Number of Items |
-------------------------------
|  Item 1   |        24       |
|  Item 2   |        18       |
|  Item 3   |        6        |
-------------------------------

I can't work it out... The user has multiple items but only one identifier in the user table

Does the User table have an item_id which then relates to user_items table? The user_items table has three fields: item_id, item name and number of items?

Anyone help a nublet out?

Thanks

• If an item can "belong" to at most one user, just store directly in the item table a FK into the user table:

  CREATE TABLE item (
    item_id   SERIAL PRIMARY KEY,
    item_name VARCHAR(20),
    user_id   BIGINT UNSIGNED, -- NOT NULL if every item must belong to a user
    FOREIGN KEY (user_id) REFERENCES user (user_id)
  );
  

  Then, to fetch the number of items per user:

  SELECT user_id, COUNT(*) AS num_items FROM item GROUP BY user_id;
  

  If you wish also to fetch associated user information from the user table, you merely need perform a SQL join:

  SELECT   user.*, COUNT(*) AS num_items
  FROM     user JOIN item USING (user_id)
  GROUP BY user_id;
  

• If an item can simultaneously "belong" to multiple users, store in your user_items table FKs into both the user and the item tables.

  CREATE TABLE user_items (
    item_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    FOREIGN KEY (item_id) REFERENCES item (item_id),
    FOREIGN KEY (user_id) REFERENCES user (user_id)
  );
  

  Then to fetch the number of items per user:

  SELECT user_id, COUNT(*) AS num_items FROM user_items GROUP BY user_id;
  

  Or, conversely, the number of users per item:

  SELECT item_id, COUNT(*) AS num_users FROM user_items GROUP BY item_id;
  

  Again, you can perform a JOIN to fetch associated information from other table(s) as required.

• Don't store in the user table a FK into the item table unless a user can have at most one item.

  CREATE TABLE user (
    user_id SERIAL PRIMARY KEY,
    item_id BIGINT UNSIGNED      -- don't do this
  );
  

Users - stores user information only, Items - stores item information only, User_items - Stores userId and itemId

The basic query would be like below, you could then display the results by item. The SELECT * returns all the rows from all the tables. So you would get all the user, item and user_items information.

SELECT * FROM user_items
JOIN items on user_items.itemId = items.itemId
WHERE userId = $yourUserID
GROUP BY itemId