0

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 has already clicked the buttons at least once, so the row generated, but I need the stat=0 and dup=0 rows as well even if they are not existing.

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)
);

1 Answer 1

0

Sorry, not an expert in MySQL, so I can only show you the idea:

Such task is usually done with a subquery which creates a list of primary keys, and use that subquery as a primary in the from, other tables are added as outer joins:

SELECT pks.user_id, pks.item_id, t.stat, t.dup
FROM (select t1.id as user_id, t2.item_id from table1 t1, table2 t2) pks
OUTER JOIN table3 t on pks.user_id = t.user_id and pks.item_id = t.item_id

Here, third and fourth columns would have 0/1 if the table3 already has a record for the user/item pair and NULLs for cases without a corresponding record in table3.

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.