sql

MYSQL create command optimization?

Following is the code to create a table in mysql database. CREATE TABLE IF NOT EXISTS `hightraffic` ( `id` int(11) NOT NULL auto_increment, `videoID` int(11) NOT NULL default '0', `userid` int(11) NOT NULL, `name` varchar(255) NOT NULL default '', `title` int(11) NOT NULL default '0', `date` datetime NOT NULL default '0000-0...

Balancing different types of result records limited by a TOP 100

Given a table Records with the columns id int, Type int and Name varchar(50), I can make search queries like so: SELECT id, Type, Name FROM Records WHERE Name LIKE '%Foo%' To tweak the performance a little, I'd like to give only a limited amount of results; currently 100 — simply by adding TOP 100 to the statement. This, however, can ...

CI PHP if statement w/ sql syntax

This is a quick syntax question... I need to block out an HTML element if two SQL statements are true w/ php. If the status = 'closed', and if the current user is logged in. I can figure out the calls, I just need to see an example of the syntax. :) So, If SQL status=closed, and if current_user=is_logged_in()...something like that. ...

Nested Set Model and SQLAlchemy -- Adding New Nodes

How should new nodes be added with SQLAlchemy to a tree implemented using the Nested Set Model? class Category(Base): __tablename__ = 'categories' id = Column(Integer, primary_key=True) name = Column(String(128), nullable=False) lft = Column(Integer, nullable=False, unique=True) rgt = Column(Integer, nullable=False,...

What approach should I take to split a string and update muliple SQL tables?

I have an Article object with the following properties: Article Name (varchar) Article Body (varchar) Article Tags (varchar) The Article Tags is a space delimited string of text values that a user has tagged the aritlce with. The 3 properties are passed to a stored procedure. I now want to do the following: 1) Insert the Article N...

Select TOP N and set variable if more could be selected

Is there a more efficient way of doing the following SQL? I want to select the top 50 results, but I also want to set a variable to tell me if I would have gotten more results back without the TOP DECLARE @MoreExists BIT SET @MoreExists = 0 DECLARE @Count INT SELECT @Count = Count(*) FROM MyTable WHERE ... --some expensive where ...

What would be the best schema to store the 'address' for different entities?

Suppose we're making a system where we have to store the addrees for buildings, persons, cars, etc. The address 'format' should be something like: State (From a State list) County (From a County List) Street (free text, like '5th Avenue') Number (free text, like 'Chrysler Building, Floor 10, Office No. 10') (Yes I don't live in U.S...

SQL: Selecting parents and their children

I'm sure this is a common query but I'm not certain how to phrase it best. I have two tables: Questions { Id, Text... } Answers { Id, QuestionId, Text...} I would like to retrieve a list of Questions and their Answers, so that the results can be displayed like this: Question A 1st answer to question A 2nd answer to question A 3rd...

Creating tables with fields from 3 different tables

I'm trying to group data from the following three tables to create a new table. the account_no fields in bmf and acct_map are actually drawn from cmf. Fields: bmf: account_no, trans_date cmf: account_no, balance_due acct_map: account_no, external_id The fields I want in my new table are : external_id, account_no, balance_due When...

SQL/C# MVC: Order over multiple fields

Hello, I have records that have "FirstName" and "LastName" for individuals, and "CompanyName" for companies. Both records reside in one single table, and I know whether it's a company or an individual through an IsCompany Bit field in my table. The problem I face now is that I need to show those in one ordered grid, in the form: Last...

Multiple foreign keys to a single column

I'm defining a database for a customer/ order system where there are two highly distinct types of customers. Because they are so different having a single customer table would be very ugly (it'd be full of null columns as they are pointless for one type). Their orders though are in the same format. Is it possible to have a CustomerId co...

How to write prepared statements for SQLite in iPhone

How to write prepared statements for SQLite in iPhone? Is it possible to do SQL Injection in iPhone apps that use sqlite db? ...

SQL table not created when deploying a WAR file to Liferay

Hi, I've created a JSR-268 portlet for Liferay which uses services to interact with a database. I can deploy the portlet without problems or errors, but the table defined by the services is not created! I get no "table not found" error when I test the portlet. I get no errors at all! The table just isn't there in the database. I've fou...

Need a help in query?

I have a hierarchical tree table structure .How can i get the left tree and right tree . 1 a NULL 2 b 1 3 c 1 4 d 2 5 e 2 6 f 3 7 g 3 8 h 4 9 i 4 10 j 5 11 k 5 12 l 6 If i have the id of a ie 1 .how can i get the tree of b and c i am expecting the tree under b as 2 b 4 d 5 e 8 h 9 i 10 j 11 k ...

Variant use of the GROUP BY clause in TSQL

Imagine the following schema and sample data (SQL Server 2008): OriginatingObject ---------------------------------------------- ID 1 2 3 ValueSet ---------------------------------------------- ID OriginatingObjectID DateStamp 1 1 2009-05-21 10:41:43 2 1 2009-05-22 12:11:51 3 1 ...

SQL get single value inside existing query?

Hi :) I have a query that returns a bunch of rows. But using the same query i would like to: 1. get the total row count in the table 2. get the row number where a certian username is located Right now im doing like so: BEGIN DECLARE @startRowIndex INT; DECLARE @PageIndex INT; DECLARE @RowsPerPage INT; SET @PageIndex = 0; SET ...

Parameterized queries WITHOUT stored procedures?

Every sample I've seen uses stored procedures. I've adopted an old application written in Classic ASP that uses inline SQL. This is an obvious issue, so I need to convert it to safer code. The client does not wish that I use stored procedures in this application, so is there a way to run parameterized queries without stored procedures...

Checking existance of user id in separate table - T-SQL

Hi, I have a true dailyWTF on my hands here. We have an account sign up page that dishes out new account numbers in sequential order. The problem we have is two fold. For one we had customers that already have customer IDs sign up for another customer ID. To solve this we added a "Current Customer ID" field to the form so they could ent...

Side effects of not including CommandType for dynamic sql?

What pitfalls may I encounter by not setting the cmd.CommandType attribute when running a dynamic sql call? I can not use adovbs.inc, and using cmd.CommandType = 200 yields the error: ADODB.Command (0x800A0BB9) Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. So by commenti...

Too many columns design question

I have a design question. I have to store approx 100 different attributes in a table which should be searchable also. So each attribute will be stored in its own column. The value of each attribute will always be less than 200, so I decided to use TINYINT as data type for each attribute. Is it a good idea to create a table which will h...