I want to query two tables as below to get the rows in the new table that does not already have a name or code entry in the current table. table current name code japan 233 india 65 england 44 table new name code japan 233 india 65 england-main 44 japan 2331 ...
Some while ago, I've been reading through the book SQL and Relational Theory by C. J. Date. The author is well-known for criticising SQL's three-valued logic (3VL).1) The author makes some strong points about why 3VL should be avoided in SQL, however he doesn't outline how a database model would look like if nullable columns weren't all...
I want to have a simple function where i can insert a sqlQuery and get a database answer in json-format like this: function ExecuteQuery(query){ $.post("sql.php", { "query": query }, function(data){ return data; }, "json"); } The response i get is undefined, I think i have misunderstood something quite basic but i ...
I'm trying to make sense of the right way to use JOIN, COUNT(*), and GROUP BY to do a pretty simple query. I've actually gotten it to work (see below) but from what I've read, I'm using an extra GROUP BY that I shouldn't be. (Note: The problem below isn't my actual problem (which deals with more complicated tables), but I've tried to c...
I was wondering what the most efficient way is to reorder my records after I delete a record. Basically I have something like (ItemID, SortOrder). So when I delete an item, the SortOrder column will no longer be incremental. I want to re number sort order so that the numbers are incremental again. I was thinking maybe I could do this wi...
Hi I am trying to put a default value of "1/1/0001 12:00:00 AM" in but first it does not allow me. So I then tried to put "1/1/0001" but when I test it. It gets changed to (((1)/(1))/(1)) and when I try out the default value it gives back "01/02/1900 12:00:00 AM" I am not sure why. ...
Consider a web page having grid-view connected to SqlDataSource having all permission to insert update and delete. Publish the web page. This is all on one computer local Now opening website on browser A - pressing edit of grid-view opening website on broswer B - pressing edit of grid-view. Now I edit in both browsers and press u...
Say, we have a set of records that should be ordered with a non-trivial ordering method, e. g. natural sort or just in some specific sequence, defined by a user. We have to introduce a special column intended just for ordering. Is there a more or less common convention for naming such columns? What names do you use? ...
This is an SQL efficiency question. A while back I had to write a collection of queries to pull data from an ERP system. Most of these were simple enough but one of them resulted in a rather ineficient query and its been bugging me ever since as there's got to be a better way. The problem is not complex. You have rows of sales data. ...
I'm designing a very simple database for my application and it's configured like this: Employee Carnet Name LastName Area Name Document ID Employee (FK) Project (FK) Project ID Company (FK) Title Company Name CEO (FK) NIT Person Carnet Name Lastname Now the gist of this question is, an Area can have many Document; and a Document ...
I've added a service based database to my solution. So, it's working on my system and everything is dandy. However this program will have to run on many many machines all independant to each other (meaning they don't connect or even know they exists) I'm selling the application to different people all over my country. What exactly do I...
Hey, I have a database table called "projects" with the fields 'id', 'locationid', 'name' and 'year'. I query the database to retrieve all the values and store them in $data, before passing them into the relevant view. In what part of the website, I have created a timeline (made of list items generated with a foreach). In this timelin...
Hi Stackoverflow, This is possibly answered somewhere already but had trouble searching for it as I think I'm missing key terms. What I want to do is convert multiple columns into more columns so my query currently returns this: col1 | col2 | col3 __________________________ a | x | 1 b | y | 1 c ...
I shortened the code quite a bit, but hopefully someone will get the idea of what i am tryign to do. Need to sum totals from two different selects, i tried putting each of them in Left Outer Joins(tried Inner Joins too). If i run wiht either Left Outer Join commented out, I get the correct data, but when i run them together, i get really...
I am trying to determine if I have a database connection leak. So I need to see the number of open connections. I have some simple test code that creates a leak: protected void Page_Load(object sender, EventArgs e) { for(int i = 0; i < 100; i++) { SqlConnection sql = new SqlConnection(@"Data Source=.\SQLExpress;UID=sa;PWD=fjg^%k...
I have a Coupon model that has some fields to define if it is active, and a custom manager which returns only live coupons. Coupon has an FK to Item. In a query on Item, I'm trying to annotate the number of active coupons available. However, the Count aggregate seems to be counting all coupons, not just the active ones. # models.py cla...
As said above For instance WHERE In(var 1,var 2,var 3, ..., var 5609,) Any limits before it slows the machine down ? ...
I have a MySQL table containing phone numbers that may be formatted in many different ways. For instance: (999) 999-9999 999-999-9999 999.999.9999 999 999 9999 +1 999 999 9999 9999999999 019999999999 etc. The field is stored as a string. All I want to do is return the field with anything non-numeric removed. What SQL could be used to...
Hi All, I am using the flat file source for a large data migration and the source data in the text stream form unlike UI, datetime or sting. The component is not supporting for fast parsing for text stream. Could I get any ideas to improve fast performance in this scenario. thanks prav ...
I am here to write a SQL statement for finding the weekly summary from a table. I had a table with following fields: UIN, Date, Staff, work_hours Now I would like to gather information how many hours has a staff worked in one week. ...