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

Unexpected SELECT permissions

My user WebApp seems to have SELECT permissions on every table in every schema and I can't figure out why. WebApp is only in the Public role. Public doesn't own any schema. Explicit permissions ...
4
votes
1answer
59 views

How to only update if nothing has changed? I need to validate several things before I insert

Say I have two tables: runners and waypoint_checkins. I want to add waypoint checkin entries but I can never add a waypoint if the runner has finished the race, (see below for schema). Finally. A ...
2
votes
0answers
27 views

How to order by fields of joined table while selecting for update?

For selecting some rows of table Fwt_Chipkarte for update I use the following SQL statement (IBM DB2): SELECT o.Archiviert, o.Id, o.IdComm, o.Kartennummer, o.Konto, o.LetzteAenderung, ...
1
vote
2answers
24 views

Select data where column value exists in seperate rows for same reference id

This is on a MySQL database. Locations have services. What I want is to get all results where service_id is 2 AND 3, not OR so using IN is out I believe. Database ---------------------------- | ...
0
votes
1answer
31 views

Group-by in an Oracle database

on an Oracle database. If I make an: select id,id_operator,denumire,cod_fiscal,loc_den,str from rap where is_punct_lucru=1 and id_operator in ( select id_operator from rap where ...
0
votes
1answer
218 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 ...
0
votes
2answers
41 views

MYSQL Select Distinct with SUM

I am developing a Stocks management application, the Portfolio register table has 4 columns, other than date and registry_key, and it looks like this: ---------------------------------------- | ...
1
vote
1answer
7k views

Select records in one table but not in another, when the second table is not matching on primary key

I have two tables from which I need only the results that appear in one table (list) which do not appear in the second table (cardinal). The list table's primary key is sku, and the table has a ...
1
vote
1answer
40 views

Multiple left join where not in another table

I have the following tables (there is more detailed data in each one, I just put the minimum to create a sample): __________ _____________ _____________ _____________ | person | | provider | | ...
0
votes
1answer
178 views

Speeding up a simple select query on Mariadb

I have a 100k rows table with the following structure: +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
1answer
183 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
0answers
32 views

Select sub columns from a view

I have a large view in Sql Server named ViewA which looks like Select ColumnA, ColumnB, ... ColumnZ FROM TableA Join TableB ON ... Join TableZ ON This view gives me exactly what I need in ...
0
votes
1answer
40 views

How to select users that doesn't in the role

I have 4 roles which are Admin, Procurement, Accounting, HR roles and 3 tables which are Users, Roles and Userroles. They user can have many roles as well as the roles can have many users. the problem ...
0
votes
1answer
158 views

How to get result count from mysql inner join

I have a mysql query something like this. SELECT SQL_CALC_FOUND_ROWS wp_posts . * , wp_geodir_gd_place_detail . * FROM wp_posts INNER JOIN wp_geodir_gd_place_detail ON ( ...
1
vote
0answers
42 views

Select last row for a certain group

I have two tables: Table users columns | type | notes --------|--------|------- id | bigint | PK ... | ... | ... Table events columns | type | notes ...
0
votes
1answer
30 views

SELECT w/ INNER JOIN Query on Optional Columns

I have five tables: File2, Branches_of_Study, Schools, Education_Establishments, and Certificates. File2 is the transaction table with all the other tables as reference tables. File2 is all about the ...
0
votes
1answer
115 views

What is wrong with this SELECT query? No errors, no results

Haunted by this MySQL query that refuses to return results: select entity_id from customer_entity where entity_id NOT IN (SELECT sfo.customer_id FROM sales_flat_order sfo); If you divide the two ...
0
votes
2answers
795 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 ...
2
votes
1answer
29 views

Insert into a DB that was selected via other DB?

I have a table that stores what database should I use for a given device( specified by its IMEI), like this: imeiDB( IMEI VARCHAR(15), db VARCHAR(100)); Let's say I have the database ...
0
votes
2answers
38 views

Which query performs better when searching a row where a column has maximum?

Consider a simple table for example purposes: person(name varchar, age int) I have a task to retrieve the oldest person. Which query is better in general for such problems? Query 1: criteria with ...
1
vote
4answers
75 views

MSSQL Select all parent rows for which exists a combination of children rows with desired value

I would like to select all parent rows for which exists a combination of children rows with desired value. The easiest approach is to use multiple exists clauses, but this is not optimal - anyone ...
8
votes
3answers
20k views

How to do a case sensitive search in WHERE clause?

I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings. Any idea on how to do a case sensitive search in SQL query?
14
votes
5answers
25k views

How can I use a default value in a Select query in PostgreSQL?

I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this? E.g. ...
0
votes
1answer
192 views

Processlist show most of the queries are sending data for select query

I have faced some problem like sending data state in processlist. In my application I used parallel processing which send 50 count at a time, and that will do some MySQL database operation (select and ...
0
votes
1answer
61 views

Grouping based on the status, then count its product and the total based on its category ID

I have a table with product ID, category ID and status of the product. +---------+-------------+-------------+ | id | cat_id | status | +---------+-------------+-------------+ | 1 ...
3
votes
1answer
72 views

How is encoding done in SQL database tables for pattern matching?

Are strings in table columns represented as bit patterns or Unicode? Does the database engine bring them into memory in a different representation character set from the disk-persisted ...
0
votes
2answers
64 views

DB2 - REPLACE INTO SELECT from table

Is there a way in db2 where I can replace the entire table with just selected rows from the same table ? Something like REPLACE into tableName select * from tableName where col1='a'; (I can export ...
0
votes
2answers
52 views

MySQL: What exact commands does each privilege level allow?

I am configuring some new logins for users who have already been using the relevant MySQL servers under other logins. I would like to limit their privileges to the minimum each user needs, based on ...
0
votes
1answer
8k 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
31 views

Postgres: Disable “similarity” column output on “SELECT SIMILARITY”?

I've tried disabling similarity column output on SELECT SIMILARITY but still cannot. Below query will generate a column similarity: SELECT SIMILARITY(title, 'Nation'), title FROM gallery WHERE title ...
1
vote
1answer
43 views

MySQL select with join table

I have 3 tables, one of them is join table: user table: CREATE TABLE `user` ( `id`, `name`) media table: CREATE TABLE `media` ( `id`, `value`, `type` enum('M', 'T')) user_media table: CREATE ...
0
votes
1answer
46 views

Select range of non-identical date from different table

On MySQL5 with innoDB, I have sensor data in multiple tables, all with an ID and a DATETIME field, like so (two tables shown, but there are more). The IDs and DATETIMEs are not identical between ...
8
votes
4answers
12k views

What does the term “projection” refer to in an SQL query (or view definition)

In Oracle's document The Query Optimizer, under View Merging, I found the following information The view merging optimization applies to views that contain only selections, projections, and ...
1
vote
1answer
41 views

Can I parse a text value from a specific column in a query (MySQL)?

I was playing around with Sakila and I ran across the actor_info view and one of its columns is film_info which is a detail of all the movies one actor has worked in. For example a query would be: ...
0
votes
1answer
140 views

Run and Save multiple SELECT statements

Within a Postgres function I would like to run a number of SELECT commands. For each SELECT command I would like to output written to separate text files. What is the best way to implement this? ...
0
votes
2answers
62 views

SQL Server user cannot select from a table it just created?

I'm having trouble understanding permissions in SQL Server (in this case SQL Server 2012, but not sure how version-dependent this issue is). I can create a database, create a login/user on master, ...
1
vote
2answers
191 views

SELECT and UPDATE in a single query while updating selective fields of duplicate records only

I have a MySQL table named Contacts_table with, say, the following contents: ID NUMBER NAME CITY 001 2222222222 John Doe Los Angeles 002 ...
1
vote
1answer
266 views

Insert into table select * from table vs bulk insert

I just wanted to know that SQL Statement INSERT INTO TABLE1 SELECT * FROM TABLE2, will work same like bulk insert ? If no, Is there any way exclude index while inserting records. Process is inserting ...
0
votes
1answer
55 views

How do I perform a query on a dynamic list of tables?

I have a database named as ( conntrack ) with unknown amount of tables.I have a static table "tabidx" which has one column "date". The following table is static and its name is tabidx which provide ...
0
votes
3answers
138 views

DISTINCT Role in SELECT

EDIT: @Moderators: Please delete this question. I still feel that it is unclear what I am asking. Maybe I could have used some Venn diagrams in order to describe the problem. The COUNTs do not seem to ...
0
votes
1answer
53 views

How do I select the value with SERIAL data type in postgres [closed]

I want to know how to select values from a column that has SERIAL data type. I already try to do that but the output is like this 'Resource id #3', and not just '3' I use SERIAL so I dont have to ...
1
vote
1answer
78 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), ...
2
votes
2answers
39 views

MySQL - SELECT on MyISAM Table on index - Does table size (rowcount) matter?

I was arguing with a coworker if SELECT * WHERE t BETWEEN ? AND ? on a MyIsam table where t is an index is significantly slower for huge tables. The hypothetically returned dataset is always the ...
1
vote
1answer
26 views

select group by with multiple AND conditions on the same columns

I've an sql table like this: SID|SERVICE_SID|ATTRIBUTE_SID|VALUE 1 2 5 "VAL1" 2 2 4 "VAL8" 3 2 3 "VAL4" 4 11 5 ...
0
votes
1answer
13 views

Numeric cell with 0 not null

I have the following query (in a Oracle Database): SELECT SUM (CANTITATE) FROM ANEXA2A_TABEL2_ROWS WHERE TIP_ACTIUNE = 'COLECTATE' AND TIP_AMB = ...
1
vote
1answer
49 views

how to cache a subset for cascading select queries in mysql

Here is another database problem I stumbled upon. I have a date-range partitioned MyIsam look-up table with 200M records and ~150 columns. On this Table I need to perform cascading SELECT-Statements ...
0
votes
1answer
32 views

Grant Select on some columns for specific user

I have a windows group with 20 people and all 20 people in the group were revoked access for some columns in the table. Now I need to add the select permissions for 2 of the users in this windows ...
0
votes
2answers
36 views

Why does “DELETE WHERE (VARCHAR)=0” execute successfully?

DELETE FROM stats WHERE name=0 "name" is type VARCHAR(64). The above resulted in all records in the table being deleted, instead of just the 10~ that should have been affected. Only that many were ...
0
votes
1answer
83 views

The cost of left out join versus a select [closed]

I'm currently working with Ebean for database abstraction. My test database is Postgresql 9.3 I turned on logging for the SQL queries to test some code behavior. So, assume 2 tables, OneToMany ...
0
votes
1answer
133 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 ...