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
45 views

Optimizing nested “Select Distinct” query

I am updating this question. I am using following query which causing issue. Note following points I am using 4 tables in this query message_share messages myusertable friends What i am trying ...
4
votes
3answers
46 views

Retrieve Father and Child structure in a Database Table

I have a table with this schema: CREATE TABLE [td].[MyTable] ( [ID] [int] NOT NULL ,[FatherID] [int] NULL ) (Note: I have excluded all the columns not ...
0
votes
1answer
49 views

Shortening code with SQL parameters [on hold]

How can I cleanup SQL parameters with short code? Can I write like this too? ...
7
votes
2answers
45 views

Benchmarking of SQL data crunching using a common datum

@nhgrif and I were curious about a couple different ways to aggregate a number of records based on a duplicate/common datum. Two different ideas will be shown. Any advice on all code but especially ...
-2
votes
1answer
43 views

Loading data from SQL server to webpage takes too long

There's a div in my webpage which will load data from an SQL server when the user clicks the button loadresource. Here's the JavaScript for button click: ...
4
votes
1answer
63 views

Unduplicated counts over various dimensions

I'm working with some data on college students, which has a unique key of SSN, Campus, ...
1
vote
0answers
68 views

ZenDesk user management [on hold]

I'm having issues trying to keep this code DRY and modular. I'm open to any suggestions. The code is using the ZenDesk API. I'm trying to insert organizations and users and make the code generic ...
5
votes
3answers
244 views

Can this SQL call for a scalar be made shorter?

When I work with SqlDataAdapter, I usually follow this pattern. Especially, please note the using/fill combination. It's basically a one-liner. The other stuff is just the facility around it. ...
2
votes
2answers
233 views

Get and display employee time clock data

I have been making a web page in PHP and JavaScript to take employees clock times and do some things with them so I can display them. Everything is currently working quite well, but the code looks ...
6
votes
5answers
332 views

Transaction handling for multiple SQL statements

In this code I update two tables called Payment and SalaryTrans. First I insert records (Salary payments) to ...
5
votes
3answers
187 views

Legacy Data and Current System Data Merged together in a single SQL Database

There was a lot going on in this Database that I couldn't change, a lot of bad practices because the data was originally being pulled from an old mainframe database and then transformed by a horrible ...
4
votes
2answers
473 views

Querying school databases

This is the application I created for my personal use. I need some quality testing of this application. If you see some errors or any lengthy coding (it's already lengthy), and it needs to be ...
5
votes
1answer
38 views

SQL query with dynamic unpivot+pivot for cross product

In this Data Explorer query I am trying to do the following: For each tag: Compute sum of answer scores in this tag ("S") Compute count of answers in this tag ("A") For each tag class (Bronze, ...
4
votes
3answers
96 views

SQL design for representing a two-level hierarchy of objects with containment

I am trying to represent questions in quizzes. The database is Mysql but I am using fairly generic sql. The assumption is that varchar cannot handle more than 255 though, so I am supporting longer ...
5
votes
2answers
173 views

General select statement to stored procedures

I'm trying to write a general function to several specific stored procedures, but I'm afraid that it will cause errors that I don't see now. The function is to get the name of the stored procedure as ...
3
votes
2answers
109 views

Efficient way to extract the first row in a Group By group

I have a large Sql Server view with this schema: [archive_ID] (int, not null) [archive_date] (datetime, not null) [archdata_path_ID] (varchar(50), not null) [archdata_value] (int not null) I need ...
5
votes
3answers
72 views

Relational schema and query for multiplayer tabletop game

As an exercise, I was asked to design a database schema (for MS SQL Server) for a tabletop game. The requirements were simple: players compete in matches and there are specific match types (e.g. 1v1 ...
18
votes
6answers
1k views

Average time to first answer

Just trying out my T-SQL foo. Wrote a script to get the average time (in seconds) to first answer for a specified language. ...
5
votes
3answers
238 views

Extracting certain records

The purpose of this SQL query would be to extract the record 10192 only, because the end_time is both in the past and its foreign counterpart in the Node table ...
2
votes
1answer
32 views

Reusing select statement

I am checking if there are any rows in a table with some conditions. If there are more than 1 row, it should filter again with some more conditions. ...
6
votes
1answer
39 views

Trying to avoid a double full scan with aggregates in query

I have a statistics query I'm trying to run against the below table: ...
5
votes
1answer
69 views

Selecting template from database using organization ids

This is my first SQL post over here, and in fact, I have more or less the experience of a regular SQL programmer, except that the task at hand this time was way harder than normal. I am expecting, ...
4
votes
3answers
204 views

SQL percentage calculation

I have created a query in which there is a column that calculates a percentage: ...
4
votes
1answer
77 views

Batch of SQL statements in a single query

I'm using the following query for multiple transactions. This is the scenario. Generate Salary (this is not in the scope of the query) I have employee attendance in ...
6
votes
2answers
262 views

Multiple SQL statements in a single transaction

Using the following query I'm doing transactions with two tables ...
7
votes
2answers
41 views

Revision 2 - Step 1: PsychoProductions management tool project

I have been told by DBA.SE people that this was off-topic for DBA.SE and should be on CR.SE instead. So... here I submit this database schema design for my trusted fellow CRitters to review! This is ...
4
votes
3answers
83 views

Web application to insert into MySQL database

I'm currently learning PHP, SQL/MySQL and HTML to develop a web application project (I have very little practical experience in all of these.) To get started, I spent far too long and many questions ...
4
votes
1answer
36 views

Unifying XML formatting with inheritable parsing

Inspired by the XML format for game data question, I was interested to see how my system works for storing game data. My system works a lot like Halo, where there are a variety of gametypes and ...
22
votes
2answers
2k views

Tired of FizzBuzz yet?

There have been many FizzBuzz questions lately, and here is one more! Granted, it is pretty straightforward to do FizzBuzz with SQL using calculations. So instead, I'm going to do a FizzBuzz which ...
10
votes
1answer
79 views

Abusing Excel VBA… to maintain data stored in a database

I need to build a little UI to allow easy maintenance of some data stored in a MySQL database, and the end user wants to do that in Excel (I know, I know). The code I came up with comes pretty close ...
11
votes
2answers
536 views

PHP URL Shortener

I just developed a URL shortener. index.php: ...
5
votes
1answer
45 views

Library database search with multiple JOINs is too slow

I have a MySQL query which runs very slowly. I've rewritten it many times but no improvements yet. My current query for advanced search takes about 60 secs to complete. Any suggestions for improving ...
17
votes
1answer
173 views

Executing large SQL script file with GO statements using ADO

I wrote a small component (still in the works but working) which takes a large SQL script file, splits it into different "blocks" based on GO statements, and executes them one by one. The only major ...
4
votes
1answer
42 views

Speeding up class that uses an ODBC connection

I have created a class that gets data from an ODBC connection. The code works but it is really slow - I'm talking up to 1.20ish minutes to run. I know my code is inefficient but I'm really not sure ...
7
votes
3answers
156 views

Getting the top 10 stores within a category

I have the following query. I know there's an easier way to do this without the sub queries, or at least optimizing how they're used. I've read about cross joins and using ...
6
votes
3answers
73 views

SQL Stored Procedure Get Distinct and Update

The idea is to show how many times a user (by EmployeeID) is in the TblTableList and then update the ...
8
votes
2answers
67 views

FizzBuzz in SQLite 2

This query performs a FizzBuzz in SQLite 2.8.17 without creating any tables or sprocs. First attempt: ...
6
votes
2answers
94 views

Put it in a bucket

You're running a jeans company. Your system is collecting detailed orders' data, but it's a bit archaic and it's storing the number of units ordered per size in a delimited string with 20 "fields", ...
12
votes
2answers
312 views

FizzBuzz in SQL--no loops

MS SQL Server, no loops. ;) ...
5
votes
1answer
43 views

Using SQL to reorder items in a list view - is this efficient?

I'm writing an application for work that requires me to process a list of transactions. The list is small (5 records at the most) and I'm required to display that data in an android ...
10
votes
1answer
69 views

Loading Data Warehouse with Dynamic SQL

For a data warehousing project I ran into the following: Custom fields that users can create, modify and delete, that should be loaded into the data warehouse as they are when the ETL happens. On ...
2
votes
2answers
39 views

Using Common Table Expression and Outer queries

In following query I'm use both Common Table Expression and Outer queries. Apparently both looks same to me. If I summarize my requirement, I have employees in ...
5
votes
2answers
69 views

Inserting tables with foreign key

How can I improve this method that adds data to three tables in the database? The tables are: UserTable UserInfoTable ...
11
votes
2answers
78 views

Revision 1 - Step 1: PsychoProductions management tool project

I started a project to build my own invoicing and management system to take the place of prohibitively expensive QuickBooks software. This will be broken down in 5 steps, with the current step in bold ...
6
votes
5answers
501 views

Username, Password and UserType Validation

I have two tables in the database: Credentials (userid, password, usertype) Customer ...
13
votes
2answers
125 views

Nokogiri crawler

The following code works but is a mess. But being totally new to Ruby I have had big problems trying to refactor it into something resembling clean OOP code. Could you help with this and explain what ...
6
votes
1answer
65 views

Is there a way to optimize this aggregate postgreSQL statement by using CASE?

I have a working SQL statement which shows the following: Player Champion Role Total Kill/Death Ratio Total Kill+Assist/Death Ratio Total Win % Difference between Ratio from one month ago Difference ...
4
votes
4answers
499 views

Two while for the same query

If I have to loop results of a query echoing first all fields of a column, then echoing something not to loop, then fields of another column. ...
2
votes
1answer
17 views

Query the Max Nth per group

I'm trying to write the most efficient query I can to pull the relevant data I need for retrieving the latest conversations for a user, ordering groupings of conversations by most recent message in ...
4
votes
3answers
63 views

Building SQL from multiple combinations of query parameters

I have a node.js/express.js based REST application. In one GET service I am querying data based on different set of request parameters. I am looking for a better way to implement it. ...