sql

SQL Compact (CE) problem with creating foreign key

I'm trying to create a foreign key in a SQL Compact database but I keep getting the error message "A foreign key value cannot be inserted because a corresponding primary key value does not exist." TableA is referencing TableB already and I'm trying to create a reference from TableC using the same primary key in TableB. Since I already h...

implementing UNIQUE across linked tables in MySQL

a USER is a PERSON and a PERSON has a COMPANY - user -> person is one-to-one, person -> company is many-to-one. person_id is FK in USER table. company_id is FK in PERSON table. A PERSON may not be a USER, but a USER is always a PERSON. If company_id was in user table, I could create a unique key based on username and company_id, but i...

MySQL - How to run a SELECT query that orders results by a score -- such as upvotes - downvotes.

I have two tables -- an article table and a vote table. Users can either vote up or vote down articles of their choice (similar to Reddit). The fields I have in the vote table are: article_id user_id vote The value for the vote field can either be 0 or 1... (0 if they vote down the article, 1 if they vote up). What I'm trying to d...

Solution for Linq2SQL expressions that has no translation

Is there a way to provide translation for expressions that have no translation ? like double.parse() ...

PHP/MySQL COUNT doesn't seem to work as it should

I'm pulling my hair out for this, what is wrong with this query: SELECT COUNT(id), * FROM location WHERE country = '$country' AND LCASE(namenodiacritics) LIKE LCASE('%$locname%') ORDER BY name ASC Am I allowed to COUNT(id) and * in a single query? I keep getting this error: You have an error in your SQL syntax; ...

Can anyone suggest a DBMS which is fast and efficient at writes?

I have a project which is going to be write-heavy rather than read-heavy. I was wondering if anyone had any suggestions for open source DBMS setups which are quick at writes? It doesn't necessarily have to be a relational DBMS either; I'm open to suggestions. ...

Data from 4 different database

I need to get data from 3 different databases on one event command. Can anybody tell me any efficient way besides I am querying to all three different database servers in a row:- Server 1 : Select * from .... Server 2 : Select * from..... and so on... Thanks very much ...

select maximum score grouped by date, display full datetime

Gday, I have a table that shows a series of scores and datetimes those scores occurred. I'd like to select the maximum of these scores for each day, but display the datetime that the score occurred. I am using an Oracle database (10g) and the table is structured like so: scoredatetime score (integer) ------------...

Limiting mySQL data as 5 column 2 entries per column 1 entry.

Hey there, I'm new to mySQL, so my poor knowledge of the languages might be precluding me from searching for the result I need. I am building a simple application in CodeIgniter that will access the Twitter API and return a table of status updates for a certain number of various users. I would like to limit this to showing 5 updates per...

How to check if it's null?

I retrieve data from database like below. How do I check whether the value retrieved from database is null? Private Function GetBatch() As DataSet Dim dataset As New DataSet Dim adapter As Data.SqlClient.SqlDataAdapter Dim cn As New System.Data.SqlClient.SqlConnection(connectionstring()) GetBatchCommand.C...

LINQ-to-SQL IN ()

Currently I use a block of code like this, to fetch a set of DB objects with matching IDs. List<subjects> getSubjectsById(List<long> subjectIDs){ return ctx.tagSubjects.Where(t => subjectIDs.Contains(t.id)).ToList(); } But this is really inefficient, because it requires the entire table to be read from the database and then filtere...

Django: queryset filter for *all* values from a ManyToManyField

Hi (sorry for my bad english :p) Imagine these models : class Fruit(models.Model): # ... class Basket(models.Model): fruits = models.ManyToManyField(Fruit) Now I would like to retrieve Basket instances related to all fruits. The problem is that the code bellow returns Basket instances related to any fruits : baskets = Baske...

ORACLE SQL:Get all integers between two numbers

Is there any way to select the numbers (integers) that are included between two numbers with SQL in Oracle; I don't want to create PL/SQL procedure or function. For example I need to get the numbers between 3 and 10. The result will be the values 3,4,5,6,7,8,9,10. Thx. ...

How do I create a loop in Access VBA/SQL to select records and output to Excel?

Hello, I need some help on writing some VBA/SQL code into a module in Access, which should do the following: Loop through Table 'Schools', with just one field (SchoolName). Use each value in the first table to select records from Table 'ChildData', with several fields about individual children, including which school they attend (Scho...

How to write this in SQL?

I have one table called Visit and another called Measurement. Primary key on Visit is vis_id and it is also foreign key in table Measurement. Now I would like to write a report based on data in table Measurement on following conditions: Visit has field "itemAmount". I want only have data that has itemAmount = 2 in table Visit and both r...

SQLite3: programatically determine whether a column is sorted in ascending or descending order

Suppose I have an index on a table in SQLite3: CREATE TABLE Person (id integer primary key, firstName varchar(20), lastName varchar(20), address varchar(200)); CREATE INDEX IX_Person ON Person (lastName ASC, firstName ASC); I can discover which columns are in the index like this: sqlite> pragma index_info('ix_person'); 0|2|lastName 1...

SQL SERVER 2008 Dynamic query problem

I have a dynamic query which reads like this Alter PROCEDURE dbo.mySP -- Add the parameters for the stored procedure here ( @DBName varchar(50), @tblName varchar(50) ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements f...

ORA-02437: cannot validate <name> - primary key violated

Hi, I have a table: CREATE TABLE MY_TABLE ( MY_ID NUMBER NOT NULL, COLUMN_1 NUMBER, COLUMN_2 NUMBER ); ALTER TABLE MY_TABLE ADD CONSTRAINT PK_FOO PRIMARY KEY (MY_ID); at a later point, when executing the following sql, I get an error: ALTER TABLE MY_TABLE DROP PRIMARY KEY DROP INDEX; ALTER TABLE MY_TABLE ADD CONSTRAINT PK_FOO P...

Conditional columns in OR-constrained SQL statements

This question is about a general technique in SQL, that I can't quite work out how to best achieve with my current understanding of the language. Note that this will be running against Oracle, in case any vendor-specific functionality would be particularly useful, though I expect there is a nice way of doing this with "pure" SQL. Short...

Automatically create schema on SQL Server using SQLBulkCopy in .NET

I use SQLBulkCopy to copy a .NET datatable to a temporary SQL Server table. Is there a way to dynamically create this temporary SQL Server table from .NET code without using SMO? E.g. dynamically creating a "CREATE TABLE" statement in .NET? If somebody has a solution / some running code for this problem I would really appreciate it! ...