SQL Query to show documents for specific users when joined

user_1266449 0
Viewed 116

I have created a document upload system, where an organisation will tell a user "you need to upload these X documents". Then the user gets an email, and can go to the profile page of that organisation and upload each of these documents in its corresponding upload form. I have created 3 tables for this, vrm_document (this holds all the documents that are uploaded to the document system by organisations). vrm_document_user_link (this shows the documents linked from an organisation to a user). And vrm_document_user_upload (this holds the documents uploaded by the user to the organisation).

I cannot get my query right to show all the documents the user needs to upload together with the values whether it's uploaded already or not.

I have created a DBFiddle showing the query with the faulty data that is returning. In the example output of the DB Fiddle, the first row is returning a value for vrm_document_user_upload_id and document_path, while these are values for another user, but since the vrm_document_id matches, it shows these values here.

How do I solve this query?

This is the database structure I have created, together with the inserts:

CREATE TABLE vrm_document(
    `vrm_document_id` INT(11) NOT NULL AUTO_INCREMENT,
    `parent_vrm_document_id` INT(11) DEFAULT NULL,
    `is_default_document` TINYINT(3) DEFAULT '0',
    `country_id` INT(11) DEFAULT NULL COMMENT 'used only if document is a default document',
    `user_id` INT(11) DEFAULT NULL COMMENT 'user id of who it is created for, null for default documents',
    `user_auth_level` INT(11) DEFAULT NULL,
    `title` VARCHAR(255) NOT NULL,
    `version_name` VARCHAR(255) DEFAULT NULL,
    `description` VARCHAR(255) DEFAULT NULL COMMENT 'empty for certain document types',
    `vrm_document_type_id` INT(11) NOT NULL,
    `document_preview` VARCHAR(255) NULL,
    `document_preview_thumbnail` VARCHAR(255) NULL,
    `document_path` VARCHAR(255) DEFAULT NULL,
    `language_id` INT(11) DEFAULT NULL,
    `timestamp_created_utc` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `timestamp_modified_utc` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `create_user_id` INT(11) DEFAULT NULL COMMENT 'the user id of who created the document',
    `create_user_auth_level` INT(11) DEFAULT NULL,
    `create_user_id_toggle` INT(11) DEFAULT NULL COMMENT 'user id of who was toggled to to create the document',
    `create_user_auth_level_toggle` INT(11) DEFAULT NULL,
    PRIMARY KEY (`vrm_document_id`)
) ENGINE = InnoDB;

CREATE TABLE vrm_document_user_upload(
    `vrm_document_user_upload_id` INT(11) NOT NULL AUTO_INCREMENT,
    `from_user_id` INT(11) NOT NULL,
    `from_user_auth_level` INT(11) NOT NULL,
    `to_user_id` INT(11) NOT NULL,
    `to_user_auth_level` INT(11) NOT NULL,
    `vrm_document_id` INT(11) NOT NULL,
    `document_path` VARCHAR(255) NOT NULL,
    `vrm_document_upload_status_id` INT(11) NOT NULL DEFAULT 0,
    `reject_reason` VARCHAR(255) DEFAULT NULL,
    `timestamp_uploaded_utc` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `create_user_id` INT(11) DEFAULT NULL COMMENT 'the user id of who uploaded the document',
    `create_user_auth_level` INT(11) DEFAULT NULL,
    `create_user_id_toggle` INT(11) DEFAULT NULL COMMENT 'user id of who was toggled to to upload the document',
    `create_user_auth_level_toggle` INT(11) DEFAULT NULL,
    PRIMARY KEY (`vrm_document_user_upload_id`)
) ENGINE = InnoDB;

CREATE TABLE vrm_document_user_link(
    `from_user_id` INT(11) NOT NULL COMMENT 'the user id who attached the document to another user',
    `to_user_id` INT(11) NOT NULL COMMENT 'the user id who the document is attached to',
    `vrm_document_id` INT(11) NOT NULL,
    FOREIGN KEY (`vrm_document_id`) REFERENCES `vrm_document` (`vrm_document_id`)
) ENGINE = InnoDB;


