sql

SQL aggregation question

I have three tables: unmatched_purchases table: unmatched_purchases_id --primary key purchases_id --foreign key to events table location_id --which store purchase_date item_id --item purchased purchases table: purchases_id --primary key location_id --which store customer_id credit_card_transactions: transaction_id --primary key trans_...

Is it OK not to use a Primary Key When I don't Need one

If I don't need a primary key should I not add one to the database? ...

MySQL: Rewrite MSSQL with correlated subquery in FROM clause

We have a table that contains website page views, like: time | page_id ----------|----------------------------- 1256645862| pageA 1256645889| pageB 1256647199| pageA 1256647198| pageA 1256647300| pageB 1257863235| pageA 1257863236| pageC In our production table, there is currently about 40K rows. We want to generate, for each da...

trouble creating a sql statement select statement reference same table twice

Ok, here is setup. Note the code below is SIMPLIFIED version. PHP 5.2 ENGINE: MyISAM table quotes q table users u FROM quotes q LEFT JOIN users u ON q.qid = u.uid Soo... the quotes table references a user (from users table) (the owner of quote) but quotes table ALSO has a field called createdby (the user who created the quot...

What characters ARE allowed when querying a mysql database?

I have a textarea in a form, when I enter special characters in it, I get an error in mysql. (when submitting the form to a php-file which does the work of inserting into mysql) I need to know exactly what characters that aren't allowed, or easier would be, exactly what characters thar ARE allowed, so that I could validate the textarea ...

Optimize SQL query for canceled orders

Here is a subset of my tables: orders: - order_id - customer_id order_products: - order_id - order_product_id (unique key) - canceled I want to select all orders (order_id) for a given customer(customer_id), where ALL of the products in the order are canceled, not just some of the products. Is there a more elegantly or efficient...

How to make a list of unique items from a column w/ repeats in PHP SQL

Say I have a table that has a column which goes Column B apple apple apple orange apple orange orange grapes grapes mango mango orange And I want to query it in such a way that I get a list like so apple orange grapes mango How do I do this in PHP SQL? Much thanks. ...

MySQL: Get all records that have an id for one year but not another

Hi, I have a table: HOST_ID | ContractYear 1 | 2008 2 | 2008 3 | 2008 1 | 2009 4 | 2009 What I need is a query that will tell me all the HOST_ID's that are in 2009 that are not in 2008. The answer in the example above is HOST_ID=4. Thanks. ...

how to write an if condition case in stored procedure

i have an table where i am passing 5 paramter table name=" image" parameter @imageID, @imageName, @imageDirectory, @imageResource, from the front end [from page that is from textbox control] if they didnot send any values to stored procedue then i should do an simple Query condition1: select * From image condi...

LINQ to SQL, ExecuteQuery, etc

This might all be a bit subjective: Our organization has made a strong attempt to adopt LINQ to SQL as our primary data access method and for the most part this works well. (Let’s leave the EF out of the discussion.) Some of our developers find LINQ difficult and migrate back to traditional raw SQL via ExecuteQuery. We also utilize Ope...

efficiency of SQL 'LIKE' statement with large number of clauses

I need to extract information from a text field which can contain one of many values. The SQL looks like: SELECT fieldname FROM table WHERE bigtextfield LIKE '%val1%' OR bigtextfield LIKE '%val2%' OR bigtextfield LIKE '%val3%' . . . OR bigtextfield LIKE '%valn%' My question is: how efficient is this when the number o...

Fetch recently watched videos?

I am using MySQL. I have the following table structure: tablename: Videos videoId title description 1 Video title 1 Some description text1 2 Video title 2 Some description text2 3 Video title 3 Some description text3 4 Video title 4 Some description text4 5 Vi...

Best way to incorporate legacy data

I am working on a price list management program for my business in C# (Prototype is in Win Forms but am thinking of using WPF for the final ap as a MVVM learning exercise). Our EMS system is based on a COBOL back end and will remain that way for at least 3 years so I cannot really access it's data directly. I want to pull data from them...

Get the last children from database

My situation: Table A ( ID Parent_Id TimeStamp ) The root has Parent_Id null and children has Id of its father. I simple want to get all LAST children of every Table A. Father and Children I don't want. (except last one). Is it possible to build a SQL to get this? PS: I'm on sql anywhere 11. Maybe an ansi sql can solve this, i'm no...

SQL: Deleting duplicate records in SQL Server

hi! I have an sql server database, that I pre-loaded with a ton of rows of data. Unfortunately, there is no primary key in the database, and there is now duplicate information in the table. I'm not concerned about there not being a primary key, but i am concerned about there being duplicates in the database... Any thoughts? (Forgive...

Procedurally transform subquery into join

Is there a generalized procedure or algorithm for transforming a SQL subquery into a join, or vice versa? That is, is there a set of typographic operations that can be applied to a syntactically correct SQL query statement containing a subquery that results in a functionally equivalent statement without a subquery? If so, what are they (...

SQL Delete When column a and column b does not exist.

Ok, so you have something like this working. You Insert into a table from a tmp table, where the Equipment Number and the Account Number are missing... Insert INTO ClientEquipments( SUB_ACCT_NO_SBB, EquipmentDate, EquipmentText, EquipmentNumber) Select replace(a.SUB_ACCT_NO_SBB,'"','') as SUB_ACCT_NO_SBB, getdat...

PostgreSQL and Point in Time Recovery

I was reading through this: http://www.postgresql.org/about/ And I saw this: An enterprise class database, PostgreSQL boasts sophisticated features such as ... point in time recovery I need some light shed on this subject and its features and examples of it in action or share their own performance experiences? ...

Troubles formatting if statements in SQL

I've created the following sql statement, every thing in $() is a variable (comes from the module we're using on our site so $(varname) is the right format in this case, not @varname. It keeps telling me there's an error near THEN... how am I supposed to format this properly? IF ($(Shiptobilling) = 'yes') THEN BEGIN CardOrd...

SQL Server - bulk delete (truncate vs delete)

Hello folks, We have a table with a 150+million records. We need to clear/delete all rows. Delete operation would take forever due to it writing to the t-logs and we cannot change our recovery model for the whole DB. We have tested the truncate table option. What we realized that truncate deallocates pages from the table, and if I am...