I have associative table movies and their categories. How can i get movies that have category 1 and 2? Result: Answer Do GROUP BY. Use HAVING with COUNT DISTINCT to make sure both 1 and 2 are there. Just for fun, you can also do a self join: Or, use INTERSECT:
Tag: sql
Laravel – Get sum of a column without querying to database more than once
My table structure is like this: but now I want to move the gift to another table since the gift can be given to the users more than once in different dates. I have used $user->gift in many places in my project and I don’t want to change the code. So I want to create a method in User model
SQL to append records
I have two tables tbl1 and tbl2. Consider tbl2 as the main set and tbl1 has been derived from other sources but will essentially now be a subset of tbl2. tbl1 cd productcd type 1 1 A 1 2 AB 1 3 A 2 3 AB 2 4 AC 3 1 A tbl2 cd productcd type priority 1 1 A
How to Get Count(*) and table data based on where condition in single query
I’ve a stored procedure like this: In the above ex I’ll be getting the table data and the Count of total rows based on where in two queries. This is working fine, but in my actual case the select query is large and calling it twice, once for table and second for count like above is costing time. J…
I wanna create a new table from the data of two tables
I have a A table to store the product name, the quantity and input day of goods in stock. In adverse conditions, invoices for the above products will be sent later. I will put the product name and invoice quantity in B table. The problem here is that I want to check the quantity of goods with invoice and with…
How to use data from multiple similar columns as rows in SQL
I have a table as below: ItemName 1mth_presale_cost 2mth_presale_cost 1mth_postsale_cost 2mth_postsale_cost 1000 10.1 12.1 12.5 15.1 1001 20.2 15.2 25.2 17.3 I want the result to be like below table: ItemName 1mth_cost 2mth_cost 1000 10.1 12.1 1000 12.5 15.1 1001 20.2 15.2 1001 25.2 17.3 I don’t want to…
MariaDB Created view takes too long
I have a problem. I have a table with 6 million records in it. Every record has a column dateTime, and for my code I need the most recent 16 records in ascending order. This took too long to query directly out of the original table, so I created a view using the following query: This means that the view
SQL MAX(col1, col2) with priorities
I need to select row with MAX(val1) for each group. If there is more than one row with equal val1 = MAX(val1) I need to select from them row with MAX(val2), etc. In pseudo code it should be like this: For example: I have table nums with one TEXT column name and three INTEGER columns — id, num1 and num2
Full text search returning too many irrelevant results and causing poor performance
I’m using the full text search feature from Postgres and for the most part it works fine. I have a column in my database table called documentFts that is basically the ts_vector version of the body field, which is a text column, and that’s indexed with GIN index. Here’s my query: The diction…
Calculate total units sold and total sales value
Can you help me check my answers whether is this the right way? Im really new to database This is the question This is my answer The reason i use with read only constraint was to enhance the security! Answer The way I see it, query would look like this: If compared to yours: don’t select columns you don…