A core SQL statement, SELECT retrieves data from one or more tables or other sources of row set data such as views or table-valued functions.

learn more… | top users | synonyms

2
votes
3answers
1k views

How to speed up a multiple select query

EDIT - Putting my whole stored procedure: I have the below stored procedure: CREATE PROCEDURE [dbo].[data_Update] @Owner NVARCHAR(255), @ID VARCHAR(255), @Division VARCHAR(255), @Manager ...
1
vote
1answer
107 views

How to insert the gathered dates into another table using INSERT SELECT in MySQL?

insert into rfdate (date) select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select ...
0
votes
0answers
82 views

Use join to aggregate data from various normalized tables

I have a database as follows: tbl_clients (Primary Key: client_id, Foreign Keys:place_id,district_id,zone_id,country_id) tbl_places (Primary Key: place_id) tbl_districts (Primary key: district_id) ...
28
votes
1answer
5k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
0
votes
1answer
60 views

select statement for subtype

Okay, so all I'm trying to do here is list the managers who supervised the employee that charged a customer $100 for a rental on November 1st, 1998. My issue is that I don't know how to select the ...
0
votes
1answer
549 views

Update does not working while select returns right results

I am trying to execute the query below: UPDATE( SELECT Ord.Completed FROM Product P INNER JOIN Order1 Ord ON Ord.OrderID = P.OrderID WHERE EXISTS( SELECT * FROM Delivery ...
5
votes
1answer
5k views

SELECT with large WHERE IN clause taking long time

I'm pretty new at all this so be gentle :). I have large medical DB where I need to extract data for patients based on selected doctor(s). Right now I build a patient list by SELECT patID FROM ...
0
votes
1answer
27 views

How to modify the below script to take tables and its sizes for a particular databse to send via mail to a recipient?

SELECT top 10 t.NAME AS TableName, (SUM(a.total_pages) * 8)/1024.0/1024.0 AS TotalSpaceGB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN ...
0
votes
1answer
345 views

How to make a single row result query to return multiple rows

I have the following WITH SQL Query which does select a set of filling stations along a route within the distance of 1000m. To have the measurement in meter I cast the geometries to geography. I also ...
1
vote
2answers
497 views

How to find all schemas where a table has records?

In my Oracle database, I have a number of schemas (users/owners) with similar table structures (about 100 in total). However, some tables are empty in a lot of schemas. Is there a way to find out for ...
2
votes
1answer
459 views

MySQL - Get Minimum Value, Data From Multiple Tables

Good evening all, first post on the DBA forums. I am having real issues working out how to accomplish a seemingly simple task within MySQL as a model for PHP code later on. I am not exactly a MySQL ...
0
votes
1answer
101 views

4 tables, find department with the highest average salary [closed]

ABC is an organization operating in a certain country. Below are the data for the database that manages the information on this organization's employees: employee(employee_name, street, city) ...
0
votes
1answer
21 views

Selecting one variant out of different similar results

I have a table that looks as follows: Book Format Contents A html ... A markdown ... B html ... C markdown ... I want to return only 1 version of a book. ...
1
vote
0answers
56 views

How to use the value from record like as varchar?

I'm trying update a old table with result of selec for another tables but get a error using this sintaxe (e.g.): DO $$ DECLARE _roles refcursor; _onerole varchar; _record RECORD; ...
0
votes
2answers
47 views

Get all the users that are managed by a targeted manager

I have the following model: Table user: (id, name, direct_manager_id) Table customer: (id, name, managed_by_id) i.e. users can have another user as their direct manager, Customers can be managed ...
1
vote
1answer
52 views

Creating a View with results from join of scalar tables

I am really bad at SQL Queries and seem to be stuck at a very odd problem. Maybe there's an alternative of doing this, but I simply can't figure it out. Maybe you guys can help! So, here's my ...
2
votes
1answer
2k views

How to speed up slow query using GROUP BY and CASE?

I'm not very experienced with MySQL and and trying to figure out how to speed up a slow query using GROUP BY and CASE statements. CASE 1 -- GROUP BY and CASE SELECT SQL_NO_CACHE m.id, m.sku, ...
1
vote
0answers
208 views

two column compare ,get count(*) of two column A value same cloumn B value

I have a Mysql table as --------------------- -- page - user -- --------------------- -- page a - user 1 -- -- page a - user 2 -- -- page a - user 3 -- -- page b - user 1 -- -- page b - user 3 -- ...
1
vote
1answer
3k views

Select into existing table where ID is in another table [closed]

I have one table containing ~35k IDs (TableC), one table that features multiple columns (TableA), amongst others the ID from the before mentioned table and a third empty table, featuring the same ...
0
votes
1answer
28 views

Copy every entry from TableA to TableB where the ID is in a textfile containing a list of IDs

I have a text file (or an xls) with a 35k lines, each containing a single ID. I'm required to copy every entry of TableA to TableB where the ID is in said list. Would it be best if I'd create a new ...
-2
votes
1answer
25k views

How to get MAX and MIN in GROUP BY query?

In a simple query SELECT col1, col2, col3 FROM table1 GROUP BY col1 each row includes the first value of col2 and col3 for each unique value of col. How to select MAX and MIN for each value of ...
0
votes
1answer
56 views

How not to display column name during select query

I have a sql file which prints the count(*) of some tables and result of it is something like this. COUNT(*) ---------- 10443 COUNT(*) ---------- 497 COUNT(*) ---------- ...
0
votes
0answers
46 views

SSRS using select from (Select)

I want to report a script which contains a Select from (Select), when I just run the second select I have no problem but when I put the whole script it loads and nothing happens. Here's my code : ...
0
votes
1answer
389 views

Columns count in SELECT query and SQL Server buffer cache

Let's assume that I have a table with 30-50 millions records. It has ~40 columns. Currently when I request data it always requests all columns because of legacy ORM that we use. I know that if we ...
0
votes
1answer
46 views

Move/Copy unique entries only from TableA to TableB, if it's not in the destination table

I'm required to move every unique entry from TableA to TableB, it the unique entry from TableA is not already in TableB. Both tables look the same, they don't have any keys, but the combination of to ...
0
votes
1answer
428 views

Select all results from two independent tables

I have two tables: Comments -------- id comment_content time_stamp Activities ----------- id activity_content time_stamp And I would like to combine the results from both, ordered by their ...
2
votes
2answers
67 views

How can I simplify this UNION?

Here's the case - I have the following query: SELECT DISTINCT post_id FROM ( ( SELECT DISTINCT rp.post_id, pm.meta_value AS premiere_date FROM wp_related_people AS rp JOIN ...
0
votes
1answer
440 views

Mysql Select table records which have not been updated since last month

I have a table names 'scoredata' having fields id, lessonid, levelid, studentid, addedate in which score for each day will be entered by the admin. I want to select only those studentid whose score ...
1
vote
3answers
1k views

Select only rows with minimum values from union table

SQL Server 2005 Here is a sampling of the data from a union of two tables (named "vProducts_All"): DBID UPC Price1 Price2 1 023614025559 689.0000 649.0000 2 ...
1
vote
1answer
470 views

Determine last time a mysql table was accessed (i.e. SELECT'ed)

In my mysql DB schema there are old tables that are no longer used. I want to come up with a systematic way to show my developer a list of tables and last time they were read (SELECT'ed, not ...
0
votes
1answer
566 views

Copy Data To Different Server using SELECT * INTO?

I'm not a DBA by any means. I'm just knowledgeable enough with the MySQL command line to be capable half the time and dangerous the rest. Today I was working on exporting data from a one DB into ...
1
vote
1answer
473 views

MAX (id) function with MySQli

How can I use the following statement to return only the record with the maximum PersonID from the table? /* Create the prepared statement */ if ($stmt = $mysqli->prepare("SELECT Max(PersonID) AS ...
1
vote
1answer
537 views

Select statement for selecting every week's log record

Here is a simplified version of the table in question: +------------------------------------------------------+ | DatabaseName | LogRecordNumber | LogTimestamp | ...
0
votes
0answers
40 views

mysql:Multi tables SELECT Query

I have 2 tables in my database table_names, with the fields: id, name, join_date, country table_sigs, with the fields: id, pet_title, act_date, member_id I would like to know how many members ...
0
votes
1answer
52 views

Work with table named Group [closed]

I have a table named Group in my database , when I try to work on it it gave error : SELECT GroupID,GroupName FROM Group it said : Msg 102, Level 15, State 1, Line 1 Incorrect syntax ...
1
vote
1answer
83 views

Slow Query with LIMIT

We have this query: SELECT "messages".* FROM "messages" WHERE "messages"."account_id" = 1 AND "messages"."status" = 'unread' AND (created_at >= '2014-04-24 01:00:00.000000') ORDER BY id DESC It ...
0
votes
1answer
79 views

Query to select distinct name based on several factors

I have a table with the following data (simplified here): ID | PolicyName | Setting | State | Value 1 Default DiskUsage Enabled 1 2 Default Memory Enabled 1 3 Default CPU Enabled 0 4 Personal ...
0
votes
1answer
341 views

Filtering the results of a SELECT query to avoid certain words in the value of a column?

I have a table called videos with a column named title and there is a set of words (around 50 words as of now but the number of words may increase) which is the knowledge base for filtering the ...
1
vote
1answer
51 views

Unexpected empty result [closed]

select a,b from table1 where a not in (select x as a from table2) AND b not in (select y as b from table3) The above query gives an empty result. Why? And how should I construct this combined ...
0
votes
2answers
46 views

How to use relationships to extract data via select

Disclaimer: Novice in SQL I have 3 tables. There is one "major" table (labeled used_for_lookup below) used to do look-ups on and the other two (labeled fid_table and did_table below) are foreign keys ...
-1
votes
2answers
5k views

How to get missing values in row order?

In a table, many rows have been deleted, how to get the id of missing rows for next INSERT? For example id col1 1 1 3 3 4 4 5 5 8 8 9 9 how can I get the ...
0
votes
1answer
65 views

Using count() and sum() in a calculation in a select

I am trying to do the following in a single select. We have a SAAS that has Company records and User records. A User is a member of a Company (Company.CompanyId = User.CompanyId). In the User record ...
0
votes
0answers
57 views

SQL JOIN excluding duplicate

I'm using PostgreSQL under ubuntu 14.04. I have two tables as below: create table material_base_name ( id bigserial, base_name varchar(50), constraint pk_materials_base_name primary key (id) ); ...
1
vote
1answer
46 views

joins with “could be blank” conditions

Question is most probably stupid but I am stuck on this and cant think anymore(Pissed Off). I have these tables-> employee(has a column foreign to location),location and location_flag. And I need ...
0
votes
1answer
27 views

How can I get all rows of data not included in a TVP

I am trying to do the following: declare @domains DomainNames; declare @CompanyId int declare @CompanyName varchar(255); declare @deletedList DomainNames; set @CompanyId = 239; set @CompanyName = ...
1
vote
0answers
136 views

Select all foreign key relationship chains from any set of potentially gapped table names

I am using this query to find all foreign key relationships: SELECT con.relname AS child_table, att2.attname AS child_column, cl.relname AS parent_table, att.attname AS parent_column ...
1
vote
1answer
796 views

use variable to determine the name of a column in the select in postgre

I'm trying create a update for a new structure of tables from a old table, but not using functions. I'm trying create a script to it. The old table is like this: -- old table (OldTable) name| col_a | ...
-3
votes
1answer
490 views

Which is faster Select Column names or Select *? [closed]

I am writing a Stored Procedure which is joining 3 tables, each table have two Columns. Query 1 : SELECT * FROM [dbo].[PersonInfo] P join [dbo].[Receipt] R ON P.Id = ...
0
votes
1answer
169 views

SQL Server sometimes uses a lot more cpu for query

I have a stored procedure with a quite heavy SELECT statement that includes five joins and uses FieldName LIKE '%searchterm%' on multiple columns. This query usually finishes in about 2-5 seconds. But ...
0
votes
1answer
523 views

Mysql Select vs Select SUM performance

I need to check whether the surfer has alrady voted this can be done using Method 1 Select record_num FROM table where etc etc. i.e. SELECT record_num FROM content_votes_tmp WHERE up = 1 AND ip ...