I having this issue when I try to retrieve data from two tables. DEPT Table EMP Table I want to use this query but it’s creating the data with all locations in table DEPT DATA Answer Basically your query is doing a CROSS JOIN which creates all possible combination of two tables. As DEPT table has 4 rows…
Tag: mysql
Deciding whether a user’s new score is their best score achieved on a specific activity
I am currently trying to create a database where the user will complete a series of activities, and their score from each activity will be stored in an ‘ActivityLog’ table, here is the current format: …
How to overlap NULL values in MYSQL when using group by?
This is my current table, let’s call it “TABLE” I want end result to be: I tried this query: but it doesn’t work i tried replacing NULL with 0 and then perform group by but “TBA” (text value) is creating problem, kindly help me out! Answer This looks like simple aggregation…
Find the ‘count of employees’ per department & name of those counted employees present in EMP table
Considering the already known EMP table, structure like: TABLE EMP(EMPNO, ENAME, DEPTNO) I want to fetch ‘count of employees’ department wise & name of employees present in that department …
Cron job to update row after 5 days
I’m having difficulty to make a cron job on cpanel to update the state of an user if the account has been created for 5 days. It just doesn’t work at all. When I program the cron job on the cpanel it …
Assign increasing integer values to a column in mysql database
We have an application where we have some learning path and each learning path is associated with some courses. The association between learning path and courses is stored in a mysql table. Now we also want to store course position in learning path to course mapping, so I have added another column ‘posi…
Selecting values used by all identifiers
I have a SQL table which contains UserID and AppID. Now i need a select query which selects all appid’s which are used by all userid’s For example: In this example I would only want AppID 35 because it’s used by all UserID’s. Answer You can do aggregation : Use distinct inside count() …
SQL Delete all rows in a table based on another table and another column value
table1: table2: Let me explain better, i want to delete all ORDER ID in table 2, based on all ID of table 1 if post_status = wc-refunded MYSQL VERSION: 5.7 EDIT: fixed with first solution from the @Tim Biegeleisen (THANKS!) Answer You could use exists logic: We can also try using delete join logic:
Regex to match duplicate/alias e-mails in MySQL
I am trying to come up with some regular expression to check whether an e-mail exists in a database. What is more specific here is that I want to find e-mails that are the same, but might be written differently. E.g. john.doe@example.com is the same as johndode+123@example.com and j.o.h.n.d.o.e@example.com. U…
Mysql query for getting monthwise n th highest spending customer
I am using sakila.payment table. Columns: payment_id, customer_id, staff_id, rental_id, amount, payment_date, update_date I am using this query to get customers spending the highest amount for each month. How can I get the Nth highest spending customer for each month? Answer Try the following, if you are usin…