sql

Using SQL GROUP BY to see when a field changes

I have a sql query that groups by ID and TYPE that returns the following result set ID DATE TYPE 101667712, 2008-10-31, W9 101667712, 2009-01-05, W9 101667712, 2009-01-05, W9 101667712, 2009-01-05, W9 101667712, 2009-01-05, W9 101667712, 2009-01-05, W9 101667712, 2009-01-05, W9 101667712, 2009-01-05, W8 101667712, 2009-01...

Help with writing MySQL query or queries

I'm trying to write a function in PHP who's job it is to check a date and time against these tables and determine if it's possible to schedule an appointment. Tables: locations --- id appointment_types --- id length_of_time (not sure what to store this as, but at the moment I would like it to be precise to the quarter hour) partners ...

PDO Uncaught Exception

This error appears when executing the code below: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1065 Query was empty' in /home/content/63/6563663/html/inventory/pg.php:20 Stack trace: #0 /home/content/63/6563663/html/inventory/pg.php(20): PDOStatement->execute() ...

Cursors vs. While loop - SQLServer

Say I have a bunch of rows in a DB (SQLServer 2008 in this case) that can be used to create equations. ----------------------------------------------------- OperationID | EquationID | Operation | Amount | Order ----------------------------------------------------- 1 | 1 | + | 12 | 1 2 | ...

Current way to setup ASP.NET site with demo data?

I need to set up ASP.NET and connect it to some demo (northwind) data. Considering all the versions of SQL, what is the most current /efficient way to get a database into a project and do a select against it? This question arose once we discovered we could download a MDB and a MDF format of northwind... ...

SQL query for sales report (Drupal w/ Ubercart)

I've created a sales report with filters for product type, brand and gender. The filters are saved as terms within a Drupal 6 DB structure where vid represents a specific vocabulary id for product type / brand / gender. The report should display a record per product variant (model) with sales data. SELECT op.title, op.nid, op.model, SU...

SQL Query Assistance

Hi all, I'm having a complete brain fart moment so i figured i'd ask away here. I have 3 tables that look like this Equipment Table EquipmentID | LocationID ------------------------- 1 | 2 2 | 2 3 | 1 4 | 2 5 | 3 6 | 3 Location Table LocationID | LocationName ------------...

SQL Select Statement: How to get all values from Table 'M' and any correlating values from Table 'T' or 0 if nothing.

I am trying to create a query within a commercial ERP system that uses Access as its database, E2 Shop System. So it just runs the query through Access. I want the query to tell me "how much time was logged on every machine on a prompted date, even if it is 0". I have figured out how to get it to output how much time is on a machine...

How to change id in existing MySQL tables with data to autoincrement from last highest id?

I have just managed to migrate a pretty big database from SQL Server to MySQL. I didn't set the primary keys to auto increment during the migration because the tables have relationships based on ids as primary keys which are foreign keys in another table. Now for adding new records I want to alter the primary key 'id' columns in all tab...

MySQL Match...Against vs. nulls?

I have a query that uses left joins like so: SELECT a.id, a.username, a.date_created, a.contact_id, a.role_id, c.first_name, c.middle_initial, c.last_name, c.email, r.role FROM accounts AS a LEFT JOIN contacts AS c ON a.contact_id = c.id LEFT JOIN roles AS r ON a.role_id = r.id WHERE ( MATCH (a.username, c.first_name, c.middle_initial...

Using a variable in a SQL Server 2005 stored procedure with XQuery

I'm working with the following XML <AEDControl ControlGU="17fed98c-8128-4c6b-9b50-3dbe73889b9d" ControlLabel="Posting Status" TypeGU="6b4d08b1-6340-450c-beae-517b7d84e717" ControlDescription=""> <Elements> <Element ElementGU="2da346d1-2e05-4aa3-9bae-5aa9b3b75d5c" Label="Active" ...

SQL Server 2008: copying the contents of all tables from one database into another database

I have two databases with THE SAME schema. I need to copy the contents of all the tables from one database into the other database. What is the best way to do this>? There are about 200 tables. I have ssms. ...

What is wrong with this Microsoft Access query?

Consider this Access query: SELECT prod_Parts.ID, prod_Parts.Number, prod_Parts.Revision, prod_Parts.Description FROM prod_JT_Shipping INNER JOIN (prod_JobTraveller INNER JOIN prod_Parts ON prod_JobTraveller.PartID = prod_Parts.ID) ON prod_JT_Shipping.JT_ID=prod_JobTraveller.ID; Also this: SELECT p...

mysql and php - export specific row

Hello I have a test table with name mytable and with following data id name surname ================== 1 sotos val 2 john rik 3 peter ask How can id export for example the second row in mysql using php knowing the id? ...

Violation of Unique Key constraint

I am using grails, and I am getting the following when trying to create a new EducationType in my controller 2010-10-26 17:14:49,405 [http-8080-1] ERROR util.JDBCExceptionReporter - Violation of UNIQUE KEY constraint 'UQ__educat ion_type__0519C6AF'. Cannot insert duplicate key in object 'dbo.education_type'. 2010-10-26 17:14:49,409 [ht...

How can I store and retrieve data from a checkboxlist?

SQL Tables Listing ID, Title..... ListingType ID, Name ListingMatrix ListingID, ListingTypeID Basically a listing can be more than 1 type and I want that to be able to be shown using the ListingMatrix table. However, I'm having a lot of issues populating the checkboxlist because I have it being sorted by Title to keep it user frie...

Changes to a Query Based on Year

select datepart(month,s1.Timeperiod) as monthofaum, datepart(YEAR,s1.Timeperiod) as Yearofaum, ISNULL(s2.endingAum,0) as Starting_Aum, s1.endingAum as Ending_Aum from #temp_1 s1 left outer join (select * from #temp_1)s2 on month(s1.Timeperiod) = dateadd(D,1,month(s2.Timeperiod)) This w...

PHP Output of SQL Query

As of now I have no errors in my program, but I need the primary key for one of the tables for a relation for the following Query. but instead of getting a actual number the value the query is sending back is Resource id #4 Here is my Code: (The query that I'm having issues with is the $sql_branch, is there a function to change the resu...

Create Index on partial CHAR Column

I have a CHAR(250) column being used as a foreign key to a varchar(24) column. In MySQL I recall that I could create an index specifying column(24) in order to create an index on the leftmost 24 characters. This doesn't appear to be possible on MS SQL Server. My question is this: Is it possible to use an indexed view on SQL Server 20...

Not exists in a update statement takes more time

In MySQL, update table1 t1 set column1 = 100, column2 = 200 where column3 > 500 and not exists(select * from table2 where column1 = t1.column1); This query is too time consuming to execute, do have any other faster way of rewriting. for table2 ID is a primary column so i guess ...