sql

SQL: many-to-many relationship, IN condition

I have a table called transactions with a many-to-many relationship to items through the items_transactions table. I want to do something like this: SELECT "transactions".* FROM "transactions" INNER JOIN "items_transactions" ON "items_transactions".transaction_id = "transactions".id INNER JOIN "items" ON "items"....

union on the same table

I have a table: ID | Id1 | Id2 1 | 100 | 12 2 | 196 | 140 3 | 196 | 141 4 | 150 | 140 5 | 150 | 199 I want to write a query that will give me a table containing records with the same ID2 and with id1 equal to 196 or 150. I thought about union: select * from table where ID1 = 196 union select * from table where ID1 = 150 ...

Sql combine 2 rows to one

Hi , i have this table Testers employee name ------------ Sam Korch dan mano i want to combine tow rows to one, it will be "Sam Korch,Dan Mano" i have this query select @theString = COALESCE(@theString + ',', '') + EmployeeName from Testers join vw_EKDIR on Testers.TesterGlobalId = vw_EKDIR.GlobalID where TestId = 31 it w...

How to avoid Foreign Keys constraints for all tables in DB truncate ?

Hi, for designing purposes i need to truncate all DB which has lots of FK's. I cannot use DELETE command simply because some tables set with Identity of TinyInts and contain about 150 items. this is a query ( truncate all tables in selected DB ) i'm trying to run Declare @t varchar (1024) Declare tbl_cur cursor for select TABLE_NAME ...

SQL: Add counters in select

Hi, I have a table which contains names: Name ---- John Smith John Smith Sam Wood George Wright John Smith Sam Wood I want to create a select statement which shows this: Name 'John Smith 1' 'John Smith 2' 'Sam Wood 1' 'George Wright 1' 'John Smith 3' 'Sam Wood 2' In other words, I want to add separate counters to each name. ...

How to Implement Excel COMBIN function in SQL

How can i implement Excel COMBIN function in SQL 2005, either in a store procedure or in a function. About COMBIN function in Excel follow this Link. Thanks. ...

Convert row to column using sql server 2008?

Table name is Looupvalue id Ptypefield Value 1 1 D 2 1 E 3 1 F 4 1 G 5 1 H 6 2 FL 7 2 IF 8 2 VVS1 9 2 VVS2 10 2 VS1 11 2 VS2 12 3 0.50 13 3 1.00 14 3 1.50 15 3 2.00 16 4 Marquise 17 4 Round 18 4 Pear 19 4 Radiant 20 4 Princess Lookupvalue table value convert roow to ...

INSERT statement conflicted with the FOREIGN KEY constraint

I am getting the following error. Could you please help me in ? Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Sup_Item_Sup_Item_Cat". The conflict occurred in database "dev_bo", table "dbo.Sup_Item_Cat". The statement has been terminated. insert into sup_item (supplier_id,sup_it...

Adding FK Index to existing table in Merge Replication Topology

I have a table that has grown quite large that we are replicating to about 120 subscribers. A FK on that table does not have an index and when I ran an Execution Plan on a query that was causing issues it had this to say --> /* Missing Index Details from CaseNotesTimeoutQuerys.sql - mylocal\sqlexpress.MATRIX (WWCARES\pschaller (54)) T...

Using Entity Framework V4 - Mapping Stored Procedures with XML output

I have been attempting to implement the Entity framework into my project and unfortunately it seems like mapping an XML result set from SQL is not something that is supported. I also have issues with returning multiple result sets, though I understand that the EFExtensions was created to try to mitigate that issue. Is there a way to ta...

Stored Functions with Linq to Entities

Hi, How can I make a MS-SQL stored function availabe in LINQ expressions if using the Entity framework? The SQL function was created with CREATE FUNCTION MyFunction(@name) ...). I was hoping to access it similarly to this: var data = from c in entities.Users where MyFunction(c.name) = 3; Unfortunately I have only .NET 3.5 available....

Ruby find method with select

I can't seem to get this one right at the moment. I want distinct records but I need other attributes to come along with the results of the find method. Current nonworking code is: Visit.find(:all, :select => "user_id, DISTINCT cookie") ...

Check mysql_query results if DELETE query worked?

I have a DELETE query which deletes a record from a mysql db. is there any way to make sure if the delete was performed or not? I mean, for a query to FIND stuff you do $res=mysql_query($var); $nr=mysql_num_rows($res); and you get nr of rows returned. Is there any similiar method for deletion of records? Thanks ...

I can't delete records from MySql

I have two tables. table a references table b I believe. When I try to delete the package alltogether like this: $query="DELETE a, b FROM classified as a, $sql_table as b WHERE a.ad_id = '$id' AND a.classified_id = b.classified_id AND a.poster_password='$pass'"; b MUST be deleted first I guess. Even in PhpMyAdmin I cant delete a if ...

I need an answer book of Michael Mannino

Hi I want to do all problems of database development application and design book of Michael Mannino but really I don't know the correct answer of them .I have searched a lot for downloading the answer book but I couldn't find it ,please help me that how can I find it or is there any other book with this subject that has answer for its pr...

hibernate criteria filtering on a set of enum values

Hi I'm using Hibernate 3.2 with a 1.6 JDK and Mysql 5.0 I'm trying to use the criteria api to put together a dynamic filter. However when I add a Restriction on a Set of Enums that is a property of my Criteria object I get a org.hibernate.exception.GenericJDBCException. my code is like this: public class FollowUp { ... public...

sql deploy with dos

Hi, How can I customize the the FOR command below to loop through the files inside the Database folder following the order of the number prefixed in the file name? FOR /R ../Database %%f IN (*.sql) DO sqlcmd -S %1 -d %2 -U %4 -P %5 -i "%%~f" >> Logs/%2_DBInstall.log || goto errors Database folder contains: 001_usp_procedure1.sql 002_u...

ASP.NET Sql Provider

I got a problem. When i'm creating the new project in ASP.NET using VS 2010, it's web.config with a default connection string about SQL Express created. But i haven't even got SQL Express installed. What should i do to change the default AspNetSqlProvider to work with my instance of full-weight SQL Server as a services database? And how ...

foreign key constraints on primary key columns - issues ?

What are the pros/cons from a performance/indexing/data management perspective of creating a one-to-one relationship between tables using the primary key on the child as foreign key, versus a pure surrogate primary key on the child? The first approach seems to reduce redundancy and nicely constrains the one-to-one implicitly, while the...

sql: DELETE + INSERT vs UPDATE + INSERT

A similar question has been asked, but since it always depends, I'm asking for my specific situation separately. I have a web-site page that shows some data that comes from a database, and to generate the data from that database I have to do some fairly complex multiple joins queries. The data is being updated once a day (nightly). I ...