sql

sql select all for one table only

I have a couple joins that I'm doing. I say tablename.column to identify what I want to select in the database...don't want to select all columns. However, my last join I do want to select all for that. Is there a way I can use an asterisk or something for the last join? The last table is going to be dynamic too so I can't hard code it ...

Union all geometry in a SQL Server table like GeomUnion in Postgres

Just to clarify up-front: I'm talking about unioning geometry, not the SQL keyword UNION. I'm trying to move some spatial data from Postgres with PostGIS to SQL Server 2008. It was fine until I saw a statement like this: SELECT GeomUnion(the_geom) FROM some_table This unions all geometry in that column and return it as one result (si...

Executing a SQL-script dosn't success

Sorry for my English first of all. I have a problem and need help. I have a simple tool made by myself on c#. This tool makes connect to local or remote firebird server (v.2.5). And my tool can create specified .fdb file (database) somewhere on the server. Also i have a file with sql-statements (create table, triggers and so on). I wan...

maintain the user input string in to db

Hi, Probably simple question but it has been a long time since i work with UI, I have a textarea that the user can enter his input. I'm saving the input into sql server table. when i want to display the input all the breaks that the user entered are gone. I remember there was a way to replace char 13 with <br/> (did that with classic A...

SQL Magento Sales Report

I’m trying to add new columns in report/sales/shipping. Columns are now ok but I can’t display values coming from another table. in app/code/local/Mage/Sales/Model/Mysql4/Report/Shipping.php After protected function _aggregateByOrderCreatedAt($from, $to) { try { $tableName = $this->getTable('sales/shipping_aggregated_order...

How do I create a join which says a field is not equal to X?

I have the following database table with information about people, diseases, and drugs: PERSON_T DISEASE_T DRUG_T ========= ========== ======== PERSON_ID DISEASE_ID DRUG_ID GENDER PERSON_ID PERSON_ID NAME DISEASE_ST...

2 SQL Queries from same table

I am looking to grab the first row (ordered by date descending) from a table, and then grab the rest of the rows (ordered by last_name ascending). I'm assuming I need to use a UNION statement for this, but I'm having trouble getting it to work. Thanks in advance ...

Date issue in sql

I have a table that I am entering data into every day. But I don't want that this data to be modified by other users. So I am backing up this data to another table using: INSERT INTO tbl_cancel_backup SELECT tbl_cancel.[cdate] AS 'cdate', tbl_cancel.[machine_no] As 'No', Sum(tbl_cancel.[amount]) AS 'Total' FR...

SQL join on one-to-many relation where none of the many match a given value

Say I have two tables User ----- id first_name last_name User_Prefs ----- user_id pref Sample data in User_Prefs might be user_id | pref 2 | SMS_NOTIFICATION 2 | EMAIL_OPT_OUT 2 | PINK_BACKGROUND_ON_FRIDAYS And some users might have no corresponding rows in User_Prefs. I need to query for the first name and l...

SQL query to get data from a large list of strings

I have a large list of strings (1500 email addresses to be more specific) and I need to look up a piece of data in a very large DB table for each of the strings (e.g. the primary key, mydataitem). How can I do it efficiently? For example, this is way too slow (amongst other problems): $stringArray = ('foo','bar','baz',..., 'for 1000s...

Creating a string from a lambda expression

I have functions that take SQL where clauses, and I'm wondering if there's a way to make them all strongly typed. Is there a way to take a lambda expression like a => a.AgencyID == id and convert it to a string where clause? Like "AgencyID = 'idValue'"? Thanks! ...

How do I control the definition, presentation, validation and storage of HTML form fields from one place?

I want to be able to define everything about a form field in one place, as opposed to having some info in the DB, some in HTML, some in JavaScript, some in ASP... Why do I have to worry about possibly changing things in four separate places (or more) when I want to change something about one field? I.e., I don't want to: declare the ...

How to run .sql file from a batch file?

I am running sql server 2008 express and i need to schedule some stored procedures to run nightly...so i have built out these .sql files which i would want to run from .bat file...i need to know the command to execute these .sql files one by one and store their results i guess...can anyone help me out? ...

With the recent prevelance of NoSQL databases why would I use a SQL database?

After developing software for about 5 years now, I have spent probably atleast 20% and perhaps up to 40% of that time simply making a RDBMS able to save and retrieve complex object graphs. Many times this resulted in less than optimal coding solutions in order to make something easier to do from the database side. This eventually ended a...

Special case of updating a column with not null and unique constraints in PostgreSQL

This is a toy example that illustrates a real problem in PostgreSQL. The below examples are using a PostgreSQL 8.4.3 server, but I suspect other versions have the same problem. Given the following table: => create table tmp_foo (foo boolean not null unique, bar boolean not null unique); => insert into tmp_foo (foo, bar) values (true,...

Sql design question - many tables or not?

15 ECTS credits worth of database design down the bin.. I really can't come up with the best design solution for my problem. Which is this: Basically I'm making a tool that gathers a lot of information concerning the user. At the most the user would fill in 50 fields of data, ranging from simple checkboxes to text input. I'm designing t...

Special characters won't work in MySQL (UTF-8)

So, I've had some issues while trying to come over from Latin1 encoded databases, tables as well as columns, and now that everything is finally in UTF-8, I can't seem to update a row in a column. I am trying to replace an "e" with an e with acute (é). But it gives me this: ERROR 1366 (HY000): Incorrect string value: '\x82m ...' for colu...

Sorting a time value in a dataset

In my current solution, I am converting a DateTime value, "Time" in my database using CONVERT so it displays in a ##:##AM/PM format, and I realize that CONVERT just takes whatever datatype and turns it into a VarChar, or a String once its in C#. This is making my sort work incorrectly in my Gridview. I am sorting the columns in my DataSe...

Excel 2003 send date parameters to SQL query

PROBLEM: I have a few worksheets that use sql queries to retrieve data. I would like all of these sheets to query data based on the same date range entered by a user in excel. eg. I would enter somewhere in a form or in a cell a date range and retrieve the value in sql to obtain data based on the user specified dates. Is there anyway t...

SQL group by -- can merge?

In SQL we could add group by clause -- then do sum, count, or avg of a particular (numeric) column. Is there a way to "merge" / "concatenate" particular column -- for each group? I need this done in one SQL statement ...