sql

Problem with joining db tables

I have problem when joining tables (left join) table1: id1 amt1 1 100 2 200 3 300 table2: id2 amt2 1 150 2 250 2 350 my Query: select id1,amt1,id2,amt2 from table1 left join table2 on table2.id1=table1.id2 My supposed o/p is: id1 amt1 id2 amt2 row1: 1 100 1 150 row2: 2 200 2 250 row3: 2...

SQL 2005 Query Optimisation

Hi everyone I have a SQL 2005 table consisting of around 10million records (dbo.Logs). I have another table, dbo.Rollup that matches distinct dbo.Logs.URL to a FileId column in a third table, dbo.Files. The dbo.Rollup table forms the basis of various aggregate reports we run at a later stage. Suffice to say for now, the problem I am h...

WPF Data Bound Control, Detect SQL Table change

Im having a moment of stupidness. I have a Treeview control in WPF, that is displaying a list of Quotes, ie Quote#, Quote Name eg "Q#1000" "Server Quote" This treeview is bound to a collection of objects public ObservableCollection quoteitems = new ObservableCollection(); Which are loaded from a SQL database via an SQLDataReader whic...

MySQL Question about scheduling

I want to ask about scheduling, is there any MySQL command for scheduling. For example, I want to insert a data into the DB. Can I write a sql statement to hold it for some time, or maybe I insert this data in some scheduled time, for example, tomorrow 8:00 a.m. I know these can be done by programming, but I want to do it in SQL command...

Help with sql query

I am looking for some help in creating the correct SQL for this scenario... Two table Model and Serials simplified Model ----- CODE | MAKE | MODEL Serials ------- CODE (FK) | SERIAL I want to retrieve the CODE, MAKE AND SERIAL where the MAKE = 'some value' I only want the distinct CODES though... ...

Migrating database ms access 2003 to 2007

I'm very new to Access and I don't have any experience. At work, I was ask to research about migration of MS Access 2003 databases to 2007. I have read on the Internet that usually if you open .mdb files in Access 2007 it will normally work. But in my case I'm getting an error: Action Failed Now I'm not sure if I'm getting the co...

how to create Delphi 4 structure to map column names in XLS to column names in SQL

I have an EXCEL sheet.it has various form-fields that are named as “smrBgm133GallonsGross/ smrBgm167GallonsGross “ and “smrEgm133GallonsGross/ smrEgm167GallonsGross “ I added to the XCEL sheets 2 new form fields named smrBgm167GrosGalnsDA/smrEgm167GrosGalnsDA The above additons I made in EXCEL should ACTUAL...

Collation conflict in stored procedure while assigning a variable

We are trying to create a stored procedure, however we run into the following error message: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. This error occurs at line 33, which reads as follows: SET @MINTIME = (SELECT CONVERT(varchar,DATEADD(MONTH,-1,G...

mysql db as master, access mdb's updating from it - how?

I am helping a friend to streamline his little manufacturing business db. Currently, they have An address db bookkeeping db order mgmt db All the three are mdb files. They can't pay me really, so I am doing good will work. Thus, just doing the barely needed. It would be ideal to merge order mgmt and address, but that's maybe for l...

How can I get the IDs of the rows that make up a GROUP BY ... HAVING query?

I have the following query that looks for transactions that cancel themselves out from the same customer (Some transactions are negative). SELECT c, ABS(r) magnitude, SUM(r) total, COUNT(*) num FROM table GROUP BY c, magnitude HAVING num > 1 AND total = 0 ORDER BY total The result of this query is the customer id, the magnitude of the...

Can this be done in SQL 2008?

Given this data set: SummaryID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 25 1 2 3 4 5 6 7 6 5 4 3 2 1 2 3 25 1 2 3 4 5 6 7 6 5 4 3 2 1 2 3 25 ...

Mysql field name within regular expression

I have the following table Table bots{ ip_address varchar(15), bot_name varchar(32) } Given that some bots have static ips and others won't, the table has entries such as 192.168.0 and 192.168.1.15 Now I have to see if a given ip belongs to a bot. I was thinking something along these lines SELECT bot_name FROM bots WHERE __i...

Diagnosing an OLEDB exception when Quering Excel 2010

To query an excel sheet via SQL, I used to use either: Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;""" or Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + strPath + ";Extended Properties=""Ex...

SQL string replacement workaround

I'm working in a modified version of Sybase 10 that doesn't have the stuff, replace or str_replace functions. I need to replace part of string in a number of records. Are there any creative ways to program around this, so I don't have to do them all manually? Example: UPDATE status SET description = replace(description,'abc','def') W...

What does it mean to say that "the where clause of select statements can perform >, >=, <, <= operations on one column only"?

According to the Wikipedia article on Google App Engine: The where clause of select statements can perform >, >=, <, <= operations on one column only. Therefore, only simple where clauses can be constructed. What does this mean? ...

Best way to flatten/denormalize SQL lookup tables?

I have a bunch of tables like this: Lookup_HealthCheckupRisks ------------ ID Name 1 Anemia 2 Anorexic 3 Bulemic 4 Depression ... 122 Syphilis PatientRisksOnCheckup ------------------ ID CheckupID RiskID 1 11 2 2 11 3 3 12 1 4 14 1 5 14 3 ... But I need a flattened version, like th...

Asp.net Page Counter : Store number in MySql Database

I've been searching the web for some good page hit counters. I'd like to create a page hit counter asp.net page that keeps its values stored in a MySql Database under a row called 'PageCounter'. Any ideas would be appreciated. ...

How to query multiple SUMs into multiple columns of the same SQL query in iReport?

Is this efficient? How could it be improved? I was trying to get data from one set of columns into two sets of columns according to a condition. All the methods I tried ended up with multiple rows. This is very similar to this other question but a bit more complicated. Here's what I did: (Because this is for iReport/JasperReports, it...

Changing INT to BigInt

I have a warehouse table with 16 tons of data in it. I have a few Integer columns in it. We have to cast these into BIGINT for every query we write, because the SUM is too large to fit in an INT. We now have a new datamart under development. So we thought, why not change all these columns into BIGINT and we have less to worry for the ne...

MySQL query DATE comparison

I have a problem with my query. Its giving me the correct result for some cases and for some it does not, so if anyone could tell me what to change it'd be amazing cause I'm stuck with it for some time now. Here is my table data: Table reservation_logs: log_id int(15) auto_increment Primary Unique room_log int(10) dt_...