SQL: Insert DateCreated value?
In my table I have a dateCreated column in the format of DATETIME. How do I insert the current datetime when I add the record to the database? ...
sql
Replace and Group By in SQL
HI I want to group my results by a field that has had characters replaced but also keep the original value. This is what I have but I need to return the original URL value without changing the results. select Count(ID) as TotalClicks, Replace(URL, '/', '') as DistinctURL from email_click_throughs where emailid = @ID group by Repla...
What should I store in my blob field?
I try to use blobs as little as possible. When possible I replace blobs by a link to a file. I can only think of a few times that I needed a blob. I used to put zipped pickled python objects in there, but I use mongo or couchdb these days for that. One thing I still use it for is to store wkb (gis) objects. This made me wonder, what do o...
SQL Server Query Help
My problem is I am comparing two records from different tables and deleting those that match. I have found this bit of code : DELETE emails FROM emails INNER JOIN CustRecords ON CustRecords.Email = emails.email Which works well, but i would also like to have a return value of how many records have been deleted. Now if I do an ...
Visual Studio 2010 database edition schema compare where target is dbproj
I'm using visual studio 2010 database edition and running a schema compare against a SQL database instance to sync up new objects on the database which arent yet in my project (dbproj). My solution contains many projects some of which reference each other via database references. When I write updated from my schema compare to my target p...
How does SQL Server treat indexes on a table behind a view?
So I'm trying to understand how SQL Server makes use of indexes on tables behind views. Here's the scenario: Table A has a composite clustered index on fields 1 & 2 and a nonclustered index on fields 3 & 4. View A is written against Table A to filter out additional fields, but fields 1-4 are part of the view. So we write a query th...
Form a query to find duplicate zipcodes
Hello, I have a zipcode table. I want to look through this table and find every zipcode that is in it twice. The table does have a primary key set on it(zipcode_rid). How would I do this? Also, I am aware that there are official multi-state/city/county zipcodes. That is what I'm wishing to find. My table structure is like so: zipcod...
I need a unique subset of data from my table using SQL
I am working with a Dbase table via SQL commands. I have the following free table to work with. import1 account, phone, subaccount, locationid I need the first occurrence of each uinique combination of account and phone. No one field in the table is unique. I can get a partial subset of data using the distinct qualifier but i need the...
Query using JOIN instead of INTERSECT
Hello, I have a schema like this: // table badge_types id | name +++|++++++++++ 1 | mentor 2 | proctor 3 | doctor // table badges id | badge_type_id | user_id +++|+++++++++++++++|++++++++ 1 | 1 | 5 2 | 1 | 6 3 | 2 | 6 4 | 3 | 6 5 | 2 | 19 6 | 3 | 20 ...
can sql functions such as min() or max() destroy record integrity
Can functions such as Min() or Max() possibly destroy the integrity of a record? Take the case of the query I recently crafted: SELECT account, MIN(phone), MIN(chargeid), MIN(locationid) FROM import1 GROUP BY account, phone is there any chance I am mixing my field data into a new record unintentionally? What if I changed one Min to a M...
Problem select query for number 2?
I just creates this stored procedure in selecting palletnumber=datatype is int. SerialNumber is varchar. Then pallet numbers are 1 - 200..But if i enter 2 it shows 2 with other palletnumbers. I change @search to nchar and its ok but serialnumber is has error. Im new in stored procedure. ALTER PROCEDURE [dbo].[sp_SearchFresh] -- Add ...
how can i recall data from an SQL table TEXT field into and HTML textarea using PHP? (all part of a simple Article Management System)
I building my own simple article management system, and have run into a bit of trouble upon creating a page to edit the articles. I am able to recall the title and tagline of the articles in the form input using the 'value' attribute. However, I am yet to work out how to do the same thing with the form textarea as it has no 'value' att...
Needs help on column wise sql query
I need to display data on my crystal report from the two tables in format given below. In the below table C /A are the image categories. 1, 2 are the total number of C or A's processed by user Amit , linked with UserMaster. Similarly, for CMS and PM, then their totals. --------------------------------------------------------------------...
Help forming a complex SQL statement
Hello friends! I'm coming up stumped on forming a working SQL statement for a project of mine. First of all, I have a "questions" table and a "response" table. The response table is tied to the question table through a foreign id called "question_id" (tied to the questions "id"). A question doesn't necessarily have to have a response, b...
Query property of enum collection mapped as element
Hi, I have the class with enum property collection: public enum PropertyType { Apartment, Townhouse, AndSoOn } public class Company { private readonly ISet<PropertyType> desiredPropertyTypes; public virtual ISet<PropertyType> DesiredPropertyTypes { get { return desiredPropertyTypes; } } // other stuff... }...
Check for non-match in join table
I have two queries who's results should be mutually exclusive, but they are not. This one properly finds all accounts that have an email with a matching name and order_id: SELECT * FROM `accounts` LEFT OUTER JOIN `emails` ON emails.account_id = accounts.id WHERE (emails.type_name = 'name' AND emails.order_id = 1) This one should s...
ms-access: update record where some field = textbox value
can someone please help me with the following query i need to update a datasheet (table) in access through a form: i will have something like this SQLtext = "update table1 set column1="sometext" where column2=textbox1.value" DoCmd.RunSQL SQLtext is this possible to do? i have a textbox on a form and when i click a button on that fo...
How to get the last tuple from a table with SQL Server 2008 R2?
I want to get a field from the last tuple I have inserted to a table using SQL Server 2008 R2? ...
Excel tables as the datasource in VBA
I want to query the table (Named range) from vba using SQL. name id Alpha 1 Beta 2 Gamma 3 I want to query like, "select Name from table1 where id =3" in VBA can someone help with the connection string that needs to be used. thanks! Srin ...
Transact SQL - Get Identity?
How can I get the last or next Identity of a table? ...