Home Retrieve data from two tables in mysql. There is only one common column between the two tables

Questions

Retrieve data from two tables in mysql. There is only one common column between the two tables

November 10, 2023

I have a table "ttquestion" which has columns ‘qId’, ‘question’, ‘option1’, ‘option2’, ‘option3’, ‘difficulty’. The primary key is ‘qId’.
I have another table "level" which has columns ‘userId’ and ‘qId’. There is no primary key in this table.

I have to retrieve data from "ttquestion" and the below query is working fine. Now the query has to be modified such that I should get 12 records from "ttquestion" and should exclude ‘qId’ present in the table "level" for a specfic user.

SELECT * FROM (
    (SELECT * FROM ttquestion WHERE difficulty='1' ORDER BY RAND() LIMIT 4) 
    UNION ALL 
    (SELECT * FROM ttquestion WHERE difficulty='2' ORDER BY RAND() LIMIT 4) 
    UNION ALL 
    (SELECT * FROM ttquestion WHERE difficulty='3' ORDER BY RAND() LIMIT 4)
) 
ttquestion ORDER BY difficulty;

Kindly suggest as I am not good at SQL queries.

>Solution :

Try adding a NOT EXISTS clause in each subquery to filter out the ‘qId’ values that exist in the "level" table for a specific user.

SELECT * FROM (
   (SELECT * FROM ttquestion
   WHERE difficulty='1' AND NOT EXISTS (SELECT 1 FROM level WHERE level.qId = ttquestion.qId AND level.userId = 'exampleID')
   ORDER BY RAND() LIMIT 4)
   UNION ALL
   (SELECT * FROM ttquestion
   WHERE difficulty='2' AND NOT EXISTS (SELECT 1 FROM level WHERE level.qId = ttquestion.qId AND level.userId = 'exampleID')
   ORDER BY RAND() LIMIT 4)
   UNION ALL
   (SELECT * FROM ttquestion
   WHERE difficulty='3' AND NOT EXISTS (SELECT 1 FROM level WHERE level.qId = ttquestion.qId AND level.userId = 'exampleID')
   ORDER BY RAND() LIMIT 4)
) ttquestion ORDER BY difficulty;

replace 'exampleID'' with the actual user ID . this modified query ensures that the selected ‘qId’ values do not exist in the "level" table for the specified user.