Structured Query Language is a language for interacting with relational databases. Read the tag wiki's guidelines for requesting SQL reviews: 1) Provide context, 2) Include the schema, 3) If asking about performance, include indexes and the output of EXPLAIN SELECT.

learn more… | top users | synonyms

0
votes
0answers
6 views

SQL query with nested subqueries

The following query is taking over 800ms to run, and returning 300 rows. ...
0
votes
0answers
13 views

SQL query with GROUP BY to show badges and points by user and course

I have the following database schema. My goal is to obtain a result set that lists the total badge points earned by each user. The badges might be earned in different courses. I want to include the ...
4
votes
1answer
50 views

SQL query that flattens many-to-one relationships

I have a database with these tables, where each mapping table represents a "many-to-one" relationship between the table on the right and the table on the left (click to enlarge): I am selecting a ...
1
vote
2answers
34 views

Select random row using PDO

What do you think of this way to generate a random number from the total rows in a table? I would like to create a page that random.php precisely generates a random ID and shows it. ...
0
votes
1answer
41 views

Get time slots based on multiple workshops

I have a list of Workshops, each having open days (Monday, Tuesday etc.) and open time and close time (which will be same for each day). Now, Based on the current time, I need to find out next 7 days ...
2
votes
1answer
18 views

Query column names and then using them in statements

I currently have a table with over 250 columns, in which ~10-15 columns have column names that have a specific prefix, namely EQP_. Assume that you do not want to ...
0
votes
1answer
26 views

Get properties from all children in a recursive SQL structure

In our product, Company is a model that can have many Companies belonging to it; so some Companies are parents, some are children, and many are both. We have a ...
5
votes
2answers
77 views

Adding an item to database + JSON

Is this code good? Or is it the noobiest PHP you've ever seen? ...
3
votes
2answers
247 views

Selecting all movies where specified actors worked together

Here's a query I wrote trying to find all movies that three actors have been in together: ...
2
votes
0answers
29 views

Altermate to CURSOR - SQL

I am currently using an iterator because I have to insert record in one table, get its primary key and add it to another table. But since it's a loop, it's very slow when data is very big. ...
3
votes
2answers
29 views

Fetch records that do not get matched in another table

I'm trying to fetch records from table PaymentToken if its AccountNumber matched but ...
2
votes
0answers
30 views

Retrieving the most recent posts from the users someone is following

I have two different implementations for retrieving posts from the users someone is following and I want to know which one you would recommend using. First I'll show you the tables, and then the two ...
1
vote
0answers
12 views

Table Trigger to update date columns

I've created the following trigger to force two columns (date_edited and date_deleted) to have the ...
3
votes
0answers
43 views

Checkout process

I'm building a checkout process where I am quite frequently making SQL connections based on user input so this is quite important. I want to know if it's well-protected from any SQL injection or other ...
1
vote
0answers
20 views

Exclusive row lock

I'm in a situation where I need to manage a kind of FIFO process within a SQL procedure. The procedure is called by a multithreaded windows application. I've created a table which look like this: ...
3
votes
0answers
31 views

Selecting the most recent record for each product

I have the following structure of a table: ...
1
vote
3answers
45 views

User-tracking history query

I have a query consisting of UNIONs. ...
0
votes
0answers
7 views

Associative table in SQL Server [migrated]

If I have 4 tables (Army, Navy, Air Force, Marines), all have serial number as primary key, and name. And I have a table operation which has op_num as primary key and op_name. I also have a table ...
1
vote
0answers
28 views

Current .NET session username injection in SQL Server stored procedures

I have a legacy WCF service that is ported from a security model using database logins and roles to a custom security. One effect of this change is that stored procedure are no longer executed using ...
8
votes
1answer
96 views

Top 10 editors partitioned by year

I wanted to get some stats on who has done the most edits on Code Review, so I wrote the following query on SE Data Explorer. All improvement suggestions are welcome. Note that I used a cursor to ...
0
votes
1answer
51 views

Finding all delivery orders with multiple purchase orders

I have a SQL query to find all delivery orders with multiple purchase orders (one-to-many). Is there a better or shorter way to write this? ...
2
votes
0answers
19 views

Generating days in a specific range

I have a query in MSSQL, where I get a summary by days from a range in a table, and this is in union with other queries. This query is used to generate days in a specific range, then add the registry ...
1
vote
2answers
33 views

Displaying a greeting to one user selected from a database

I execute the following SQL statement to get back an data object into a variable. ...
2
votes
1answer
36 views

Method to act on parameter or from iterable if not parameters are passed