INSERT INTO `vrm_document` (`vrm_document_id`, `parent_vrm_document_id`, `is_default_document`, `country_id`, `user_id`, `user_auth_level`, `title`, `version_name`, `description`, `vrm_document_type_id`, `document_preview`, `document_preview_thumbnail`, `document_path`, `language_id`, `timestamp_created_utc`, `timestamp_modified_utc`, `create_user_id`, `create_user_auth_level`, `create_user_id_toggle`, `create_user_auth_level_toggle`) VALUES (1, NULL, 1, 19, NULL, NULL, 'Vrijwilligerscontract', 'Vrijwilligerscontract - Give a Day', 'Standaard vrijwilligerscontract aangeboden door Give a Day.', 2, NULL, NULL, '/vrm/documents/default_documents/180130_Uitnodiging_Aventi-in-beweging_v2.pdf', 14, '2020-03-27 14:53:19', '2020-03-27 14:53:19', NULL, NULL, NULL, NULL);
INSERT INTO `vrm_document` (`vrm_document_id`, `parent_vrm_document_id`, `is_default_document`, `country_id`, `user_id`, `user_auth_level`, `title`, `version_name`, `description`, `vrm_document_type_id`, `document_preview`, `document_preview_thumbnail`, `document_path`, `language_id`, `timestamp_created_utc`, `timestamp_modified_utc`, `create_user_id`, `create_user_auth_level`, `create_user_id_toggle`, `create_user_auth_level_toggle`) VALUES (9, NULL, 0, NULL, 2, 5, 'Nieuw doc als test', 'v1', 'Dit is een test', 2, NULL, NULL, 'vrm/documents/uploaded_documents/2020/03/template for evaluation of KBC Minimal Data Security requirements v2.0-DRAFT1-27032020163601.docx', 14, '2020-03-27 17:36:01', '2020-03-27 17:36:01', 1, 1, 2, 5);
INSERT INTO `vrm_document` (`vrm_document_id`, `parent_vrm_document_id`, `is_default_document`, `country_id`, `user_id`, `user_auth_level`, `title`, `version_name`, `description`, `vrm_document_type_id`, `document_preview`, `document_preview_thumbnail`, `document_path`, `language_id`, `timestamp_created_utc`, `timestamp_modified_utc`, `create_user_id`, `create_user_auth_level`, `create_user_id_toggle`, `create_user_auth_level_toggle`) VALUES (10, NULL, 0, NULL, 2, 5, 'Attest goed gedrag en zeden', '', 'Het attest van gedrag en goede zeden moet opgevraagd worden en terug opgeladen worden voor elke vrijwilliger die start bij ons. ', 3, NULL, NULL, NULL, 14, '2020-03-27 18:40:42', '2020-03-27 18:40:42', 1, 1, 2, 5);
INSERT INTO `vrm_document` (`vrm_document_id`, `parent_vrm_document_id`, `is_default_document`, `country_id`, `user_id`, `user_auth_level`, `title`, `version_name`, `description`, `vrm_document_type_id`, `document_preview`, `document_preview_thumbnail`, `document_path`, `language_id`, `timestamp_created_utc`, `timestamp_modified_utc`, `create_user_id`, `create_user_auth_level`, `create_user_id_toggle`, `create_user_auth_level_toggle`) VALUES (12, NULL, 0, NULL, 2, 5, 'test type 3', '', 'test voor type 3', 3, NULL, NULL, NULL, 14, '2020-03-31 07:19:14', '2020-03-31 07:19:14', 1, 1, 2, 5);
INSERT INTO `vrm_document` (`vrm_document_id`, `parent_vrm_document_id`, `is_default_document`, `country_id`, `user_id`, `user_auth_level`, `title`, `version_name`, `description`, `vrm_document_type_id`, `document_preview`, `document_preview_thumbnail`, `document_path`, `language_id`, `timestamp_created_utc`, `timestamp_modified_utc`, `create_user_id`, `create_user_auth_level`, `create_user_id_toggle`, `create_user_auth_level_toggle`) VALUES (64, NULL, 0, NULL, 2, 5, 'ooooooooo111', '', 'aezfs<wvcxcvw', 3, NULL, NULL, NULL, 14, '2020-04-03 12:21:06', '2020-04-03 12:21:06', 1, 1, 2, 5);



