sql

delete main row and all children mysql and php

Hi there, I have inherited a PHP project and the client is wanting to add some functionality to their CMS, basically the CMS allows them to create some news, all the news starts with the same content, and that is saved in one table, the actually news headline and articles are saved in another table, and the images for the news are save...

Is a date in Oracle-SQL without a time-value always 00:00:00?

Heyho, I need to grab some datas from actions which been done from date A 00:00:00 and date B 00:00:00 (in this case Date A: 16.07.2010 Date B: 20.07.2010) so i wrote this select-statement: Select avg(cnt),fext from ( Select to_char(mytable.dateadded, 'DD.MM.YYYY') dateadded, fext, count(id...

I need some advice on storing data in mysql, where one needs to store more than one, let say userids for a single post?

In cases when some one needs to store more than one value in a in a cell, what approach is more desirable and advisable, storing it with delimiters or glue and exploding it into an array later for processing in the server side language of choice, for example. $returnedFromDB = "159|160|161|162|163|164|165"; $myIdArray = explode("|",$ret...

c# with SQL (ADO.Net vs others?)

Hello, I'm new to C# with MSSQL, so I was wondering, what is the most reliable vs Easy method to connect my C# App to MSSQL to do the usual read/add/edit/update/delete ..... which path I should take? I just want a reliable way to do the work note: using VS2010 cheers ...

Lost connection to MySQL server in Windows XP while adding Data Source - ODBC

Hi, When I am trying to connect to the localhost, there is no problem. But when I am connecting to my server, I am getting this error. [08S01][MySQL][ODBC 5.1 Driver] Lost connection to MySQL server at ‘reading initial communication packet’, system error: 2 Please tell me how to work around this. I have tried with MySQL ODBC Drivers, b...

Join query with multiple tables invovled

Hi, I am using Join in sql for first time with respect to many tables, I have got error with this : I have three tables, Semester table StudentID Department Semester 1 1 1 Course Table CourseID CourseName Semester 1 S.E 1 2 D.B 1 ExamAttend Table(foreign keys as StudentID and ...

Aggregate functions return wrong values when joining more tables

I want to display all customers and their addresses and the number and total sum of their orders. My query looks like this: select *, sum(o.tota), count(o.total) from customer c natural join orders o group by c.custId; which works fine. but if I add a new table to the query: select *, sum(o.tota), count(o.total) from customer c ...

insert identity table column value

i have table propertybag, it's have only one identity coloumn fo eg propertybagid identity(1,1) i want to insert this value to this column one by one by query how? plz reply soon ...

Table structure of a student

I want a table structure which can store the details of the student like the below format. If the student is in 10 th standard -> I need his aggregate % from 1st standard to 9th standard. 5 th standard -> I need his aggregate % from 1st standard to 4th standard. 1 st standard -> N...

SQL Server 2008 : replace string

I have a table with an erroneous symbol('�') in a number of rows in one column. The TSQL script below does not work. UPDATE S SET S.Offering_Details = REPLACE(S.Offering_Details, '�', '...') FROM tblSacrifices S The column in question has datatype of nvarchar(230) and allows null entries. The data came from a csv file converted from...

How to select even non existent values

I'm using SQL-Server 2005 I have two tables, Users and Orders. Each user can have many orders. Tables connected via userID. Orders has date column ( which is when order was made ). Users have registrationSite column ( which is who is the affiliate site behind user and all of his orders ). I want to select sum of orders per day and site...

Multiple record to be passed to a function which inserts it to a table.

HI, I have a web form which has 23 textbox's , I need to pass it to a function, which has SQL command to insert the values of 23 textbox to the table.. Is there a better way using DataSet and SQLDataAdapter? Becuase if not then i need to pass all the 23 vlues in the function like //function to insert textbox values to the table public...

SQL Server 2008 Table Partitioning

I have a huge database that has several tables that hold several million records. It's holding engineering data for a client and continually grows. This is impacting performance, even with optimised indexing. So I've been looking at partitioning. However, I would be looking at partitioning on a version held in a table. In it's most sim...

Return NEWSEQUENTIALID() as an output parameter

Imagine a table that looks like this: CREATE TABLE [dbo].[test]( [id] [uniqueidentifier] NULL, [name] [varchar](50) NULL ) GO ALTER TABLE [dbo].[test] ADD CONSTRAINT [DF_test_id] DEFAULT (newsequentialid()) FOR [id] GO With an INSERT stored procedure that looks like this: CREATE PROCEDURE [Insert_test] @name as varc...

What is the difference between INTERSECT and WHERE IN ?

I have written and passed 70-433 (SQL 2008 MCTS exam) and was reviewing when I realised that I have been doing what INTERSECT does with a WHERE IN subquery, as well as EXCEPT with a WHERE NOT IN subquery. Are there any differences between using the new commands instead of subqueries? ...

Data Base design turnkey records

Hi, I have some questions regarding data base design for simple CMS. Every Page in Table "PAGES" could be "AssignedTo" and "LockedBy" by a specific User. To manage Users I am using SQL specific table "aspnet_Users", every User is identified using a GUI uniqueIdentifier column. If a page is "Unlock" and can be edited by Users, its value...

select max for more than one column?

Hello.. Suppose i have the following table Students (StudentID, StudentName) StudentMarks (StudentID, Mark) I need to select the student with the highest mark.. if there is more than one student have the same mark (and its the highest mark).. then select according to name order (alphabatically) How can i do this? Thanks in advance ...

Does something like Common Table Expressions exist in PL/SQL?

I recently learned about CTE's in SQL Server and am attempting to use it in PL/SQL. I do not need the recurive benefits of it, however, I would like to use it in lieu of creating a view and to improve query performance. Just looking for some direction on what code may be similar. ...

SQL - looping through columns, once empty is found, update it

Hi, I started similar thread but as the problem is more difficult, I would like to start from scratch. Imagine there are 10 inventory slots (in DB they are columns in the user's row). When an users picks an item up, it should be placed into the first empty column (it gets updated). But how to do the looping through the columns (except fo...

how to Create SQL Server Database with User?

How (in SQL Server) to create a database and create a user and this user will be an admin on this database only with SQL script? I mean this user can not do/access any thing out side this database! and is it possible to create user that has only can add, update/edit or delete data from tables and nothing else? I know how to do that in ...