So I'm trying to build a simple forum. It'll be a list of topics in descending order by the date of either the topic (if no replies) or latest reply. Here's the DB structure: Topics id, subject, date, poster Posts id, topic_id, message, date, poster The forum itself will consist of an HTML table with the following headers: Topic | Las...
I plan to delete an entire table with over 930,000 rows of data. Which is the best way to do it without increasing the Log size or increasing the DB size. I am on a live site and my hosting has given me 150 MB of space..I am already using 125 MB and hence need to be careful of the DB size since increase in log will increase the size of ...
Hi, Say I have this table schema. ID AccNo Amount Say I have this data ID AccNo Amount 1 1020 100.00 2 2040 50.00 How do I write a TSQL update query to update AccNo 1020 amount column with the amount from 2040?? Malcolm ...
So, I have a field in my users table named last_active which updates every time a user reloads a page. It's stored in unix timestamp. I would like to output it like this: Last online: 4 d 18 h 19 m ago How would one do that? Can you do it with php's date()? Thank you. ...
I have a small site where I want to get Related Videos on basis of Tags... what could be the best MS SQL 2005 query to get related Videos on basis of Tags. If you can give LINQ query that would be awsome. Here is Database Schema: CREATE TABLE Videos (VideoID bigint not null , Title varchar(100) NULL, Tags varchar(MAX) NUL...
This is for an assignment... I am to Write SQL out to display Customer's Last name, order date, product ID, fabric of the product, quantity ordered, and unit price. This must be a 4-table join and must user INNERJOIN even if where can be used. These are the tables involved. Cus (CID, Last, First, Phone) Orders (OrdID, OrdDate, ShipD...
Hello, I have a procedure and i would like that only one user could execute this procedure at same time. How i can do it? A lot of thanks! Regards Marc ...
Final query that I have to do and I'm getting an error stating that I have not specified OrdID's table: Here's what I have: SELECT Last, OrderLine.OrdID, OrdDate, SUM(Price*Qty) FROM ((Cus INNER JOIN Orders ON Cus.CID=Orders.CID) INNER JOIN OrderLine ON Orders.OrdID=OrderLine.OrdID) INNER JOIN ProdFabric ON OrderLine.PrID=ProdFabric...
Hey everyone, I'm designing a database for an organization that has multiple "types" of users. At first, I created only one user table. However, while all users share some common information (first name, last name, username, password, etc.), each user type requires one or two additional fields that are not applicable to all users. While...
Suppose I have a junction table EmployeeId DeptId --------- ------ 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 4 1 5 2 5 3 6 1 6 2 6 3 So 1 employee can work in many departments My problem is to find which employee works in multiple departments? e.g. If I want to search an employee who works for dep...
Hello, my application has to deal with calendar information (incl. single occurrence, recurrence, etc.). In order to easily interface with other applications I thought that it would be a good idea to create my database schema based on the iCal format (fields, relationships, constraints) directly so that I get iCal compatible objects via ...
Hi, This SQL query does not execute. Can someone give the correct syntax? UPDATE PayrollTotals ptm SET PTAmount = PTAmount + (SELECT pts.PTAmount FROM PayrollTotals pts WHERE pts.PTACID = 38 AND pts.PTAmount > 0 AND pts.PTEmpID= ptm.PTEmpID) WHERE PTACID = 42 I want to update types 42 with types 38 where the P...
HI all, Im using windows vista home premuim, and I've install Visual studio 2008 and MS SQL 2000 in my laptop, My problem was i cannot establish data connection, an error said , LOGIN failed for REJ(SQL SERVER). Im using windows authentication in connecting my visual studio to SQL ...
Hello, First - apologies for the fuzzy title, I could not find a better one. I have table with the following structure (simplification): EmpID DeptID 1 1 1 2 2 1 3 2 4 5 5 2 This table represents a many-to-many relationship. I'm interested in finding all the EmpIDs that are related to a specific group of De...
I have a reasonably large datatable structured something like this: StaffNo Grade Direct Boss2 Boss3 Boss4 Boss5 Boss6 ------- ----- ----- ----- ----- ----- ----- ----- 10001 1 10002 10002 10057 10094 10043 10099 10002 2 10057 NULL 10057 100...
The problem is during inserting data from staging table during import routine. The system is a legacy one I inherited, and short term while I develop something more suitable I want to patch things to avoid the data transfer failing. Unfortunately the facility exists via another application to create an entry into table, called CommRece...
Hi How can I get the name of all columns of a table in SQL SERVER 2008? Thank you ...
(I wish I could have come up with a more descriptive title... suggest one or edit this post if you can name the type of query I'm asking about) Database: SQL Server 2000 Sample Data (assume 500,000 rows): Name Candy PreferenceFactor Jim Chocolate 1.0 Brad Lemon Drop .9 Brad Chocolate .1 Chris Chocolate .5 Ch...
Hey, I'm not quite sure if this is the right approach, this is my situation: I'm currently trying to select 15 galleries and then left join it with the user table through the id but I also want to select one random picture from each gallery however from what I know you can't limit the left join (picture) to only pick up one random pict...
hi , i have this sql : SELECT * , ( xnum * xprice ) AS amount, SUM( xnum * xprice ) AS total FROM xxbasket LEFT JOIN xxsubproduct USING ( xsubproduct_id ) LEFT JOIN xxcolor USING ( xcolor_id ) WHERE xuserid = '3' when i use SUM( xnum * xprice ) AS total it's gives me only one row , but when i remove this SUM( xnum * xprice ) it's g...