-1

I have 3 tables. One with a list of users, one with items and one with relation between the users and the items. User can have an item (stat = 0/1) or can have a duplicate (dup = 0/1). If you click on the item's or the duplicate's button, it runs a query that adds or updates the row.

It looks like this:

| user | item_id | stat | dup |
| ---- | ------- | ---- | --- |
| 17   |   1     |  1   |  1  |
| 5    |   2     |  1   |  0  |
| 8    |   1     |  0   |  1  |
| 9    |   4     |  1   |  0  |

I compare these data who have an item what the other does not have and vica versa with this query:

SELECT t2.user_id,
       GROUP_CONCAT(CASE WHEN t1.dup=1 AND t2.stat=0 THEN item_id END) `My Item List`,
       GROUP_CONCAT(CASE WHEN t2.dup=1 AND t1.stat=0 THEN item_id END) `Item List`
FROM table3 t1
LEFT JOIN table3 t2 USING (item_id)
WHERE t1.user_id = @current_user
  AND t2.user_id <> @current_user
GROUP BY t2.user_id

My problem is I can only compare the rows if the user already clicked the button of the item at least once, so the row generated, but I need the stat=0 rows as well even if they are not existing.

After the code the table should look like this temporary, so I can use the query above to get the relations even if the user doesnt have (stat=0) that item:

| user | item_id | stat | dup |
| ---- | ------- | ---- | --- |
| 17   |   1     |  1   |  1  |
| 5    |   2     |  0   |  0  |
| 5    |   6     |  1   |  0  |
| 8    |   1     |  0   |  1  |
| 9    |   4     |  1   |  0  |
| 9    |   6     |  0   |  0  |
| 17   |   13    |  0   |  0  |

How can I integrate this to the query above?

My tables:

Table1

CREATE TABLE table1(
    id NOT NULL AUTO_INCREMENT,
    user_name varchar(255),
);

Table 2

CREATE TABLE table2(
    id NOT NULL AUTO_INCREMENT,
    item_no varchar(255),
    item_name varchar(255),
    item_group varchar(255)
);

Table 3

CREATE TABLE table3 (
  id int NOT NULL AUTO_INCREMENT,
  user_id int NOT NULL,
  item_id int NOT NULL,
  stat tinyint NOT NULL,
  dup tinyint NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES table1(id),
  FOREIGN KEY (item_id) REFERENCES table2(id)
);

Thank you!

0

1 Answer 1

0

This seems "wrong":

CREATE TABLE table3 (
  id int NOT NULL AUTO_INCREMENT,
  user_id int NOT NULL,
  item_id int NOT NULL,
  stat tinyint NOT NULL,
  dup tinyint NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES table1(id),
  FOREIGN KEY (item_id) REFERENCES table2(id)
);

May I suggest:

CREATE TABLE user_item (
  user_id int NOT NULL,
  item_id int NOT NULL,
  stat tinyint NOT NULL,
  PRIMARY KEY(user_id, item_id),
  INDEX(item_id, user_id),
  FOREIGN KEY (user_id) REFERENCES table1(id),
  FOREIGN KEY (item_id) REFERENCES table2(id)
);

and also remove dup from the other tables.

That will prevent duplicate user-item pairs.

If you need to "count" how often user-item pairs, you could use a ct INT NOT NULL column, and IODKU to upserting new pairs or bumping the count of existing pairs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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