INSERT INTO `vrm_document_user_link` (`from_user_id`, `to_user_id`, `vrm_document_id`) VALUES (2, 24, 1);
INSERT INTO `vrm_document_user_link` (`from_user_id`, `to_user_id`, `vrm_document_id`) VALUES (2, 24, 9);
INSERT INTO `vrm_document_user_link` (`from_user_id`, `to_user_id`, `vrm_document_id`) VALUES (2, 24, 10);
INSERT INTO `vrm_document_user_link` (`from_user_id`, `to_user_id`, `vrm_document_id`) VALUES (2, 24, 12);
INSERT INTO `vrm_document_user_link` (`from_user_id`, `to_user_id`, `vrm_document_id`) VALUES (2, 24, 64);


INSERT INTO `vrm_document_user_upload` (`vrm_document_user_upload_id`, `from_user_id`, `from_user_auth_level`, `to_user_id`, `to_user_auth_level`, `vrm_document_id`, `document_path`, `vrm_document_upload_status_id`, `reject_reason`, `timestamp_uploaded_utc`, `create_user_id`, `create_user_auth_level`, `create_user_id_toggle`, `create_user_auth_level_toggle`) VALUES (5, 1, 1, 2, 5, 1, 'vrm/documents/user_uploaded_documents/2020/04/helpende-handen-werf-44-01042020125653-07042020162326.docx', 1, NULL, '2020-04-07 16:23:26', 1, 1, NULL, NULL);

And this is the Query I am trying to give me correct results:

SELECT vdul.*, vd.title, vd.description, vduu.vrm_document_user_upload_id, vduu.document_path
FROM vrm_document_user_link AS vdul
LEFT JOIN vrm_document_user_upload AS vduu ON vdul.vrm_document_id = vduu.vrm_document_id
LEFT JOIN vrm_document AS vd ON vdul.vrm_document_id = vd.vrm_document_id
WHERE vdul.from_user_id = 2
AND vdul.to_user_id = 24
AND vd.vrm_document_type_id != 1

Update second query after response from Balmar:

SELECT vdul.*, vd.title, vd.description, vduu.vrm_document_user_upload_id, vduu.document_path
FROM vrm_document_user_link AS vdul
LEFT JOIN vrm_document_user_upload AS vduu ON ((vdul.vrm_document_id = vduu.vrm_document_id) AND (vdul.from_user_id = 2 AND vdul.to_user_id = 24))
LEFT JOIN vrm_document AS vd ON vdul.vrm_document_id = vd.vrm_document_id
WHERE vd.vrm_document_type_id != 1

DB Fiddle link: https://www.db-fiddle.com/f/f4es4LDfFE7HUMrnSPbJKw/0

Update DB fiddle to include the user_ids in the JOIN statement: https://www.db-fiddle.com/f/f4es4LDfFE7HUMrnSPbJKw/2

mariadb mysql sql
1 Answers

You need to add the user IDs to the join condition, so you only get documents for the same users.

SELECT vdul.*, vd.title, vd.description, vduu.vrm_document_user_upload_id, vduu.document_path
FROM vrm_document_user_link AS vdul
LEFT JOIN vrm_document_user_upload AS vduu 
    ON vdul.vrm_document_id = vduu.vrm_document_id 
        AND vdul.to_user_id = vduu.to_user_id 
        AND vdul.from_user_id = vduu.from_user_id
LEFT JOIN vrm_document AS vd 
    ON vdul.vrm_document_id = vd.vrm_document_id 
        AND vd.vrm_document_type_id != 1
WHERE vdul.from_user_id = 2
AND vdul.to_user_id = 24
user_14918950
Related