sql

Column Naming Advice

I know there are other naming convention discussions, but the ones I saw were all general guidelines, I'm asking more for advice on good practice regarding my specific case. However, if there is another question that directly covers my case, please link it and I will gladly delete this question. I have a database for modeling electronic...

Choice of MySQL table type for non-critical webapp data (MyISAM vs. InnoDB)

Consider this scenario with the following assumptions: The database is used for a non-critical webapp. Query speed is of vital importance. The read/write patterns are roughly >95 % reads and <5 % writes. The database is backuped up daily using mysqldump. The is no need for transactions or advanced crash recovery. If the database crash...

Outer Cross Join?

I'm trying to avoid using queries in while loops. Therefore I've come to the conclusion that I should use a cross join. Take this example: SELECT * FROM products CROSS JOIN images USING (imgId) CROSS JOIN productcolors ON colorsId = colorId WHERE productId = 1 should return two rows (table structure below): imgId | productId | colo...

Merging databases how to handle duplicate PK's

We have 3 databases that are physically separated by region, one in LA, SF and NY. All the databases share the same schema but contain data specific to their region. We're looking to merge these databases into one and mirror it. We need to preserve the data for each region but merge them into one db. This presents quite a few issues for ...

pass null parameter to SQL server query

How can I pass a null parameter to a SQL server query. I have a simple table with a nullable int column. If I pass a .NET null value, I get a sql error. If I pass DBNull.Value, no row matches the filter. Is there a simple way to do this without using ISNULL. OleDbConnection connection = new OleDbConnection(); connection.Connecti...

How do You Get a Specific Value From a System.Data.DataTable Object?

I'm a low-level algorithm programmer, and databases are not really my thing - so this'll be a n00b question if ever there was one. I'm running a simple SELECT query through our development team's DAO. The DAO returns a System.Data.DataTable object containing the results of the query. This is all working fine so far. The problem I h...

How to properly construct SQL subquery in this code?

When I execute the following code, I'm getting results such as: ID column1 column2 34 NULL NULL 34 Org13 Org13 36 NULL NULL 36 NULL Org2 36 Org4 NULL 41 NULL NULL 41 NULL Org5 41 Org3 NULL I want my results to look like: ID column1 column2 34 Org13 Org13 36 Org4 Org2 41 Org3 Org5 I've got two tables: Table1 and Ta...

Is mixing ADO.NET and LINQ-TO-SQL bad? My data layer isn't working...

UPDATE As Mathias notes below, this exact problem has been reported and resolved here: ASP.NET-MVC (IIS6) Error on high traffic: Specified cast is not valid ORIGINAL POST This may be too specific a debugging issue to be posted here, but I'm posting it anyway in the hopes that it produces a solution that others find useful. I have a w...

SQL delete from related tables

I am trying to delete all records that match a quiz id from the question table and the relationship table. The query works for a select statement, but won't allow the same statement to delete. @quizId is a quiz Id value I pass into my stored procedure. Does anyone know how to delete from both tables using one statement? Is it possib...

SQL - Difference between these Joins?

I should probably know this by now, but what, if any is the difference between the two statements below? The nested join: SELECT t1.* FROM table1 t1 INNER JOIN table2 t2 LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID ON t2.table2_ID = t1.table1_ID The more traditional join: SELECT t1.* FROM table1 t1...

How to implement this Query in Hibernate?

I'm converting a legacy iBatis implementation to Hibernate, and for backwards compatibility purposes need to present counts of an object's collections rather than the collections themselves. The original query was: select A.*, ( select count(*) from B where B.A_id = A.id ) as B_count from A; and b_count would be presented in the resp...

What is the best practice for accomodating both a one-to-many and a one-to-all (or one-to-*) relationship?

Just curious what is the best practice for solving the following issue. I have institutions, accounts and users tables. Institutions and accounts have a one-to-many relationship. Institutions and users have a one-to-many relationship. Users and accounts have a many-to-many relationship or alternatively they could have a many-to-al...

Inserting articles straight into MediaWiki database

I need a way to insert new articles straight into my MediaWiki database without damaging the wiki installation. I'm guessing if I knew what tables/attributes MediaWiki inserts to when creating a new article then I could fill them in myself. Does anyone know a better way or have any suggestions? ...

Concatenating record values in database (MS Access) or in server side code (ASP.NET)

Hi, sorry to bother you people again. I've searched all over the internet but I can't find the solution to my problem. I have two tables in Access and the output is like this: MATH 5 ENGLISH 3 ENGLISH 2 PHYSICS 5 MATH 1 MATH 3 I want it to be: MATH 5, 1, 3 ENGLISH 3, 2 PHYSICS 5 How can I accomplish this? It tried playing with S...

Select a dummy column with a dummy value in SQL?

I have a table with the following Table1 col1 col2 ------------ 1 A 2 B 3 C 0 D Result col1 col2 col3 ------------------ 0 D ABC I am not sure how to go about writing the query , col1 and col2 can be selected by this select col1, col2 from Table1 where col1 = 0; How should I go about addi...

Insert one row for each key

Let's say I have two tables lnglngdef: lngId | lngName 1 Korean 2 Spanish lngnottranslated: lngId | engDef 1 Hello 2 Hello 1 Hi 2 Hi Now I want to insert a new "engDef" with the value 'Welcome' in the lngnottranslated for each entry in the lnglngdef table. In this case that's two new rows in the ln...

Is SQL outdated?

A friend of mine who is a computer scientist told me he thinks SQL is a thing of the past. I used SQL for years and think the language is totally relevant when dealing with database specific tasks. My friend believes the new frameworks (e.g.: django, etc) that are coming out are making things even more generic, making SQL an outdated d...

How to extract all fields from create sql statment using .Net?

I am creating a windows application using VB.Net and this application will take a SQl create .sql file as a parameter and will return all fields and their data types inside in a list or array. Example: USE [MyDB] GO /****** Object: Table [dbo].[User] Script Date: 07/07/2009 10:16:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFI...

HABTM finds with "AND" joins, NOT "OR"

I have two models, associated with a HABTM (actually using has_many :through on both ends, along with a join table). I need to retrieve all ModelAs that is associated with BOTH of two ModelBs. I do NOT want all ModelAs for ModelB_1 concatenated with all ModelAs for ModelB_2. I literally want all ModelAs that are associated with BOTH Mode...

MySQL 5: Does it matter what order my GROUP BY fields are in?

Peeps, I have a few aggregate/calculated fields in my MySQL query. My GROUP BY clause is dynamically generated, depending on what options a user selects in a web form. Curious if the order of fields listed in the GROUP BY clause can have any impact on the calculations (things like SUMs, AVERAGEs, etc) Thanks! ...