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

0
votes
1answer
26 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
21 views

modify a select stmt to give you the definition of a column value [on hold]

I have Select [casenumber], [centercode], [Outcome] the columns centercode and Outcome are numeric contents and their respective Verbatium descriptions are in a child - TblDesc - tables how can I get ...
0
votes
1answer
24 views

How do I select all child table records based on condition [on hold]

I have two tables - demographics and PatSubs. Both tables are linked by CaseNumber filed. PatSubs may have one or many substance entry for the CaseNumber. If I do the following: USE [njpies2013] GO ...
0
votes
1answer
115 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 ...
0
votes
1answer
20 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
25 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
30 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
2k 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
53 views

How can I optimize this Cross Table COUNT() mysql query?

I am trying to cut down the retrieval speed of this MySQL query. All instances of COUNT() point to the PRIMARY key of each field. Currently this query makes my MySQL hang. It seems ridiculous as I'm ...
0
votes
2answers
39 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
34 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
26 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
39 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
23 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 = ...
0
votes
1answer
88 views

SQL SELECT - adding field to SELECT with JOIN is changing the results

I have a SQL sentence with JOIN, running on mysql 5.5.16: SELECT DISTINCT (ot.orders_id), ot.value FROM orders_status_history osh INNER JOIN orders_total ot ON ( osh.orders_id = ot.orders_id ) WHERE ...
2
votes
0answers
32 views

Mysql search matching multiple values in multiple columns

I need to speed up a search. Currently we let our users input a name: John Doe Then in Java we split that string to create multiple values (or "terms") ["John", "Doe"] and loop through the terms ...
0
votes
1answer
62 views

Gets records by max date and hour

I have this query: "SELECT * FROM `" . $table . "` WHERE STR_TO_DATE(data, '%d/%m/%Y')=(SELECT max(STR_TO_DATE(data, '%d/%m/%Y')) FROM " . $table . ") AND hour=(SELECT MAX(hour)-".$delta_hour." ...
1
vote
0answers
22 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
23 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 | ...
0
votes
1answer
66 views

Select Multiple Values from Same Column; one sql statment

I am looking to return a list of users from a database. There are hundreds of users in the db. Each user has it's own unique user_id. All of the values that I am trying to return reside in the same ...
0
votes
1answer
181 views

Restrict access to rows by usename

In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so ...
-3
votes
1answer
61 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
343 views

Getting the top count of records from a table for each month and user

