sql

Sybase: SQL Update table based on another table

So I have this sql query select user_id, profile_id from user_groups join profile_groups on user_groups.group_id=profile_groups.group_id group by user_id, profile_id having count(user_groups.group_id) = (select count(*) from profile_groups as pg where pg.profile_id=profile_groups.profile_id) and count(profile_groups.group_id) = (se...

To aggregate or not to aggregate, that is the database schema design question

If you're doing min/max/avg queries, do you prefer to use aggregation tables or simply query across a range of rows in the raw table? This is obviously a very open-ended question and there's no one right answer, so I'm just looking for people's general suggestions. Assume that the raw data table consists of a timestamp, a numeric foreig...

Insert binary data into SQL from c# without a stored procedure

Does anyone know whether if its possible to insert binary data into an SQL field from c# without using a stored procedure? For example - convert a byte array into base64 or something like that and then use a text command like the following... String.Format("update A set B = {0} where C = D", Convert.ToBase64String(b)); where b is a b...

MySQL: Removing duplicate columns on Left Join, 3 tables

I have a table that uses 3 foreign keys into other tables. When I perform a left join, I get duplicate columns. MySQL says that the USING syntax will reduce the duplicate columns, but there aren't examples for multiple keys. Given: mysql> describe recipes; +------------------+------------------+------+-----+---------+-------+ | Fiel...

Pull 3 tiers of related data from a single table

Our business as a tiered Salesman relation, sometimes called an omni-tier. Its 3 deep. in english: Salesman-A-tier has people under them, we'll call them salesman-B-tier, and b-tier has salesman under them salesman-C-tier. table: id, name, agentId 1011, bob, 0 1012, jim, 1011 1013, tim, 1011 1014, sam, 1011 1015, dav, 1013 1016, kim...

is there a need to refactor a large data access layer

i have a data access layer that abstracts the rest of the application away from the persistence technology. Right now the implementation is SQL server but that might change. Anyway, i find this main data access class getting larger and large as my tables grow (about 40 tables now). The interface of this data access layer is any questi...

TQL: how to do stress testing for a stored procedure

I got a SP with a parameter that is supposed to be fed unique value like '111','112',... I would like to execute stress testing to compare the latency after adding index. Is there any sql command or tool can help on this? ...

How to get the tag relations results just by using SQL(s)?

Table tags: article_id tag 1 Language 1 Java 1 c++ 2 Language 2 c++ 3 c++ and how can I write SQL(s) query(s) to make the data like below: Table tags_relations: tag1 tag2 relations_degree Language C++ 2 Language Java 1 note: if...

Kohana 3 Random Query

Hello, Im new to kohana 3 here, and my understanding of it is very limited. The problem is instead of displaying the first row, what code should I use when I'll try to get a random row from a certain table? BTW: I'm using the ORM module. Thanks ...

table design + SQL question

I have a table foodbar, created with the following DDL. (I am using mySQL 5.1.x) CREATE TABLE foodbar ( id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, weight double not null, created_at date not null ); I have four questions: How may I write a query that returns a result set that gives me th...

Building SQL Query

Hi all, I need some help to build SQL Query. I have table having data like: ID Date Value 1 12/01/2009 4 2 12/02/2009 3 3 12/03/2009 6 4 12/01/2008 2 5 12/02/2008 4 6 12/03/2008 5 Here is my query: select ID, Date, Value from MyTable where Date between '12/01/2009' and '12/04/2009' ...

Alter composite key to include newly added column in sql server 2005

I have a table GB_Assignor_Assignee. I have a primary key which includes this combination(StateCode, CountyID, Doc_Type_Group_Code). Now i have to add a new column Doc_Type_Code. I added it by altering table. I want to include this new column inside this primary key.So my combination will be(StateCode, CountyID, Doc_Type_Group_Code,Doc_T...

'/' as alias name creates error

hi all I am exporting from a database to excel. While doing so it is exporting the records as such to the excel. So I am using an alias name to modify the query in c#.net but it generates an error. The query is select name as personsname,eventtime as Date/time from mytable Here the Date/time as alias name creates error Can any...

select * from table_name where column like '&nbsp'

I need to find records containing html code such as '&nbsp' But when I try to run the select * from table_name where column like '&nbsp%' I got prompt asking for the value of nbsp. I guess the database thinks that nbsp is a parameter. I am wondering if the is an escape character so that I can tell the database that "&" is part of my q...

mysql's current_timestamp for java derby

Hi all, In MySQL I can easily set the default value of a timestamp field to be the current time fieldname timestamp not null default current_timestamp Does anyone know how to do the same thing in java derby? ...

How to delete unique index referenced by foreign keys?

I have a table, let's call it Users. This table has primary key called Id. Despite having Id as primary key (unique clustered), it has other index (unique nonclustered) on the same column(Id). I would like to drop this constraint, but foreign keys reference this unique nonclustered index and I get The constraint ... is being referenced...

how to auto resize image in sql database in a table

Hello all, I have a question, I have a database with some webshop products. I load 2 images from a url (domain.com) one is for the thumb image (product_thumb_image) and the other is a full image. (product_full_image) I use for both the same url and resize the image by having a Height="100" attribute in the image tag.I know this is not...

How do I return a Unicode value in this SQL example?

I need to return Russian text from a SQL Server 2005 database table. In the following example which is a simple way of describing my dilemma, the @Test variable will print out question marks: DECLARE @Test nvarchar(max) SET @Test = 'Баннер' PRINT @Test (Note that the @Test value is Russian text, for those who don't have the font ins...

Issues with a Prev link

Right now I have some links to move to a Previous page or to the Next page (or any page number). ex. <--prev | 0 | 1 | 2 | 3 | next--> My issues is that I am only listing items on the page with a "status='A'". (A for active... some items are no longer being listed on this page and would have a 'D' for Delete or 'S' for Sold Out). In ...

tSQL CASE to control execution

I understand how to use a case statement to return different values: SELECT CASE Color WHEN 'Blue' THEN 'Water' WHEN 'Black' THEN 'Oil' WHEN 'Red' THEN 'Blood' END FROM dbo.Liquid Is there a way to use it to control flow instead of IF-ELSE, i.e. DECLARE @Color varchar() SELECT @Color = Color FROM d...