sql

IndexOf function in t-Sql

Given an email address column, I need to find the position of the @ sign for substringing. What is the indexof function, for strings in t-sql. Looking for something that returns the position of a substring within a string. in c# var s = "abcde"; s.IndexOf('c'); // yields 2 ...

Database design question, using multiple tables or XML

Imaging the following 3 relationships in a data model Entity > Path > Link Both relationships are 1 to many. So Entity has multiple paths and a path has multiple links. Should I do this as 3 tables with relationships between the tables Or create a table that stores that path information as XML. This table (lets call it Paths), will...

Maximum size for a SQL Server Query? IN clause? Is there a Better Approach

Possible Duplicate: T-SQL WHERE col IN () What is the maximum size for a SQL Server query? (# of characters) Max size for an IN clause? I think I saw something about Oracle having a 1000 item limit but you could get around this with ANDing 2 INs together. Similar issue in SQL Server? UPDATE So what would be the best approach i...

String Manipulation in SQL Server

Is there any way of setting an "Column Alias" in SQL Server? For Example i have two columns. Description and Price ID colDescription colPrice 1 Red ball costs %colPrice% dollars 2 2 Blue ball costs %colPrice% dollars 3 The selection of colDescription for ID=2, should fe...

Java ResultSet hasNext()

I've got a class that implements Iterator with a ResultSet as a data member. Essentially the class looks like this: public class A implements Iterator{ private ResultSet entities; ... public Object next(){ entities.next(); return new Entity(entities.getString...etc....) } public boolean hasNext(){ ...

How do I create a view where column names are defined from query return values

First, my table layout: tblEquippedItems: <-(table name) slotid (FK), itemid (FK), charid (FK) <-(attributes) So I have this table that describes the items a character has equipped. Slot id refers to the item slot where the item, denoted by itemid resides. Charid describes the character that has these items equipped. What I'd like to ...

sql group by count problem

Hi, I have the following query: select d.restaurant_id, restaurant_category, dish_name, cuisine_id, count(ingredient_id) from restaurant r, dish d, composition c where r.restaurant_id = d.restaurant_id and d.restaurant_id = c.restaurant_id and d.dish_id = c.dish_id group by d.restaurant_id having count(distinct cuisine_id) > ...

MySQL KEY question

CREATE TABLE `django_comments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `content_type_id` int(11) NOT NULL, `object_pk` longtext NOT NULL, `site_id` int(11) NOT NULL, `user_id` int(11) DEFAULT NULL, `user_name` varchar(50) NOT NULL, `user_email` varchar(75) NOT NULL, `user_url` varchar(200) NOT NULL, `comment` longtext N...

Filtering objects with many to many relationships

I have a two tables one for documents one for mapping to categories. Documents id | document_name 1 | somename.doc 2 | anothername.doc Documents_to_categories cat_id | doc_id 10 | 1 10 | 2 11 | 3 12 | 1 Some documents can map to multiple categories. What I want is to be able to select document...

Hibernate: Query without SQL-strings?

Hello everyone! Is it possible (and if yes, is it advisable) to do queries of Hibernate-mapped data without using SQL-strings, but some OOP way instead? ...

HABTM Query help

I have a HABTM relationship between 'articles' and 'tags' Problem: I'm only looking for articles with BOTH the tag 'sports' and 'outdoors' but not articles with only one of these tags. I tried this: SELECT DISTINCT article.id, article.name FROM articles inner JOIN tags ON (tags.name IN ('outdoors', 'sports') inner JOIN articles_tags O...

Is it a good practice to write subqueries in MySQL?

I am writing the following sub query for some project specific purpose: SELECT count(*) from table1 WHERE userB='$p' AND userA IN (SELECT userB FROM table1 WHERE userA='$row[username]') I was curious if this was the best practice when doing it in PHP or should I resort to the conventional way of first getting the subquery resu...

MySQL Query: Optimal Querying

i have this code SELECT DISTINCT idx_campus_bookinfo,c.userid as Buyer,bookname,book_explain,writedate FROM campus_bookinfo cb LEFT JOIN user_books ub ON idx_campus_bookinfo = id_product LEFT JOIN customer c ON ub.id_customer = c.id_customer where cb.idx_campus = 1 and cb.idxuser = 29 ORDER BY writedate DESC which give an output of ...

Stored Procedure consist Add column, Update data for that column, and Select all data from that table

I've written a stored procedure as following: CREATE PROC spSoNguoiThan @SNT int AS begin IF not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = 'NhanVien' and column_name = 'SoNguoiThan') ALTER TABLE NhanVien ADD SoNguoiThan int else ...

TSQL Returning one Row when expecting multiple

I have two tables, a patient table an and appointment table. I'm attempting to retrieve the information from both tables depending on what doctor is logged in at the time. My stored procedure is as follows: ALTER PROCEDURE dbo.sprocGetAllAppointmentsForUser @UserID varchar(50) AS SELECT Appts.appt_id, Appts.patient_id, Appts.dr_id, A...

What kind of SQL injections do you know?

I'll provide a simple one here: $query = "select id from accounts where email='$_POST[email]' and psw='$_POST[password]'"; $result = mysql_query($query,$con); if($row = mysql_fetch_assoc($result)) return true; else return false; If the password is 1' or '1'='1,then will do the trick! What other tips have you known? ...

MYSQL SELECT LIKE "masks"

Hi guys, I'm using a field in a table to hold information about varios checkboxes (60). The field is parsed to a string to something like this "0,0,0,1,0,1,0,1,..." Now I want to make a search using a similar string to match the fields. I.e. "?,?,1,?,?,1,..." where the "?" means that it must be 0 or 1 (doesn't matter), but the "1...

counting number of rows while skipping some based on columns

I have a table in SQL Server 2008 that looks kind of like this: ID I1 I2 ... IN ------------------------- 1 2 3 ..... 2 2 0 0 ..... 0 3 2 1 ..... 5 Where IN is about 9 columns. What I need to do is count the number of rows, but skipping rows where the values of I1..IN are 0's. I'm new to SQL and I have basically...

SQL "In" Statement Match Anything

If I have a query like this SELECT * FROM table1 WHERE col1 IN ({SUBS}) Is there anything I can replace {SUBS} with that will return all rows in the table? Further details: I am building the SQL dynamically in my app, so I cannot (should not) edit other parts of the query except what's in braces. So, SELECT * FROM table1 will no...

MySQL syntax error

The SQL statement is mysql_query("update mytable set duration=floor(min(max(TIME_TO_SEC(TIMEDIFF(NOW(), moment))/60,5),600)) where taskid='$taskid' and memberid='$memberid'")or die(mysql_error()); and the error message is You have an error in your SQL syntax; check the manual that corresponds to your MySQL server ve...