need help with beginner-level SQL query - return all records not referenced in another table
$10-30 USD
Paid on delivery
Just a quick SQL query question that's a bit beyond my level, but I'm sure it's pretty simple. So I am willing to pay for the answer to this question as a "software project".
(This is a slight variation on a "project" I posted earlier, but the winning "bidder" turned out not to be able to solve it, and I can't "unlock" it from that bidder, so I had to start over.)
Suppose I have two tables named "promo" and "promo_has_been_displayed_to_user". "promo" stores records for advertising promotions; the table "promo_has_been_displayed_to_user" has two columns, promo_id and user_id, and it is used to track that a particular promotion has been displayed to a particular user. The idea is that I don't want to display the same promotion to the same user twice. So once a given promotion is shown to a given user, we store a record in "promo_has_been_displayed_to_user" as a reminder not to show that promotion to that user again.
So: Given the ID of a particular user, what is the query that I would write, that would return the records for *all* of the promotions in "promo", where there is NOT a corresponding record for that promo and that user in "promo_has_been_displayed_to_user"?
For example, if I have this data in the tables;
mysql> select * from promo;
+------+
| ID |
+------+
| 634 |
| 290 |
| 241 |
| 154 |
+------+
mysql> select * from promo_has_been_displayed_to_user;
+----------+---------+
| promo_id | user_id |
+----------+---------+
| 634 | 45 |
| 241 | 45 |
| 154 | 56 |
+----------+---------+
and I want the records for promotions that have not been displayed to user 45. What is the query that would return records 290 and 154 from table promo, but would NOT return 634 and 241, because 634 and 241 have already been shown to user 45?
I have these tables set up already, so whatever answer you give me, I'm just going to copy and paste it onto my MySQL prompt, and verify that it returns 290 and 154. If you want to double-check that your query actually works, you can create the tables yourself in less than a minute, and run your query and verify it returns the right answer.
Project ID: #13247351
About the project
22 freelancers are bidding on average $54 for this job
Hello there, I'm on mobile now but this will do the trick: SELECT * FROM promo WHERE ID NOT IN ( SELECT promo_id from promo_has_been_displayed_to_user WHERE user_id = 45) Contact me and let me know that it wor More
what is a SQL query that would return all records from a table named \'employee\' where the field last_name is set to NULL? (I know the answer, I\'m just using the screening question to cut down on spam from \"bidders More
hi, Ready to start right now. I can provide quality and standard work. Experienced in mysql. Please PM me for more details. I can complete it in just 10 min. Thank you
what is a SQL query that would return all records from a table named \'employee\' where the field last_name is set to NULL? (I know the answer, I\'m just using the screening question to cut down on spam from \"bidders More
what is a SQL query that would return all records from a table named \'employee\' where the field last_name is set to NULL? (I know the answer, I\'m just using the screening question to cut down on spam from \"bidders More
So basically you want to select all from A where not in B? real simple query, award this project and you'll be in your way in 3 minutes! -Seth
I am curious why it didn't work with the previous bidder but I am willing to help. Did he/she used nested sql? How about outer join?
what is a SQL query that would return all records from a table named \'employee\' where the field last_name is set to NULL? (I know the answer, I\'m just using the screening question to cut down on spam from \"bidders More
