Tagged Questions

-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
40 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
52 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
votes
3answers
98 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 ...
0
votes
2answers
81 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
28 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
132 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
56 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
101 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
1answer
113 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
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 ...
4
votes
1answer
84 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
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
762 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
145 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
177 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
0answers
207 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 ...
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, ...
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
114 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 ...
2
votes
2answers
103 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 ...
2
votes
5answers
219 views

How define select, if bridge pair may have 3 players?

Normaly a pair has 2 players, but if there is an odd number of players at the club, one 'pair' has 3 players. Table, members: pair, player_id 1 1 1 2 2 3 2 4 3 5 3 6 4 7 4 8 5 9 5 10 6 11 6 12 6 ...
1
vote
2answers
400 views

GROUP BY two columns

I want to count two columns, but need to get the results of first column in one row. SELECT country, COUNT(*) FROM table1 GROUP BY country, type This query gives me country type COUNT(*) ...
2
votes
1answer
540 views

Oracle error “too many values”, must be solved with nested SELECTs

I have hit upon a "too many values" error in Oracle, when working with some intricate nested SELECTs. A simplified description of my problem, on a toy problem describing the hierarchy in a company. ...
3
votes
2answers
463 views

Select query according to date

We have a table to store the Events. Table has got a schema like EventID INT Primary Key EventName NVARCHAR(100) StartDate Datetime EndDate Datetime So in the front end we want to show the events ...
1
vote
1answer
261 views

Merge 2 tables and fetch data one by one

I have two tables: product & offers. products has (prod_id, category_id, prod_name, brand, price, status) offers has (offer_id, prod_id, offer_type, discount_amt, validity) I want to display ...
4
votes
2answers
2k views

How do I use the IF…ELSE condition in a WHERE clause?

I'm attempting to use the IF...ELSE construct in my WHERE clause to selectively apply conditions to my SELECT. Should this work? CREATE OR REPLACE package body If_Else_Pack IS PROCEDURE Moving ...
5
votes
1answer
268 views

Mysql sub query returns more than 1 row

SELECT * FROM wp_posts WHERE ID IN ( (SELECT courses FROM wp_category WHERE CatID =401) OR (SELECT meta_value FROM wp_postmeta WHERE post_id IN (SELECT courses FROM wp_category WHERE CatID =401) ...
3
votes
2answers
258 views

WHERE clause is not filtering on LESS THAN and GREATER THAN or BETWEEN

I am trying to select MTR values that are between the values of 0 and 4. What am I doing wrong? SELECT DATEDIFF(M, TheDate, TheOtherDate) AS MTR FROM MyTable WHERE MyID = 2074163 AND MTR >= 0 ...
3
votes
2answers
997 views

How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values?

I'm running MySQL 5.0.88 (Coldfusion8) I have a product search which I'm querying number-of-results as well as min/max prices/rebates across the product table. I also want to include a string of ...
2
votes
2answers
538 views

Sub-query executes 1 time for a parent query or more?

If i apply a static data in where clause is it faster then applying a sub-query. Example: (The query returns 5 records as a result set) SELECT STARTDATE, ENDDATE FROM TEST WHERE STARTDATE = ...
7
votes
4answers
895 views

Benefits of using backtick (`) in MySQL queries?

In MySQL we can create queries with or without the backtick (`) symbol. Example: SELECT * FROM TEST; SELECT * FROM `TEST`; Both works fine in mysql-console. Is there any technical difference ...
1
vote
0answers
85 views

Get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id

I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in ...
0
votes
1answer
43 views

Why this query returns rows that I don't want to be there

I'm trying to running this query: SELECT * FROM (`default_users`) LEFT JOIN `default_profiles` ON `default_profiles`.`user_id` = `default_users`.`id` WHERE `default_users`.`group_id` ...
3
votes
5answers
227 views

Single query to return counts over different IDs in a single record

select count(title_id)as algodata from titles where pub_id =1389 select count(title_id)as binnet from titles where pub_id =0877 select count(title_id)as newmoon from titles where pub_id =0736 ...
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)?
0
votes
2answers
77 views

SQL and Multiple Indexes

Consider the following table: i | a | b | c -------------- 0 | 1 | 2 | 3 1 | 4 | 5 | 6 2 | 7 | 8 | 9 3 | x | y | z The table, in the end, will have several thousand rows, and even more columns then ...
1
vote
1answer
306 views

How to avoid duplicating information in a series of nested SELECT statements?

I'm working on an SQL query that puts together customer history based on past purchases (the industry is venue ticketing). The report will be LARGE, something like 80-90 columns, with one column per ...
0
votes
3answers
344 views

Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?

I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote ...
3
votes
2answers
273 views

Select total number of records from common table expression

We are using common table expressions to temporarily save the search results. WITH Results ( ------ ) AS ( select --- ) SELECT * FROM ...
0
votes
1answer
996 views

How can I select the latest record having one state where no later records exist with any other state?

I want to select the last record (by datetime) having a given state where no later records exist with any other state for each given "widget". widget state timestamp ------ ----- ...
4
votes
2answers
3k 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?
-3
votes
1answer
288 views

Best practices for finding and updating a handful of records?

Seems like a simple question, but wondering what is the "best" way to do this if you were "dotting your i's and crossing your t's".
7
votes
3answers
2k 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
425 views

T-SQL, show a set of rows (specific column) as a field, for a better desc, please see the table relationship

I have been having a problem for quite a while now, that I can show with a sample of 5 tables (I'm actually dealing with more than 5!): -------------------------------- shipments ...
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 ...
5
votes
4answers
490 views

Multiple operations using WITH

Is there a way to execute multiple operations using the WITH statement? Something like WITH T AS ( SELECT * FROM Tbl ) BEGIN OPEN P_OUTCURSOR FOR SELECT * FROM T; SELECT COUNT(*) INTO ...

1 2