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.
1
vote
1answer
30 views
How to select specific rows if a column exists or all rows if a column doesn't
I'm writing a script that gets a count of rows for a few tables, however for some tables I want to only get a count of rows where a flag is set (in this case active=1). Is there a way I can do this ...
2
votes
1answer
24 views
To select from all tables inside the schema in PostgreSQL
Is it possible to select from all tables inside the schema?
I got all the table names from
select table_name from information_schema.tables
but I am unable to use it to do my query.
2
votes
2answers
257 views
SELECTing multiple columns through a subquery
I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them.
SELECT DISTINCT petid, userid,
(SELECT ...
2
votes
3answers
81 views
Is there a way, in a single SQL statement to ensure that all items in a list are present in some column?
What I'm looking for is a way of verifying that all of a list of items (let's say 'FOO', 'BAR' and 'BAZ') are all in a given table.column. This would be something like SELECT something FROM sometable ...
1
vote
1answer
108 views
select count(*) in mysql 5.5 innodb— rewrite advice?
I need advice on how to rewrite a select count(*) query for innodb tables mysql 5.5. in new environment its very slow...
select count(*)
from mails3
join questions using (question_id)
where ...
3
votes
1answer
73 views
Index on foreign key makes query extremely slow
We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem.
The queried table (table3) has an indexed PK, three FK with indexes ...
0
votes
1answer
73 views
How to run a SELECT query within while loop in PHP?
Within a SELECT query via PHP while loop, I need to run a mysql SELECT query as
$result1 = $mysqli->query("SELECT * FROM table1");
while ( $row = $result->fetch_assoc() ) {
if ( $row['X'] == ...
0
votes
1answer
141 views
Error “column does not exist” in a SELECT with JOIN and GROUP BY query
I'm using PostgreSQL 9.1 with a Ruby on Rails application.
I'm trying to list the last version of each "charge" (in my history table : hist_version_charges) belonging to the same project id ...
0
votes
1answer
19 views
Delete from table where multiple fields match select subquery from other table
I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table.
This is what I have so far, though it doesn't work:
...
2
votes
1answer
35 views
How to select nodes where all children is satisfied?
I have a tree structure of light bulbs. I want to turn on all the light bulbs starting from the leafs of the tree. A light bulb cannot be turned on unless all its immediate children are turned on.
...
-1
votes
1answer
77 views
Optimization of a select statement
I'm using MySQL and have a table user_data like this:
user_id int(10) unsigned
reg_date int(10) unsigned
carrier char(1)
The reg_data is the unix timestamp of the ...
1
vote
1answer
52 views
How can I make a select statement get blocked?
Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables.
To prepare the ...
0
votes
1answer
53 views
Order of where clause evaluation
If I have a MySQL table which has content from the last 3 months I actually do the following:
SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt
FROM `mDelivered`
where _campaign = 2
and `session` = ...
-1
votes
2answers
65 views
How to do division from select statements in sql server?
I am trying to create a select statement that can return a table of 1 column.
Basically, I am looping through all the records of NewHire, and then using its id, to select stuff from another table. I ...
1
vote
1answer
49 views
Select records that do not have associations outside a certain list
I have a mySQL table of nodes and each node can be associated with many categories. I have a list of category ids that are acceptable (498, 499). I want to select only nodes who do not have ...
1
vote
1answer
31 views
How to add additional tables/fields into a query with multiple inner joins?
Given this query (many thanks to RolandoMySQLDBA from question Select records that do not have associations outside a certain list), how do I add other fields from other tables into my result?
SELECT ...
1
vote
4answers
7k views
Retrieving row count without using Count function
I wrote a query below which shoots me a syntax error why would it do so,
SELECT MAX('Row') FROM
(SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS)
Error Desc:
Incorrect syntax near ...
-1
votes
3answers
105 views
Add Contents in a Column and make them 0 [closed]
http://sqlfiddle.com/#!3/96f11/3
In the above fiddle, I need the required output in the Fiddle.
ie., The UserID column in Filtered Table and Main table are equal.
I need to get the Amt column data ...
1
vote
2answers
121 views
Getting SELECT to return a constant value even if zero rows match
Consider this select statement:
SELECT *,
1 AS query_id
FROM players
WHERE username='foobar';
It returns the column query_id with value 1 along with a player's other columns.
How would ...
4
votes
5answers
6k 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
2answers
156 views
MySQL: Join one Row in a table with two Rows in other table
In my MYSQL Database COMPANY. I have two tables, like below in my diagram (arrow shows relations):
`users` `user_login`
+--------------+ ...
1
vote
2answers
32 views
Order by on an alphanumerical column
I have a column that has strings of the following type:
Miller 10
Allen 20
King 10
....
Jones 100
I try to sort the column based on the numerical part of the data.
I tried the following:
SELECT ...
-4
votes
2answers
170 views
Get Hierarchial Data in Self-Referencing Table (Parents , Childs) [closed]
In the following Fiddle,
sqlfiddle
I will pass a sno and I need all the parents of that sno in a table. And all the children of that sno in another table
Please refer to this question. Since ...
-1
votes
2answers
120 views
Select query having count and variable
I have a table like the one below.
I need to form a select query. I'm going to pass a RefID like
SELECT SNo FROM Register WHERE RefID = 1
And beside the SNo in the Statement, I need to have ...
2
votes
2answers
66 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 ...
1
vote
1answer
76 views
Insert from one row to another using cases
My original table was(being used since 2005):
CREATE TABLE `request` (
`msg` VARCHAR(150) NOT NULL,
`id` VARCHAR(20) NOT NULL,
`ctg` VARCHAR(10) NOT NULL DEFAULT 'misc',
`date` ...
4
votes
2answers
1k views
SELECT TOP in MySQL
How can I do this in MySQL?
SELECT TOP 50 PERCENT * FROM table
What's the simplest way to do it without running to separate queries (if possible)?
3
votes
1answer
43 views
PostgresSQL: Get single attribute of UDT in SELECT statement
I created a user-defined type in a PostgreSQL 9.2 database and am trying in the SELECT statement to get only an attribute of the UDT. However, I don't seem to get my code to work.
Creation of type ...
2
votes
1answer
146 views
Selecting minimum value using a subquery
I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating.
We have a table for prices ...
2
votes
1answer
48 views
Search within same ID accross several rows
My table has the following structure:
+----+---------+------------+-------------+
| id | word_id | meaning_id | sentence_id |
+----+---------+------------+-------------+
| 1 | 1 | 15333 | ...
5
votes
6answers
6k views
Is it possible to mysqldump a subset of a database required to reproduce a query?
Background
I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).
Question
...
8
votes
2answers
22k views
oracle - list users with access to certain tables
I'm sure this has been asked before but I can't seem to find the relevant details for the following.
Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it ...
0
votes
2answers
74 views
Need a way to query a table, and JOIN it with the TOP 1 related record from an other table
As a follow up to this question, I'm wondering if there is a better and/or more efficient way to gather the data in question.
As stated, this query does return me 95% of the data I need --
SELECT ...
3
votes
2answers
292 views
Should I use select count(*) for counting records or having a field like comments_no for tables like post, comments?
In my recent project which is about social networking for an Asian country, I'm in doubt whether I use the below SQL statement for counting records:
SELECT COUNT(id) shareLikeNo FROM ...
0
votes
2answers
95 views
SELECT only values THAT are different
I have two instances user1 and user2. They have colors
u_id | color<br>
1 | red<br>
1 | green<br>
2 | red<br>
2 | blue<br>
I need a mysql request, that ...
0
votes
0answers
24 views
table(s) sync question [closed]
i have a sync script that queries several different wordpress databases every 15 minutes to keep a main "feed" table up to date. it totals approximately 50,000 queries (99% of them are Selects, for ...
3
votes
2answers
3k views
Can someone explain why select with nolock will query a potion of updated data?
I was reading the answer from here (from stackoverflow, I think should ask in here)
NOLOCK means placing no locks at all.
Your query may returns portions of data as of before UPDATE and
...
0
votes
2answers
129 views
MySQL Reusing calculation in SELECT clause in a VIEW
I have an SQL query that has a simple date difference between NOW() and the creation date (is in the table). I need to reuse this again within the SELECT clause in a VIEW. Is this possible at all?
...
1
vote
1answer
861 views
MySQL Insert into two tables using new IDs
I'm trying to select data already in the database and insert it into two tables using the generated ID from table one.
table1 contains configuration options, table2 contains pricing data. Table2 uses ...
2
votes
3answers
363 views
Count consecutive rows in mysql
I would like to get the count of consecutive rows in MySQL. It is a large database I try to avoiding the joins. Here gadget_id means vehicle and every 20/30 second the vehicle sends the current ...
4
votes
1answer
90 views
Does the order of fields in SELECT query matter when using composite indexing?
I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?
For example, if I ...
3
votes
3answers
108 views
Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates
We have an unusual database structure (I leave the reasoning for another question), that in it's current form requires a regular truncation and recreation of allot of row (about 1 million).
We're ...
1
vote
2answers
351 views
Getting repeated value in a group_concat only if ids are different
I have two tables, first the table Product:
id|category_id
--+-----------
1 | 12345
2 | 12345
3 | 12465
And then a table activity:
id|prod_id|activity_type |description
...
1
vote
2answers
5k views
How to cast an integer to a boolean in a MySQL SELECT clause?
I'm new here so be kind to me. I have the following scenario:
I have many tables which, for the sake of simplicity, are represented in a View in my MySQL database. My problem is that I need a value ...
1
vote
1answer
184 views
How to re-arrange rows to best fit within groups?
I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100.
mixing_order id num_items cat order_in_cat cumulative_sum folder
1 ...
2
votes
1answer
72 views
Select columns from 4 tables
First thing, I am totally new to SQL. I saw some tutorials, videos, tried searching but didn't find what I needed. I know it has asked before, but I couldn't find it.
What I need to find is basically ...
1
vote
1answer
119 views
MySQL SELECT query: i want show duplicated entries
Example
My query is:
SELECT code_CAT FROM categories WHERE id_code IN(14,14,14,14);
output is:
53724
Only one row.
Instead I want this output:
53724
53724
53724
53724
How can I achieve ...
3
votes
2answers
118 views
Inner Join or direct data save in SQL Server
I am developing a social network site and expecting very good response from people so expecting huge users (maybe more than million in years). My website is holding user information table and all its ...
2
votes
1answer
976 views
Multiple “GROUP_CONCAT”s with WHERE clause
In a table as
id name type info
1 BMW car yes
2 Reno car no
3 IBM electronics no
4 Sony electronics yes
5 Mazda ...
0
votes
2answers
193 views
How to mix pairs of rows without JOIN (in a temporary table)?
I have a TEMPORARY table containing pairs of key/value as
id col1 col2 type pair
1 key1 key2 key 1
2 value1 value2 value 1
3 key3 ...
