sql

SQL Query: joining two fields from two separate rows

I have the following two tables: USER FID UID VALUE 4 3 John 3 3 Doe 4 4 Jack 3 4 Russel Should be fairly clear that FID 3 = Surname, and FID 4 = Name. DATEDATA UID DATE 3 1234 4 4321 I want to join these two tables, so that I end up with something like this: UID DATE NAME SUR...

[My]SQL VARCHAR Size and Null-Termination

Disclaimer: I'm very new to SQL and databases in general. I need to create a field that will store a maximum of 32 characters of text data. Does "VARCHAR(32)" mean that I have exactly 32 characters for my data? Do I need to reserve an extra character for null-termination? I conducted a simple test and it seems that this is a WYSIWYG ...

How to design SQL tables to allow for multiple parent table options?

Looking into creating a series of tables in a reporting hierarchy and am more or less drawing a blank. While tables in this structure can only have one parent, how do I structure the fields so that they point to the right parent table? As you'll see in my example below, a row's parent table can differ. ARRANGEMENT ...

SQL flattening & dynamic query question

Firstly, I am not certain this is the best way to handle this AT ALL ... totally open to alternative solutions. Secondly, I feel like I'm missing the obvious ... but I'm still missing it so not to proud to ask! UPDATE: .NET 3.5 environment w/ SQL 2005, so things like dynamic linq possible, although I always tend to think of dynamic (bu...

SQL statement with LIKE

Hello I would like to select all records that have an underscore character in their 11th character, so i try this: SELECT * FROM "BOM_SUB_LEVEL" where TOP_CODE like '%%%%%%%%%%_%%%' but this doesnt work as expected, can someone help? thanks ...

The multi-part identifier "DB1..Students.studentnumber" could not be bound.

SELECT (SELECT Grade FROM DB2..Students WHERE DB2..Students.studentnumber=DB1..Students.studentnumber AND ISNULL(Students.Disable,'') != 'T' ) as test,* from DB2..Students WHERE studentnumber IN ( SELECT studentnumber FROM DB2..Students where AND ISNULL(studen...

Rebuilding large SQL indexes isn't changing the fragmentation percentage

I have a large database running on SQL Server 2005. I query the DMV sys.dm_db_index_physical_stats to get index fragmentation information. I was shocked to see avg_fragment_size_in_percent reporting a large amount of indexes with very bad fragmentation levels (99% for a lot of them). These are on tables with non-trivial page counts (...

SQL value does not populate ASP DropDownList control

I'm trying to populate a dropdown list control. 'Inputs' is the DB. Table is 'ApplicationName', and it has a random value assigned to it. For some reason, when I click the Test Submit button, nothing is appearing in the control (DropDownList1). protected void TestSubmit_ServerClick(object sender, EventArgs e) { // Initialize the da...

Select data from SQL DB per day

Hi I have a table with order information in an E-commerce store. Schema looks like this: [Orders] Id|SubTotal|TaxAmount|ShippingAmount|DateCreated This table does only contain data for every Order. So if a day goes by without any orders, no sales data is there for that day. I would like to select subtotal-per-day for the last 30 days...

How do I put data from multiple records into different columns?

My two tables are titled analyzed and analyzedCopy3. I'm trying to put information from analyzedCopy3 into multiple columns in analyzed. Sample data from analyzedCopy3: readings_miu_id OriginalCol ColRSSIz 110001366 Frederick Road -108 110001366 S...

How can I set up a simple calculated field in SQL Server?

I have a table with several account fields like this: MAIN_ACCT GROUP_ACCT SUB_ACCT I often need to combine them like this: SELECT MAIN_ACCT+'-'+GROUP_ACCT+'-'+SUB_ACCT FROM ACCOUNT_TABLE I'd like a calculated field that automatically does this, so I can just say: SELECT ACCT_NUMBER FROM ACCOUNT_TABLE What is the best way to do ...

SQL - Is there a query that will do "foreach A in table, if !B, insert B"?

I have a table with 2 columns: nid realm 1 domain_id 1 domain_site 2 domain_id 3 domain_id I want every entry to have 1 entry for domain id, and 1 for domain site. So I want to end up with: nid realm 1 domain_id 1 domain_site 2 domain_id 2 domain_site 3 domain_id 3 domain_site If I was doing this in PHP, I'd ju...

SQL Server Connection Problems

I have one server out of three which cannot connect to our sql server 2000 using the FQDN but can connect using the non FQDN. None of the the other servers are having this problem. This problem occurs under ADO connections to a SQL Server 2000 database. The database has encryption turned on. I don't want to turn it off. Does anyon...

C# SQL Top as parameter

Trying to parameterize the value of TOP in my sql statement. SELECT TOP @topparam * from table1 command.Parameters.Add("@topparam",SqlDbType.VarChar, 10).Value = somevalue.ToString(); This doesn't seem to work. Anyone have any suggestions? Just to clarify, I don't want to use stored procedures. ...

Want 'Exists' in a Where clause to be used only when passed parameter has a value

I have a Select like the one below in a stored procedure (shortened for brevity). @param is a parameter to the stored procedure which can be NULL. SELECT name FROM Table1 WHERE EXISTS (select .... from table2 Where param = @param AND ... AND ...) AND ... AND ... I would like the EXISTS statement (the part in bold) to be used only whe...

Using VBA to export all MS Access SQL queries to text files

I have to document an MS Access database with many many macros queries, etc. I wish to use code to extract each SQL query to a file which is named the same as the query, eg if a query is named q_warehouse_issues then i wish to extract the SQL to a file named q_warehouse_issues.sql I DO NOT WISH TO EXPORT THE QUERY RESULT SET, JUST THE S...

SubSonic 2.2 Left join error

I know this may have been answered but SubSonic 2.2 causes an error in the SQL provider when trying to do a Left join (Left inner join to subsonic) instead of creating SQl like SELECT * FROM table1 LEFT JOIN table 2 ON table1.id = table2.id it creates: SELECT * FROM table1 LEFT INNER JOIN table 2 ON table1.id = table2.id and this ...

efficient query for many to many table

Hi all, I'm writing a unit test of my mock-up repositoy. While the real repository lays on the linq2sql, the mock-up only use list of linq2sql objects. When it comes to the many to many tables, the efficency is rather low. Here is the senario. I have a many2many table which is tablePersonInCommunity, stores a CommunityID and a PersonI...

Finding Duplicate Entries in a table

Hello Experts, I have a table with the following fields in Oracle 10g. TABLE_1 account_no | tracking_id | trans_amount Each account_no can have multiple tracking Id's and transaction amounts. How do i query out the duplicate entries of account_no where tracking lies between 1 and 1000, and the corresponding trans_amount ? Many than...

SQL-Time difference

2 Tables with the below model: Id: long unique; timestamp:long; price:double The data in both tables are identical except timestamp. Timestamp is given as unix_time in ms. Quest: how many pairs have a bigger time difference than 1000ms ? ...