The scenario is as follows. The API of my program will allow the user to perform some method on a particular element (let's say a tuple from a database). If this parameter is not passed to the ...
4
votes
2answers
106 views

DAO to create, update, and delete a project

I'm trying to write my first "complex" program in Java. So far, I created a local H2 database but I'm not sure about the quality of my code (I'm newbie in Java) because I don't know how I can deal ...
2
votes
2answers
208 views

Update user country based on IPv4 decimal address

I have UserSignatures, a table with clientIPs, some of which have CountryIds that are null ...
0
votes
1answer
26 views

Get parent folder ids by folder id(s)

My Scenario: #TmpDistinctFolderIds table contains one or many FolderId. ...
2
votes
2answers
63 views

TSQL query to gather amounts for an employees, spouses, and children in one row

I have a query that works as intended, kind of pulling from multiple records in a table, and flattening the data into one row. I am wondering if there is a faster/more semantically correct way to do ...
4
votes
2answers
97 views

Check existence of a row then update a column

not too experienced with SQL, wondering if I can make this better... I am trying to check if a row exists and then add one to the frequency column if it does, if it does not exist it should return ...
1
vote
1answer
57 views

Disposing of an SQL dataset

I am getting a compiler warning when running my code analysis. It is CA2202. It is saying that I have not properly disposed my dataset through all exception paths. Here is the code: ...
1
vote
1answer
41 views

Fetch data from multiple tables

I have three primary tables: Customer Product Order Relations: Many to many between product and order. ...
3
votes
2answers
78 views

Displaying recent buyers' feedback on games

I have the following code made to output the following: Code: ...
3
votes
1answer
55 views

Simple shop database site

I'm learning PHP, HTML and SQL - and wondered if anyone could take a look at this code and give me some feedback? ...
3
votes
0answers
39 views

SqlDataConnection update method

I'm using Microsoft's SqlDataConnection type provider. However, the only way I've seen to update a row in the database is to use something similar to this where it updates mutable values: ...
3
votes
1answer
15 views

SQL report of subscribers and their associated revenue

A user can have many subscriptions, and a subscription belongs to one plan. The plans table tells us a subscription's cost. Is the query below the correct one for ...
6
votes
2answers
163 views

Saving and Loading data with SQLite

I'm in the process of learning SQLite in C# and I've come up with some generic methods for finding and updating data. I would appreciate any pointers with the design of my methods and/or my SQL ...
1
vote
2answers
57 views

Pivot script for sales numbers

I've recently been tasked with migrating some code stacks for an applicatin that is moving from using Access to using SQL-Server. I don't usually use PIVOT all ...
4
votes
1answer
63 views

SQL procedure for Archiving and Deleting

I have a requirement for archiving 5 huge tables from PROD to ARCHIVE server without losing the integrity of the tables. The query makes use of the Linked Server functionality and current idea is to ...
1
vote
1answer
48 views

INSERTing a student info record based on form inputs

I want to improve my coding skills. How do I write this code with concatenation ...
3
votes
1answer
32 views

Database trigger to monitor insert/update events and update another table

I am working on a MSSQL Database (for a program called Sage 200). There are many tables in the database, however, I want to be notified of changes (new record inserted or existing record updated) via ...
2
votes
0answers
36 views

T-SQL Secure String Comparison

The previous version of my function was scalar-valued and employed a WHILE loop to do the comparison, this is SLOW. This new version is tabled-valued and uses a typical tally table in place of the ...
2
votes
2answers
41 views

PHP Dynamic QueryBuilder function - Security

This function is a working function, which builds a number of queries depending on the values of $_POST. ...
1
vote
2answers
28 views

How to combine these cte's into one SQL Query

I'm wondering if there's a better way to write this SQL Query. I have a table where I'm storing a row for each transaction that records the type of transaction along with the amount of money being ...
0
votes
0answers
19 views

Stored Procedure enhancement for updating columns in a subset

The purpose of this store procedure is to reach into a large database (test), query for a subset, and update a column value, but with a big constraint - I can only update one row at a time. The ...
1
vote
1answer
18 views

Get top row for muliple registers

I came across a piece of code that generates SELECT queries from a template. The template: ...
3
votes
1answer
54 views

Displaying a 0-7 ratings graph

I store ratings in a table called ratings. The table has these columns: id | user_id | value | ip | showcase_id value is ...
18
votes
3answers
519 views

Stack Exchange User Activity Score

About This is a SEDE query which will calculate your activity and participation on a site. It's geared for Programming Puzzles & Code Golf where questions are values more, but the values can be ...
1
vote
0answers
34 views

Potentially complicated query for advertising campaign

I'm looking for some expert advice with my working script below. What I'm after / how the code works: A merchant creates an advertising campaign with (let's say) a total budget of £100. He then ...
3
votes
1answer
74 views

Closing SQL connection for async operations

The following code executes asynchron on the SQL database. Is the closing of the connection implemented in the right way? Are there any missing features regarding connection handling which need to be ...
4
votes
1answer
555 views

Persistent login (“remember me” cookie) using PHP

The code below is a attempt to create a persistent login cookie. I am an amateur and not professional so this is the first attempt to have secure remember me cookie. Now I want you guys to review two ...