I have a table where translations are stored along with the user_id and when the translation was added. I want to create a query that shows the 'Translator of the month' (the person who did the most ...
0
votes
1answer
38 views

Select * from (every table in an subselect)

I was trying to get an select * from (for column table result) every table_name with the result bellow, how can i achieve this? (SELECT a.table_name FROM all_cons_columns a JOIN all_constraints c ...
0
votes
0answers
36 views

Selecting most recent date when dates are stored in another table

Working in a MySQL database, I have tables set up like this: Table song_instances has song_id (int), meeting_instance_id (int) The meeting_instances table has columns meeting_instance_id (int), ...
0
votes
1answer
70 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
51 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 ...
0
votes
1answer
91 views

MySQL select with left join query

I have following code. select class.name as classname, class.hours as hours, teachers.first_name as teacherFirstName, teachers.last_name as teacherLastName from teachers,class left join ...
-1
votes
1answer
56 views

Select latest record for each ID

I have three tables that contains data as below: Users Id Name Other_Columns --------------------------- 1 John Blah 2 Ricky Blah 3 Stella Blah 4 Bob Blah Saldo Id ...
0
votes
1answer
36 views

Run and Save multiple SELECT statements

Within a Postgres function I would like to run a number of PSQL SELECT commands. For each SELECT command I would like to output written to separate text files. What is the best way to implement ...
1
vote
1answer
56 views

Group by age range and gender with aggregation function on some columns

In Mysql, I have a patient table with id, birth_year and gender. Next table is transactions in which I have id, patient_id [FK from patient] and checkup_date. I need to select rows such that I get ...
0
votes
1answer
31 views

How to union tables into a new table in mysql? [closed]

I have a table as following: id col1 col2 col3 type 1 12 12 23 1 2 23 33 1 0 3 333 2 21 1 ..... I try to select 1% sample of where type=1 and all the data where type=0, I ...
1
vote
2answers
138 views

How to count the rows from 1 table but from two different column

SELECT distinct Major, COUNT(Major) AS MajorCount FROM [College Management System].[dbo].[ApplicantCourseDetails] group by Major SELECT distinct MinorI, COUNT(MinorI) AS MinorCount FROM [College ...
2
votes
2answers
66 views

show table name + number of records for each table in a mysql innodb database

How to list all tables in the current database, together the number of rows of the table. In other words, can you think of a query to come up with something like this in mysql? ...
2
votes
1answer
169 views

Difference data between adjacent rows

I have a table (called Visits) with ReqTime DATETIME NOT NULL column AND Duration INT UNSIGNED column. Duration should be set to the difference of ReqTime for two adjacent rows. But Duration is not ...
1
vote
1answer
38 views

Should I drop this column or is there a query that can achieve a results table without duplicate columns?

I use a column named active in several of my database tables to adjust whether a particular entry/row/user/etc should be included in future query result tables instead of deleting the information. ...
2
votes
3answers
234 views

Using expressions in the SELECT list to thin down a CASE statement

I have a query I'm working through rewriting - it has a huge bunch of case statements in the select list that all basically look like this: select Column1 = case when (Something = 2) and ...
0
votes
1answer
46 views

What's the difference between INSERT … SELECT and SELECT INTO

Let's say I have a table item and an other one versionned_item. From time to time, I want to copy data from item to versionned_item. What I did is WITH subset AS (SELECT ...) INSERT INTO ...
2
votes
2answers
6k views

How to JOIN two table to get missing rows in the second table

In a simple voting system as CREATE TABLE elections ( election_id int(11) NOT NULL AUTO_INCREMENT, title varchar(255), CREATE TABLE votes ( election_id int(11), user_id int(11), FOREIGN KEYs for ...
0
votes
2answers
42 views

Selecting rows with condition on two columns together

i have table with columns Number and VAT How can i select all rows without row which is Number=123 and VAT=15 ? I tried something like this SELECT * FROM tablex WHERE <.. zilions of ...
1
vote
4answers
5k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
0
votes
1answer
22 views

PostgreSQL SQL syntax - JOIN and substrings

I have a single table of hostnames and NFS exports, some but not all of which are related. Example entries: HOST VOLUME ----- ---------- host1 vol1 host1 vol2 host1 ...
3
votes
5answers
106 views

Find all values not present for other values of other column

Let's suppose I have a table with integer columns a and b (each pair (a,b) is unique) and following data: a | b ------- 1 | 2 1 | 3 1 | 4 1 | 5 2 | 1 2 | 3 2 | 4 3 | 1 3 | 3 Now I need to ...
0
votes
1answer
30 views

how to optimize grouped select from an already grouped select query

I already had a specific query to get a number of shops, but I couldn't sum the cost field, I kept on getting an error on the sum function, but it worked when I did this; select tab.shop_id, ...
0
votes
1answer
29 views

Is there any performance difference between a group by statement and nested select statements in sql?

I am curious to know about the differences between using group by statement and nested selects to achieve the same result. for example consider I have two tables such as the following: tblPurchase ...
0
votes
1answer
32 views

How to sum the buy order and sell order in one row per each ticker

I have a table containing some trading information of several tickers and I would like to divide the data into sum(Amount) for buy and sell separately, grouping by each ticker. I have one table ...
1
vote
1answer
21 views

Mysql: Problem using GREATEST with IFNULL and join

I have a query selecting the greatest value of 2 timestamp. To Achieve it I'm calling: SELECT GREATEST( IFNULL( tableb.create_time, 0 ) , IFNULL( tablea.create_time, 0 ) ) I'm using IFNULL because ...
0
votes
0answers
25 views

Text comparison select query needed that finds most whole word matches within larger string

I have a database of products, many with similar names. For example... iPhone 5s (GSM model) A1533 iPhone 5s (GSM model China) A1518 iPhone 5s (GSM model China) A1530 iPhone 5c (GSM model) ...
0
votes
2answers
101 views

SQL insert into … values (…, select(…))

I am trying to insert 25 users, user1...25 into Members with the same GR_ID = 2 with command: insert into MEMBERS (GR_id, username) values (2, (select username from USERS where username like ...
0
votes
1answer
44 views

Simple SELECT query doesn't return FALSE if no results

This is probably a simple fix, but I can't figure it out. This is the MySQL query I have: SELECT (`clockcards`.`date` IS NOT NULL) AS `clockedin` FROM `users` JOIN `clockcards` ON ...