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
votes
0answers
28 views
Using If statements in stored procedure breaks it [migrated]
This is my stored procedure:
USE [Hires_new]
GO
/****** Object: StoredProcedure [dbo].[ps_selectNewHireWorkPeriodsSQL] Script Date: 05/14/2013 12:59:28 ******/
SET ANSI_NULLS ON
GO
SET ...
0
votes
0answers
20 views
How to merge a select statement with a calculated value as new columns? [migrated]
In my sql server code, I have this select statement
select distinct
a.HireLastName,
a.HireFirstName,
a.HireID,
a.Position_ID,
a.BarNumber,
a.Archived,
...
0
votes
0answers
41 views
How to loop through a select statement? [migrated]
I have this select statement
declare @t table (Percentage float)
DECLARE @acc INT
SET @acc = 1
DECLARE @max INT
select @max = max(HireID) from NewHire
WHILE (@acc <= @max)
BEGIN
IF ...
-1
votes
2answers
57 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 ...
0
votes
1answer
44 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
vote
1answer
30 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
1answer
45 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
37 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 ...
-1
votes
3answers
104 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
91 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 ...
0
votes
1answer
93 views
Error “column does not exist” in a SELECT with JOIN and GROUP BY querry
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
2answers
86 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
29 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 ...
1
vote
1answer
88 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 ...
-4
votes
2answers
138 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 ...
2
votes
2answers
58 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
votes
2answers
102 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 ...
1
vote
1answer
67 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` ...
0
votes
1answer
63 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 ...
3
votes
1answer
38 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
116 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
41 views
Search within same ID accross several rows
My table has the following structure:
+----+---------+------------+-------------+
| id | word_id | meaning_id | sentence_id |
+----+---------+------------+-------------+
| 1 | 1 | 15333 | ...
0
votes
2answers
67 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 ...
0
votes
2answers
87 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 ...
0
votes
2answers
116 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
2answers
221 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 ...
4
votes
1answer
86 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
103 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
278 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
...
2
votes
1answer
71 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
110 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 ...
2
votes
3answers
316 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 ...
3
votes
2answers
113 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
801 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
148 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 ...
1
vote
1answer
179 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 ...
0
votes
1answer
92 views
Retrieving a fixed range of rows from Postgresql
I have a query:
select qid,ansid,ans from table1 where askerid='something'
This query can retrieve many rows. But I want to display only 50 rows at a time and then if the user clicks on more, the ...
2
votes
0answers
209 views
Help with SELECT queries
I need help with some queries. I struggled do find out how to do it, but I think I finally caught on or at least got the results I was looking for in the database.
These are the tables (given by my ...
2
votes
1answer
622 views
MySQL Explain: Using index, using temporary, using filesort. Can this query be improved?
I'm working on a event tracking system which uses a handful of lookup tables as well as the primary logging table. In a report I'm writing, an object can be selected to view statistics against. The ...
1
vote
1answer
407 views
MySQL error in where clause : Unknown column
When I try to execute the query below, I receive the error message:
Unknown column 'have_discount' in 'where clause'
My query is:
SELECT
DISTINCT(MP.item_id), item_title, item_user_id, ...
2
votes
1answer
173 views
SELECT multiple sensor values in one query
Background
I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of ...
2
votes
1answer
77 views
JOIN 2 tables, and query data from them 3 times
Basically I have 2 tables: post_meta and posts.
post_meta has a post_id, key and value:
post_id key value
6343 make Chevy
6343 model Volt
6794 make Chevy
6794 model ...
0
votes
1answer
146 views
Select fields from multiple tables
I want to make a query a little complicated, and I'm not able to do it alone.
I have the following tables:
Table color:
id_uo P
id_cor PF
id_area F
text_cor
Table area:
id_area P
...
2
votes
1answer
123 views
I have to update all the rows of a Column in a table. Here is my query
So My tables are
People:
People_name(VARCHAR 100),
People_id (INT, PRIMARY KEY AUTO INCREMENT)
Tracks:
Track_id(INT PRIMARY KEY),
People_id_references (int ,Foreign key references ...
0
votes
0answers
39 views
mysql select query optimization [duplicate]
Possible Duplicate:
mysql select query optimization
I have two tables, testa and testb.
CREATE TABLE `testa` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT NULL,
...
1
vote
2answers
116 views
Is it always bad to use SELECT * FROM?
It is widely recommended NOT to use SELECT *. I understand this, as it is not a good idea to bring the entire row into memory, when only a few cells are needed.
Is it still bad to use SELECT * when ...
0
votes
0answers
53 views
Informix Benchmark select
I'm not very familiar with databases and need your help. We have following case. Informix DB is running and target table contains 1.5 million records. We want to run application which periodically ...
2
votes
2answers
104 views
HELP with SQL WHERE syntax
I have to pull out a range of entries of a database, all based around two pieces of data. In itself thats fine. An example of the data is:
Record ID Tags
1 1423 Day, Sun, Warm
2 ...
3
votes
2answers
2k views
Select column names whose entries are not null
I would like to have a list of those columns of a table that have at least one not-NULL data entries in them.
In other words, I would like to get the column names for which the following returns at ...
