sql

What is SQL injection?

Possible Duplicates: XKCD sql injection - please explain What is SQL injection? I have seen the term "SQL injection" but still do not understand it. What is it? ...

need help in primary key and foreign key

Hi all, I need help in auto populating the primary key values in foreign key table while inserting data in foreign key table. For Example: I have created table: create table Patient ( PatientId int IDENTITY(1,1) primary key, FirstName varchar(50), SurName varchar(50), Gender char(20), ) Say 5 rows are there in this Patient Table: Say...

SQL to Update records of one table with records from another table?

Hi guys I need help here again - I have two tables here both contain like lots of records in the tens of thousands. Now we have one main table whose schema is like this: ID | Location Name | Label | Description | Longitude | Latitude And we have another table with: ID | Location Name | Longitude | Latitude The issue is that the longi...

MySQL "NULL" questions

I have a table with several columns. Sometimes some of these column fields may be empty (ie. I won't use them in some cases). My questions: Would it be smart to set them to NULL in phpmyadmin? What does the "NULL" property actually do? Would I gain anything at all by setting them to NULL? Is it possible to use a NULL field the same w...

SQL query with regex in ms-access database

Hi, I'm working with C# .Net and ms-access database. I have the following SQL query: `Select ... Like "%<aaa>%"+prmPhrase+"%</aaa>%"` The query is looking for a phrase inside a database field which contains xml data. the query works very quickly, but it returns a lot of false results. I can't search only the exact phrase becau...

SQL: replace with few records (mysql)

There's an index table. (Links tags with ids). REPLACE INTO com_index (word, ids) VALUES ('word1', concat(ids, ' 2')), ('word2', concat(ids, ' 2')) word is a primary key. I try to look through rows, add ' 2' to ids in those of them, which exist; and create a new row with ' 2' if it doesn't. So, I need to look if there's any row wit...

Little explanation of this mysql code

I got this answer from somebody on another Q before, but I would like to get an explanation of this, so that I fully understand the problem. I have a table, with a column which contains a value of either "private" or "company". Thing is, I want to check how many "private" and "company" values are in my records, so that I can display the...

Count rows from results of a "mysql_query"

If I have this: $results = mysql_query("SELECT * FROM table_name WHERE id=$id"); is there then any way to check how many rows which have a field-value of "Private" or "Company" ? I need to show the user how many "Private" and "Company" records where found, without making another query. (There is a column called 'ad_type' which contai...

SQL function to calculate median

Possible Duplicate: Function to Calculate Median in Sql Server I have a table containing two field (more, but not relevant). The fields are Price and Quantity. I want to find several statistically data for this table, and among them is median price when adjusted to quantity. Today I have a basic-slow-not so good looking funct...

Retrieving many-to-many relation properties using SQLAlchemy

I have a many-to-many relationship in which the relation-table contains more columns than only the primary key. As an example, consider a slide show system in which each image could have it's own timeout, and a different timeout depending on the slideshow. A daft example, but it will have to do for the sake of illustration ;) So I imagi...

Matching two columns in MySQL

I'm quite new to SQL and have a question about matching names from two columns located within a table: Let's say I want to use the soundex() function to match two columsn. If I use this query: SELECT * FROM tablename WHERE SOUNDEX(column1)=SOUNDEX(column2); a row is returned if the two names within that row match. Now I'd also like t...

sql query number of parent

i have table called type ID Name ParentID --------------------- 1 name1 0 2 name2 0 3 name3 1 4 name4 2 5 name1 1 i need to know how many parent (descendants) each type have EX: ID -------- descendants ID-> 1 (have no parent) ID-> 3 (have 1 parent (ID->1)) ID-> 5 (have two parent ((ID->3(ID...

Reorder integer except for value 0 with sql

I'm trying to get an ordered list of rows out of my MYSQL database table based upon an integer value 'place'. SELECT * FROM mytable ORDER BY place; This works okay, except that all rows with value place=0 should appear at the end of the table. So if my table is: name place ---- ----- John 1 Do 2 Eric 0 Pete 2 it should become: ...

How to make a complex named scope play nice with associations and other named scopes [rails]

I have the following named scope on class RentableItem < ActiveRecord::Base named_scope :available_at, lambda{ |starts_at, ends_at| { :select => "t.*", :from => "(SELECT ri.*, COALESCE(c1.start_date, '#{starts_at}') AS EarliestAvailable, COALESCE(c2.end_date, '#{ends_at}') AS LatestAvailabl...

Sql Subquery with sum

I have two tables Invoices and Payments. invoices have payments. I want to write a query that displays unpaid invoices and the remaining amount of the invoice, which is calculated by summing up the payments of the invoice and subtracting it from the invoice amount. I tried this query but it doesn't work. please how can i do it. SELECT I...

SQLite Update date sql

SQLite Newbie I am trying to update a table with a date. Something like this: Update MyTable Set MyCol=GetDate() What is the correct syntax? ...

if I want to find what's referencing an object in SQL Server, is searching syscomments comprehensive?

Let's say I have information in a table that is in the wrong database (like Customer info in the Items database). I really want to move this information, but I need to figure out who's using it. If I use this code to search for the table name, say CustomerContactNumbers, is there any possibility of things slipping by? I'm going to ignore...

sql query date time

i have a datestamp field in my table structure. using sql, i want to find any user who registered in yesterdays date using these time range. eg: 2010-02-06 14:00:00 2010-02-07 10:00:00 i will be running this query once a day to grab users. so tomorrow will be: 2010-02-07 14:00:00 2010-02-08 10:00:00 the day after tomorrow will be: ...

Creating a default query for a column in a table (SQL)???

Hey I have a column in one of my tables which is suppose to be the total sum for from the rows of a number of tables. Is there a way i can have a default query which runs on the total sum column so that every time a row is added to the other table an update is made in the total sum column. Thanks ...

UItableView referencing sections from core data:

Pleaseeee if you guys can help me or point me on the right direction, I would really appreciate. I am saving some data the user inputs into a data file using SQLITE, I am saving date, time, reading, and a note, as the following: //Create a Reading Object Readings *readingObj = [[Readings alloc] initWithPrimaryKey:0]; readingObj.date = ...