sql

Select statement using WHERE x IN (SELECT ...)

I have two tables: [ product_to_category --------------------- product_id category_id [ category --------------------- category_id parent_id I need to get all product_id's with a category.parent_id of '39'. Here's what I'm trying, but it's returning empty when there are at least a few hundred: SELECT product_id FROM product_to_cate...

Database cascading error

I have three tables in my database: Events, Jobs, and CollectableEntities. Events has a nullable FK pointing to Jobs' PK. Events and Jobs both have non-nullable FKs (that are also those table's PKs) pointing to CollectableEntities' PK. These FK relationships are set to cascade on update and delete. Basically, when you delete a Collecta...

Zend Framework sql update query

How would I write this sql the Zend Framwork way? UPDATE register SET balance = (balance + 10) WHERE added_date > 1259944184 ; I can't find any examples of this on Zends website or the web. Do I need to use "Zend_Db_Expr" ? thanks ...

Convert UPDATE with INNER JOIN from SQL for use in MySQL

I'd like to convert this for us in MySQL: UPDATE product SET price = 12.95 FROM product INNER JOIN product_to_category ON product.product_id = product_to_category.product_id INNER JOIN category ON product_to_category.category_id = category.category_id AND category.parent_id = 39 MySQL doesn't like the FROM portion...

Best way to return search info from a database in php

So I have a form for people to fill out, which is below... Once they fill that form out, how can I query it and return the information from my database? <form name="form" method="get" action="agents.php"> <table> <tr> <td width = "20%">Last Name: </td> <td><input type="text" name="LASTNAME" size="20"/> ...

Tips for creating an admin login for a client's website

Hello, I'm creating a website for a real estate firm. Looking great, but the last component is to create an admin login for my client so he can login and add/remove real estate properties he has to sell. I plan to store his entries in a database file. The entries will be displayed in a web control on a public page which will be bound to...

MySQL: splitting one table to multiple tables (same columns) for performace increase?

This question is about performance, not about possible solutions. My system holds many items of different categories. Each category has its own table since each table has many rows AND the fields are different. ItemA - id, fld1, fld2 ItemB - id, fld1, fld3, fld4 ItemC - id, fld1, fld3, fld5 .... Now there's a need to manage user inve...

Cross language development problem

I'm working on a project that involves a database (My SQL), website (PHP) and a custom high performance server application (C++). The C++ application (and its accompanying client application) make up the main bulk of the project, with the database storing long term data for it. The website is primarily for displaying various statistics, ...

Incrementing an Integer in SQL Server

Noob question here, every time I change a certain record in an SQL Server 2008 R2 table, I want to increment a RevisionId record; to do so, I'm using the following syntax: UPDATE TheTable SET RevisionId=(SELECT RevisionId FROM TheTable WHERE Id=@id)+1 WHERE Id=@id; Btw, I'm going to put this into a trigger so that this happens automag...

Sql Query Solution

I have a query as follows. select strftime('%Y-%m',A.traDate) as Month,sum(A.TraAmt) as Total,C.GroupType from TransactionTbl A left join TransactionCategory B on A.CategoryID = B.CategoryID left join CategoryGroup C on B.CatGRoupID=C.CatGRoupID where A.UserID=1 and A.ProfileID=1 and date(A.TraDate) between date('2009-12-01') and dat...

T-SQL - How to write a complex conditional join including a many-to-many join

I have tried solving this problem by posting other related questions here that focused on parts of the query. However I might as well post the entire thing and see if anyone can help. I have the following tables with the following fields: tblPerson - PersonID, PersonName tblGroup - GroupID, Name tblGroupMembership - PersonID, GroupID ...

How to re-write this inner join subquery from SQL to Lambda

SELECT ulcch.ID, ulcch.UserLoginHistoryID, ulcch.StatusID, ulcch.ClientModuleID, ulcch.DeviceState, ulcch.UpdatedAt, ulcch.CreatedAt FROM UserLoginClientConnectionHistory AS ulcch INNER JOIN (SELECT MAX(CreatedAt) AS maxCreatedAt FROM UserLoginClientConnectionHistory AS ulcch1 GROUP BY UserLoginHistory...

FirstName, LastName in SQL, too complex?

This SQL seems complex, is there an easier way to get FirstName, LastName when one or both of the fields can be NULL? SELECT COALESCE(LastName,'')+ CASE WHEN LastName+FirstName IS NOT NULL THEN ', ' END+ COALESCE(FirstName,'') AS Name FROM Person ...

import excel to sql db table

I am trying to write data from an excel spread sheet to a SQL Database. I have been able to connect to the Excel Spreadsheet and read the data but I am unable to get the data to insert into the SQL DB table. the current code is as follows any help most appreciated. Dim plmExcelCon As System.Data.OleDb.OleDbConnection Dim ldEx...

How to create Composite Key and how to refer it in other table as a foreign key

I have a table following table create table tblcountry ( unqid uniqueidentifier name varchar(100) isremoved bit ) I want to create primary key on basis of unqid + isremoved and in which isremoved must be true i have got another table: create table tblstate ( unqid uniqueidentifier, name varchar(100) f_tblcountry uniquei...

Few questions from a Java programmer regarding porting preexisting database which is stored in .txt file to mySQL ?

I've been writing a Library management Java app lately, and, up until now, the main Library database is stored in a .txt file which was later converted to ArrayList in Java for creating and editing the database and saving the alterations back to the .txt file again. A very primitive method indeed. Hence, having heard on SQL later on, I'm...

SQLPlus - Count function across several tables

hello, i am trying to count the number of riders in my data. and i am having trouble figuring this out. sample of my output is noted below. The data comes from many different tables and I had to join the tables which is not the problem I am having. I am trying to get the count number of RIDERS by EVENT by DESCRIPTION. And still disp...

MySQL - Unable to create column

I am using MySQL 5.1. When i am trying to add new field, it throws error like this, Database name is "ebill". Error Code : 1025 Error on rename of '.\ebill\#sql-98_477' to '.\ebill\user' (errno: 150) (0 ms taken) This is my sql query: alter table `ebill`.`user` add column `User_Password` varchar(25) NULL Where is the issue? ...

Mysql deadlock explanation needed

I received the following deadlock log via "SHOW INNODB STATUS". Can someone care to explain why the transaction was aborted? It seems that Transaction 2 is holding the lock, but is also stuck requesting the same lock (except for the "waiting" part), which leads to a deadlock when Transaction 1 requires it as well. ======================...

SQL Server 2005:charindex starting from the end

Hey,everyone I have a string 'some.file.name',I want to grab 'some.file'. To do that,I need to find the last occurrence of '.' in a string. My solution is : declare @someStr varchar(20) declare @reversedStr varchar(20) declare @index int set @someStr = '001.002.003' set @reversedStr = reverse(@someStr) set @index = len(@...