Getting all the child records
Hi, I am using SQL Query and below are the tables. Organization OrgID Name RAOID SubGroupID 1 Org RAO1 2 NULL 2 Org RAO2 NULL 2 3 Org Sub Group1 3 NULL 4 Org RAO3 NULL 1 5 Org RAO4 1 NULL 6 Org...
sql
Wanted: Good examples of Scala database persistence
I'm would like to use Scala to persist data to a relational database, so what I am looking for are examples of CRUD operations using Scala. I would like to code on a lower level of abstraction than an ORM like Hibernate/Toplink (read:JDBC), but between us, I would like to see examples of all types. Thanks folks. ...
SQL aggregate functions using MIN and AVG...
How can I omit Zeros in my data? For example using the MIN function? I would like the Minimum value except a 0... How can I get the next largest? MIN(availables.price) Also is there a generally way to skip over 0s in the same way if I'm using the AVG function? The problem is the table I've inherited doesn't use NULL values but has...
Naming convention of foreign keys
Hello there When making relations between tables (in mysql), I have encountered a naming dilemma. For example, if I was creating a site where a project could be created by multiple users and also read by multiple users, to link a questions and user tables, I would potentially need two tables. **project_authors** questionId userId a...
SQL Server Table Synonyms with Indexes
I have multiple databases on a single instance of SQL Server 2005. I've created a synonym on one database to access a table on another database and when writing my queries, I'd like to utilize a specific index, however, when evaluating the execution plan, it doesn't appear to use it. If I write the query to access the database explicit...
Are there downsides to using prepared statements?
I've been reading a lot about prepared statements and in everything I've read, no one talks about the downsides of using them. Therefore, I'm wondering if there are any "there be dragons" spots that people tend to overlook? ...
Help building a SQL query from multiple tables
Given the following tables, how might I build a SQL query that includes a list of all the items from the "items" table, and a column for each color from the "colors" table that, for each item listed, indicates what colors the item has a relationship with. If that is unclear at all, please let me know what additional information will hel...
SQL: count number of distinct values in every column
I need a query that will return a table where each column is the count of distinct values in the columns of another table. I know how to count the distinct values in one column: select count(distinct columnA) from table1; I suppose that I could just make this a really long select clause: select count(distinct columnA), count(distinct...
entity framework entity sql vs linq to entities
what's the purpose of entity sql, i mean if you have linq to entities why would you need to write queries in string, are there any performance reasons or something ? ...
In SQL in a "group by" expression: how to get the string that occurs most often in a group?
Assume we have the following table: Id A B 1 10 ABC 2 10 ABC 3 10 FFF 4 20 HHH As result of a "group by A" expression I want to have the value of the B-Column that occurs most often: select A, mostoften(B) from table group by A; A mostoften(B) 10 ABC 20 HHH How do I achieve this in Oracle 10g? Rema...
faster way to do this select query
Say Employee has three columns FirstName, LastName and ID. The query should first search for a name in firstname, only if its not found search for last name. so select *from Employee where FirstName = 'test%' or lastname='test'%'. wont' work. The query below will work. select FirstName, LastName, ID from EMPLOYEE WHERE LastName ...
Ruby on Rails SQL Optimizations
Lets assume: Network has_many Channels Network has_many Nicknames Channel has_many Messages Channel belongs_to Network Nickname has_many Messages Nickname belongs_to Network Additionally: Channel has_many Nicknames, :through => :messages Nicknamehas_many Channel, :through => :messages I have this method here that logs messages. Ver...
search for a key word in all the Stored Proc's for a given DB?
Hello All! How to do a global search for a key word in all the Stored Proc's for a given DB? I used the following but I am unable to get the desired results... SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'loadStatus' COLLATE SQL_Latin1_General_CP1_CI_AS ) SELECT name FROM ...
What are Some need to Know SQL Injection Techniques that Hackers use
I am tightening down my web application and I am on SQL right now. I already have sql parameters, doubling apostrophe, stripping javascript and encoding html. Is there other things I need to worry about besides the things above? ...
SQL Image DataType - C# Display Output
I have an image data type in my table. When I query using SQL Server Management Studio, I get the following in my results window, both grid and text. 0x255044462D312E320D0A0D0A332030206F[Truncated for Length] I am attempting to replicate this in a little c# program. However, when I use either SqlCommand or LINQ I get the output: JVB...
Help With SQL - Combining Two Rows Into One Row
Hello Everyone, I have an interesting SQL problem that I need help with. Here is the sample dataset: Warehouse DateStamp TimeStamp ItemNumber ID A 8/1/2009 10001 abc 1 B 8/1/2009 10002 abc 1 A 8/3/2009 12144 qrs 5 C 8/3/2009 12143 ...
How to use raw normal sql in ASP.NET MVC without linq?
Hey Everyone, I'm trying to write some code using pure SQL using ASP.NET MVC. I assume I should be building a model, and sticking to the MVC pattern. Any suggestions for good practice would be highly appreciated, and examples very useful too. For example I'm not sure if I should be splitting this code off from my main repository's, an...
if I use Nhibernate can I Switch between databases and not change the Nhibernate code?
What I want to do is convert my whole application to nhibernate from ADO.NET and test it using different databases. i know nhibernate supports many different databases so can i switch between different databases without changing any of my application or nhibernate code? ...
Is it Possible to Migrate a Database from SQL Server to PostGresQL using Nhibernate?
Basically, I just want to know if its possible to use Nhibernate to migrate between databases? ...
Cant make field unique!
Can you not have more than 2 unique fields in a table or am i doing something wrong here? I have 1 unique key for username and i want it for email too but i get #1062 - Duplicate entry '' for key 'email' alter table users add unique (email) Tbl: `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(255) NOT NULL, `...