sql

Sql Server query syntax

Hi Sql Server gurus, I need to perform a query like this: SELECT *, (SELECT Table1.Column FROM Table1 INNER JOIN Table2 ON Table1.Table2Id = Table2.Id ) as tmp FROM Table2 WHERE tmp = 1 I know I can take a workaround but I would like to know if this syntax is possible as it is (I think) in Mysql. ...

SELECT DISTINCT not working in .NET application, but works in SQL Mgmt Studio

I have a web app where I am calling a SELECT DISTINCT query on two joined tables. If I try to run the query from the web page, I get the following error: "The text, ntext, or image data type cannot be selected as DISTINCT". When I run it from within SQL Management Studio, the query runs fine - no error. Even more interestingly, there are...

LinqDataSource and DateTime Format

I'm trying to create a LinqDataSource to bind to a DropDownList in an ASP.NET form. I only want to show the elements according to a date (which is one of the fields in the database). Basically, the elements I want to show are the ones that will happen in the futures (i.e. after DateTime.Now). I was trying the following markup : <asp:...

Is there a way to multithread a SqlDataReader?

I have a Sql Query which returns me over half million rows to process... The process doesn't take really long, but I would like to speed it up a little bit with some multiprocessing. Considering the code below, is it possible to multithread something like that easily? using (SqlDataReader reader = command.ExecuteReader()) { while (r...

SQL for parsing multi-line data?

I have the unfortunate task of having to import data from excel into a database on a regular basis. The table looks something like this: IssueID References 1234 DocID1<cr>DocID2<cr>DocID3 1235 DocID1 1236 DocID2 1237 DocID2<cr>DocID3 References is a multi-line text field. What I'm trying to do is cr...

MySQL - show field value only in first instance of each grouped value?

I don't think this is possible, but I would like to be proved otherwise. I have written a simple report viewing class to output the results of various database queries. For the purpose of improving the display, when I have a report with grouped data, I would like to display the field value only on the first row of each unique value - an...

Testing SQL query on Oracle which includes a remote database

Our development databases (Oracle 9i) use a remote database link to a remote shared database. This decision was made years ago when it wasn't practical to put some of the database schemas on a development machine - they were too big. We have certain schemas on the development machines and we make the remote schemas look local by using ...

How can I insert into tables with relations?

I have only done databases without relations, but now I need to do something more serious and correct. Here is my database design: Kunde = Customer Vare = Product Ordre = Order (Read: I want to make an order) VareGruppe = ehm..type? (Read: Car, chair, closet etc.) VareOrdre = Product_Orders Here is my SQL (SQLite) schema: CREATE ...

CONNECT_BY_ISLEAF with Conditions

Hi, Hoping someone can assist me - have a hierarchical set-up going on a table using the whole START WITH and CONNECT BY clauses, which I am using to set-up a vertical-aligned menu system that can expand out to the right, depending if a menu option has children and total number of Levels. Also, as part of the menu option, if a parent ha...

database model structure

Hi all, I have a column groups. Groups has different type stored in group_types (buyers, sellers, referee). Only when the group is of type buyer it has another type (more specialized) like electrical and mechanical. I'm a bit puzzled with how I will store this in a database. Someone can suggest me a database structure? thanks ...

How can a LEFT OUTER JOIN return more records than exist in the left table?

Hi, I have a very basic LEFT OUTER JOIN to return all results from the left table and some additional information from a much bigger table. The left table contains 4935 records yet when I LEFT OUTER JOIN it to an additional table the record count is significatnly larger. As far as I'm aware it is absolute gospel that a LEFT OUTER JOIN ...

Grouping Date Range by Week

I have a date range where a start date is 2 years prior to today. e.g.) '05/29/2007' ~ '05/29/2009'. How can I break above date range so that I would get a list like the following? (start date starts with "05/27/2007", not "05/29/2007" since the start of weekday is Sunday and '05/27/2007' is the first day of week for '05/29/2007' and t...

NHibernate mapping with strings, no real foreign keys.

I've got a class "Lab" which represents a Department of a company that uses my program. It's got a child collection called "Employees" which is meant to contain all the Employees that belong to that Lab. The employee table contains a string field called "Department" which represents which department they belong to, and the Lab table cont...

mysql nested SELECT in UPDATE of same table

Hi, Essentially I need to do something like this.... this is just an example... but the syntax of the first query doesn't work in MySQL update people set prize = '' where prize = 'Gold' and class = (select class from people where id = person_id); update people set prize = 'Gold' where id = <id>; Only one person can have the Gold pri...

keeping sql database schemas synced between developers

We are a small development team (about 5) doing a dev project from different locations. We use SVN as out code repo. The biggest issue that we are having now is that our DB schema is totally out of sync between all of us. I have though of the following options: 1. Work off a "central" DB. This is a bad idea and will most likely not happ...

Is there a better way to convert SQL datetime from hh:mm:ss to hhmmss?

I have to write an SQL view that returns the time part of a datetime column as a string in the format hhmmss (apparently SAP BW doesn't understand hh:mm:ss). This code is the SAP recommended way to do this, but I think there must be a better, more elegant way to accomplish this TIME = case len(convert(varchar(2), datepart(hh, timecolum...

Why does this MySQL function return null?

Description: the query actually run have 4 results returned,as can be see from below, what I did is just concate the items then return, but unexpectedly,it's null. I think the code is self-explanatory: DELIMITER | DROP FUNCTION IF EXISTS get_idiscussion_ask| CREATE FUNCTION get_idiscussion_ask(iask_id INT UNSIGNED) RETURNS TEXT DE...

How to optimize m:n relation query on 3 tables

Hello, this is my sql problem - there are 3 tables: Names Lists ListHasNames Id Name Id Desc ListsId NamesId =-------- ------------ ---------------- 1 Paul 1 Football 1 1 2 Joe 2 Basketball 1 2 3 Jenny 3 Ping Pong 2 ...

vbscript insert into ms access

hi, I am working on a small ap that uses vbscript to write to a ms access db everytime i use it (really for personal use only so i don't need to worry about sql injection). When i run it i keep getting a "syntax error in INSERT INTO statement". The connection string is correct because the db locks when its run. table name is ors. What...

Dataset returning ZERO results

I am trying to fill in a combobox on my winform app from the database. I know there is info in the DB. I know the SP works. It returns the correct ColumnNames. But the DataSet itself is empty? Help!?!? Call from my form--> cboDiagnosisDescription.Properties.DataSource = myDiagnosis.RetrieveDiagnosisCodes(); The RetrieveDiagnosis...