Skip to content

Tag: mysql

How to insert an array value to an existing JSON document?

Given a column containing a JSON document, I can use JSON_SET() to set a key in the JSON document to a value. Supported values are null, true, false, numbers and strings; but I can’t figure out how to set an array. The example in the documentation (for JSON_INSERT(), but it works the same) weirdly shows…

Integrity constraint violation: 1052

Integrity constraint violation: 1052 Column ‘id’ in where clause is ambiguous (SQL: select * from kumeti_member_registrations left join members list on members list.id = kumeti_member registrations.member_name where id = 2)”, this Query please solve the error Answer Try this:

Is there way I can SELECT and JOIN COUNT different queries?

I’m using MYSQL and I have a message table like below. id parent_id sender_id receiver_id content readed sender_deleted receiver_deleted created 18 0 6 1 testab 0 0 0 2021-10-28 01:13:42 19 18 6 1 testcd 0 0 0 2021-10-28 01:14:55 I’m trying to combine two queries in one. Selecting and count where …

Join mysql table with distinct value from another table

I encountered a problem on a database I am working with. I have a table of counsels which may hold repeating values, but their is an enrolment number filed which is unique and can be used to fetch them. However, I want to join from a cases_counsel table on the “first” unique value of the counsel t…

Why are Duplicates not being filtered out

I am working on some practice interview Questions and am struggling with this: You are working with a company that sells goods to customers, and they’d like to keep track of the unique items each customer has bought. The database is composed of two tables: Customers and Orders. The two table schemas are…

Excluding overlapped period in doctrine QueryBuilder

I have Product and Booking entity and I’m trying to select products that aren’t already booked in a given period. In other words, to select available products during a given period. To make myself understandable, see the schema below. I only want to select product that have a booking like #1 or #5…

Group by Latest value

I only want to show the latest value. Here is the table: InstanceID InstanceDate TotalProjects 1 2021-10-14 69 1 2021-10-12 70 2 2021-10-14 20 3 2021-10-14 89 3 ​ 2021-10-13 99 3 2021-10-11 100 4 ​ 2021-10-12 12 4 2021-10-14 13 5 ​ 2021-10-14 0 Here is the query I used to get this table: This is what I want:…

Add new column in SQL query

I want to use the result of query to create new column in order to to find the change of freight to the revenue The original question is What is the impact on revenue of the additional 10% in freight. I am currently using Northwind dataset SQL query does not work as it does not recognize new variable “N…