I am a big fan of the design and functionality of Stack Overflow. So, I would like to create my own design for a commenting system based on the one used for this site! Questions: What sort of table structure should I use for storing comments? What SQL queries might I then use for fetching stored comments? ...
I have a query. I am developing a site that has a search engine. The search engine is the main function. It is a business directory site. A present I have basic search sql that searches the database using the "LIKE" keyword. My client has asked me to change the search function so instead of using the "Like" keyword they are after somet...
In just about any formally structured set of information, you start reading either from the start towards the end, or occasionally from the end towards the beginning (street addresses, for example.) But in SQL, especially SELECT queries, in order to properly understand its meaning you have to start in the middle, at the FROM clause. Th...
Well this is going to sound like a lame question, I know. This is probably one of the most obvious things to do, but I've been trying to come up with a good way of sorting entries in database. Image table looking like this: entry_id | entry_data ------------------------- 1 | data1 2 | data2 ... | ... n ...
I have a stored proc were I want to insert a GUID (user id) into a table in MS SQL but I keep getting an error about the hyphen '-' that is part of the guid value, here's my proc defined below; @userID uniqueidentifier, @bookID int, @dateReserved datetime, @status bit INSERT INTO Reservation(BookId, DateReserved, [Status], UserId) VAL...
I am making a split function in SQL Server 2005. I have already done it by using a while loop . But I am not happy with that. I want to do it using recursive function. I have already done it in C#. Now I am plotting the same in SQL SERVER 2005. But I am getting a compilation error. Here is my code ALTER FUNCTION [dbo].[fnSplit2] ( ...
What are the best practices for keeping referential integrity across multiple databases? since there's no built-in functions Or is it better to partition a single database? Update See kevin's example below. that is my situation. My inventory database has tables that reference the employeeId in the employees database. These databases a...
Hi guru We have a bunch of tables in Microsoft SQL so that each table has its own check constraint. At the end we have created a partition view that does a "UNION ALL" against these tables. Our attempts to insert into this partition view is quite successful. Select, update and delete all work properly. We want a similar functionality in...
In SQL, is there a way to enforce that only one column out of a group of columns has a value, and the others are null? Maybe a constraint or trigger? This type of thing might be for a lookup table, but are there any alternate table designs that might accomplish this better? For example: ID OtherTable1ID OtherTable2ID OtherTa...
Consider setting up a connection to a RDBMS, then yanking the network plug. (You'll get the same effect if the connection goes through a NAT gateway, and the gateway decides to purge that connection.) At that point the RDBMS server is waiting for a query, or whatever, which will never occur. And the TCP connection won't be closed by th...
Hi, I want to use the ROW_NUMBER() to get... To get the max(ROW_NUMBER()) --> Or i guess this would also be the count of all rows I tried doing: SELECT max(ROW_NUMBER() OVER(ORDER BY UserId)) FROM Users but it didn't seem to work... To get ROW_NUMBER() using a given piece of information, ie. if I have a name and I want to kno...
I'm creating a new table which is related to a Contacts table (where I store first name, last name etc). The table has: int Id (PK, Identity) int ContactId (FK) int Type (this identifies whether the following is an email, tel, fax etc.) nvarchar(40) [I need a name for this one] The nvarchar field is basically storing either ab...
I have the following scenario: 1) a Users table: int Id (PK, Identity) // more users table columns (firstname, etc.) 2) a UserGroups table: int Id (PK, Identity) // more usergroups table columns (title etc.) 3) a UserGroupMembership table: int Id int UserId (FK->Users.Id) int UserGroupId (FK->UserGroups.Id) an...
Is it possible to have something like ContactAddress.Contact in LINQ without create a foreign key relationship in SQL Server between those two (which would by Contact.Id <-> ContactAddress.ContactId)? Thanks :) ...
I have a very simple question about transactions. (in sql server 2000, but i guess it applies to general db. transactions). tblPrimaryKey PkId ----- 1 2 3 tblForeignKey Id ForeignKey ---- ----- 1 1 2 2 3 3 4 1 I have 2 tables, one referencing the other (tblForeingKey.ForeignKey references tblPrimaryKey.Pk...
hello, It seems that I can't alias a column and use it in the inner select in MySql. How do I do the following in MySql for example? SELECT NAME,ID AS M_ID FROM EMPLOYEES WHERE EXISTS (SELECT 1 FROM MANAGERS WHERE MANAGERID = M_ID) MySql would not recognize M_ID alias! EMPLOYEES (ID,NAME) MANAGERS (MANAGERID,...) Thanks ...
Been trying to get the following query working for a few hours now and am running out of ideas. Can anyone spot where I'm going wrong. Any pointers much appreciated. CalEvents = (List<CalEvent>)session.CreateSQLQuery("SELECT * FROM dbo.tb_calendar_calEvents INNER JOIN dbo.tb_calEvents ON (dbo.tb_calendar_calEvents.calEventID = dbo.tb_ca...
in the error log: CLSQL is doing: gcc -I /usr/local/include/mysql -I /usr/include/mysql -I /sw/include/mysql -I /opt/local/include/mysql -I /usr/local/mysql/include -fPIC -c clsql_mysql.c -o clsql_mysql.o and gets error: clsql_mysql.c:34:19: mysql.h: No such file or directory and a bunch of C errors because it doesn't include a heade...
We have a rather specific application that uses PostgreSQL 8.3 as a storage backend (using Python and psycopg2). The operations we perform to the important tables are in the majority of cases inserts or updates (rarely deletes or selects). For sanity reasons we have created our own Data Mapper-like layer that works reasonably well, but...
Hi All, I'm looking for a good way to maintain permissions on who can add data to a database in a C# app and SQL Server 2005. I need to explain though to make this clear. So let's take an example of this: I have two users Bob and Jim, both have been added to the SQL permissions so they have write access to the database. Now all access...