sql

Getting the most recent records with 2 or more related entries with SQL

Hello, I inherited an interesting table structure. This table structure looks like the following: GroupTable ID Name Description GroupItem ID GroupID Name CreatedBy CreationDate I need to get the three most recent GroupItem elements created by a specific user. The twist comes in with a requirement associated with the GroupID. T...

Check that the values in all the columns are different in sql - Oracle

I have a table with 14 columns in Oracle. All the columns are of integer type. i need to check no two columns in one row have same integer value in them. How can i do this using SQL. Or is it only possible to do using PL/SQL? ...

Oracle: select into variable being used in where clause

Can I change the value of a variable by using a select into with the variable's original value as part of the where clause in the select statement? EI would the following code work as expected: declare v_id number; v_table number; --set elsewhere in code to either 1 or 2 begin select id into v_id from table_1 where name = 'John Smith...

SQL question: Show me students who have NOT taken a certain course?

So I have these tables: STUDENTS: Student ID - First name - Last name - Email COURSES: Catalog ID - Course Name - Description TERMS: Term ID - Start Date - End Date COURSEINSTANCES: CourseInstance ID - Catalog ID - Term ID STUDENTCOURSES: StudentCourse ID - CourseInstance ID - Student ID - Date added to database This makes it easy ...

Stored Procedure

Hello, i'm stuck with creating a stored procedure that should update a calculated column daily. Unfortunately my knowledge about stored procedures is limited but it seems to be the right place. I try to give more background informations: The Main-Table stores claims and is called tabData. It has a column 'IsReturn' with datatype bit(b...

Entity Framework - Problem with querying nullable column

Hi, I have a problem querying data from a table with a nullable tinyint column. The problem seems to be that the query is generated as: AND ( CAST( [Extent1].[PositionEffect] AS int) = @p__linq__3) => @p_linq_3 = NULL If i run that query manually it doesn't turn up any results. However, when I replace the query with: AND ([Extent1]...

SQL Server composite key problem

I have 2 tables: create table numbers2 ( id1 int IDENTITY(1,1) not null, id2 int not null, primary key(id1, id2) ) and table 2 create table ttt ( id int identity(1,1) not null, first_name varchar(50) null, last_name varchar(50) null, sex varchar(1) check (sex in ('m', 'f')) null, number_id int not null, id_id1 int not null, id_id2 i...

SQL Full day difference

Does anyone know how to get the datdiff in full days between 2 days. Im currentlly using datediff(day,createddate,dateserved) But need it to return how many full days i.e Created = 1/7/2010 2100 dateserved = 2/7/2010 2000 currently the datediff would show 1 day but i need it to show 0 until dateserved passes 2100 Any i...

Query Performance, Multiple "OR's" vs. Separate Statements

Let's say I have an Oracle database table of 40MM 9-digit ZIP codes which includes the 4-digit ZIP code for each. I have a list of 800 5-digit ZIP codes and I need to find all the associated 9-digit ZIP codes. There are 40K 5-digit ZIP codes altogether. Assume we have indexed the 5-digit ZIP code field with a B*Tree (conventional) ind...

Calculate non-standard auto-increment automatically

The table I am working with does not have a standard auto-increment field to use as a primary key, so I need to come up with a way to automatically calculate the value that should be used in the field. My first thought was to create a trigger to happen AFTER INSERT, however, as far as I can tell, there's no easy way to reference the row...

Mysql order by comma-delimited column value

Hi Guys I would like to know if it's possible to order by a comma-delimited set returned by a subquery? I have the following: SELECT navigation.id, documents.template_id FROM navigation INNER JOIN documents ON navigation.document_id = documents.id AND FIND_IN_SET(navigation.id,(SELECT numeric_lineage ...

Function within a Function TSQL

Hi, Can I call a scalar function within a table-valued function? Thanks ...

SQL -- Derive Date Difference Column

+------+-------------------------+ | proc | endTime | +------+-------------------------+ | A | 2010/01/01 12:10:00.000 | | B | 2010/01/01 12:08:00.000 | | C | 2010/01/01 12:05:00.000 | | D | 2010/01/01 12:02:00.000 | | ...| ... | So basically the data I pull from SQL will look somethin...

Explicit syntax for select xxx from table1,table2

I have a select statement select someFields from table1,table2 which is a cartesian join, right? What's the explicit syntax for such a join. Searching the web for , isn't yielding much luck. ...

Many rows to one row result

I hope I'm explaining this well. I'm struggling with this query: I have this table that is something like this: InvoiceNum Amount Type - where type could be item, shipping or tax. So what I want returned is one row per invoice: InvoiceNum, ItemAmount, ShippingAmount, TaxAmount. Here's an example: Invoicenum Amount Type 1 ...

Numeric types and row size in MySQL

I have a user table structured like this: id MEDIUMINT(7), username VARCHAR(15) Would it technically be faster if I changed it to this instead: id MEDIUMINT(5), username VARCHAR(15) I'm confused because even though the total row length in terms of characters and digits would be shorter, I assume the number of bytes used would b...

picture from SQL database not displayed by jquery lightbox in detailsView ImageField and asp:Image control (master pages)

when i click the picture that is displayed in the detailsView ImageField, the jquery lightbox pops up, but does not show the picture. it shows a red x in the middle of the lightbox. the same thing happens with the asp:Image control shown in the code below. The picture is stored in a SQL database Image datatype. Using master pages. <...

SQL: Find and Replace with SQL?

I have a MySQL InnoDB database. I have a column my in 'article' table called url that needs to be updated. Stored in article.url = /blog/2010/article-name /blog/1998/the-article-name /blog/... I need to change /blog/ to /news/. (E.g. now article.url = '/news/...') What is the SQL needed to replace "/blog/" with "/news/" in the art...

SQL LIKE query not working

I'm trying to run the following query against an Oracle DB, but the query is returning 0 records: select * from TABLE where upper(FIELD) like '%SEE COMMENT%' I know this field contains many records with 'See Comment" in it. For example, here is one of the records: =if(and(Robust_Mean>=20,Robust_Mean<=70),.03*(Robust_Mean+29), if(Ro...

What kind of structure is that?

Hi. In the project where I work I saw this structure in database, and I ask to all of you, what a hell of modeling is this? TableX Columns: isMonday, BeginingHourMonday, EndHourMonday, isTuesday, BeginingHourTuesday, EndHourTuesday and so on... Is this no-sql? I did not asked to the personn who created becaus I'm ashamed :$ Bye. ...