Search Results

Search found 28297 results on 1132 pages for 'sql azure'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 627/1132 | < Previous Page | 623 624 625 626 627 628 629 630 631 632 633 634  | Next Page >

  • MySQL foreign key creation with alter table command

    - by user313338
    I created some tables using MySQL Workbench, and then did forward ‘forward engineer’ to create scripts to create these tables. BUT, the scripts lead me to a number of problems. One of which involves the foreign keys. So I tried creating separate foreign key additions using alter table and I am still getting problems. The code is below (the set statements, drop/create statements I left in … though I don’t think they should matter for this): SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; DROP SCHEMA IF EXISTS `mydb` ; CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; -- ----------------------------------------------------- -- Table `mydb`.`User` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`User` ; CREATE TABLE IF NOT EXISTS `mydb`.`User` ( `UserName` VARCHAR(35) NOT NULL , `Num_Accts` INT NOT NULL , `Password` VARCHAR(45) NULL , `Email` VARCHAR(45) NULL , `User_ID` INT NOT NULL AUTO_INCREMENT , PRIMARY KEY (`User_ID`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`User_Space` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`User_Space` ; CREATE TABLE IF NOT EXISTS `mydb`.`User_Space` ( `User_UserName` VARCHAR(35) NOT NULL , `User_Space_ID` INT NOT NULL AUTO_INCREMENT , PRIMARY KEY (`User_Space_ID`), FOREIGN KEY (`User_UserName`) REFERENCES `mydb`.`User` (`UserName`) ON UPDATE CASCADE ON DELETE CASCADE) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; The error this produces is: Error Code: 1005 Can't create table 'mydb.user_space' (errno: 150) Anybody know what the heck I’m doing wrong?? And anybody else have problems with the script generation done by mysql workbench? It’s a nice tool, but annoying that it pumps out scripts that don’t work for me. [As an fyi here’s the script it auto-generates: SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; DROP SCHEMA IF EXISTS `mydb` ; CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; -- ----------------------------------------------------- -- Table `mydb`.`User` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`User` ; CREATE TABLE IF NOT EXISTS `mydb`.`User` ( `UserName` VARCHAR(35) NOT NULL , `Num_Accts` INT NOT NULL , `Password` VARCHAR(45) NULL , `Email` VARCHAR(45) NULL , `User_ID` INT NOT NULL AUTO_INCREMENT , PRIMARY KEY (`User_ID`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`User_Space` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`User_Space` ; CREATE TABLE IF NOT EXISTS `mydb`.`User_Space` ( `User_Space_ID` INT NOT NULL AUTO_INCREMENT , PRIMARY KEY (`User_Space_ID`) , INDEX `User_ID` () , CONSTRAINT `User_ID` FOREIGN KEY () REFERENCES `mydb`.`User` () ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; ** Thanks!]

    Read the article

  • Linq2SQL vs NHibernate performance (have I gone mad?)

    - by HeavyWave
    I have written the following tests to compare performance of Linq2SQL and NHibernate and I find results to be somewhat strange. Mappings are straight forward and identical for both. Both are running against a live DB. Although I'm not deleting Campaigns in case of Linq, but that shouldn't affect performance by more than 10 ms. Linq: [Test] public void Test1000ReadsWritesToAgentStateLinqPrecompiled() { Stopwatch sw = new Stopwatch(); Stopwatch swIn = new Stopwatch(); sw.Start(); for (int i = 0; i < 1000; i++) { swIn.Reset(); swIn.Start(); ReadWriteAndDeleteAgentStateWithLinqPrecompiled(); swIn.Stop(); Console.WriteLine("Run ReadWriteAndDeleteAgentState: " + swIn.ElapsedMilliseconds + " ms"); } sw.Stop(); Console.WriteLine("Total Time: " + sw.ElapsedMilliseconds + " ms"); Console.WriteLine("Average time to execute queries: " + sw.ElapsedMilliseconds / 1000 + " ms"); } private static readonly Func<AgentDesktop3DataContext, int, EntityModel.CampaignDetail> GetCampaignById = CompiledQuery.Compile<AgentDesktop3DataContext, int, EntityModel.CampaignDetail>( (ctx, sessionId) => (from cd in ctx.CampaignDetails join a in ctx.AgentCampaigns on cd.CampaignDetailId equals a.CampaignDetailId where a.AgentStateId == sessionId select cd).FirstOrDefault()); private void ReadWriteAndDeleteAgentStateWithLinqPrecompiled() { int id = 0; using (var ctx = new AgentDesktop3DataContext()) { EntityModel.AgentState agentState = new EntityModel.AgentState(); var campaign = new EntityModel.CampaignDetail { CampaignName = "Test" }; var campaignDisposition = new EntityModel.CampaignDisposition { Code = "123" }; campaignDisposition.Description = "abc"; campaign.CampaignDispositions.Add(campaignDisposition); agentState.CallState = 3; campaign.AgentCampaigns.Add(new AgentCampaign { AgentState = agentState }); ctx.CampaignDetails.InsertOnSubmit(campaign); ctx.AgentStates.InsertOnSubmit(agentState); ctx.SubmitChanges(); id = agentState.AgentStateId; } using (var ctx = new AgentDesktop3DataContext()) { var dbAgentState = ctx.GetAgentStateById(id); Assert.IsNotNull(dbAgentState); Assert.AreEqual(dbAgentState.CallState, 3); var campaignDetails = GetCampaignById(ctx, id); Assert.AreEqual(campaignDetails.CampaignDispositions[0].Description, "abc"); } using (var ctx = new AgentDesktop3DataContext()) { ctx.DeleteSessionById(id); } } NHibernate (the loop is the same): private void ReadWriteAndDeleteAgentState() { var id = WriteAgentState().Id; StartNewTransaction(); var dbAgentState = agentStateRepository.Get(id); Assert.IsNotNull(dbAgentState); Assert.AreEqual(dbAgentState.CallState, 3); Assert.AreEqual(dbAgentState.Campaigns[0].Dispositions[0].Description, "abc"); var campaignId = dbAgentState.Campaigns[0].Id; agentStateRepository.Delete(dbAgentState); NHibernateSession.Current.Transaction.Commit(); Cleanup(campaignId); NHibernateSession.Current.BeginTransaction(); } Results: NHibernate: Total Time: 9469 ms Average time to execute 13 queries: 9 ms Linq: Total Time: 127200 ms Average time to execute 13 queries: 127 ms Linq lost by 13.5 times! Event with precompiled queries (both read queries are precompiled). This can't be right, although I expected NHibernate to be faster, this is just too big of a difference, considering mappings are identical and NHibernate actually executes more queries against the DB.

    Read the article

  • Error invoking stored procedure with input parameter from ADO.Net

    - by George2
    Hello everyone, I am using VSTS 2008 + C# + .Net 3.5 + ADO.Net. Here is my code and related error message. The error message says, @Param1 is not supplied, but actually it is supplied in my code. Any ideas what is wrong? System.Data.SqlClient.SqlException: Procedure or function 'Pr_Foo' expects parameter '@Param1', which was not supplied. class Program { private static SqlCommand _command; private static SqlConnection connection; private static readonly string _storedProcedureName = "Pr_Foo"; private static readonly string connectionString = "server=.;integrated Security=sspi;initial catalog=FooDB"; public static void Prepare() { connection = new SqlConnection(connectionString); connection.Open(); _command = connection.CreateCommand(); _command.CommandText = _storedProcedureName; _command.CommandType = CommandType.StoredProcedure; } public static void Dispose() { connection.Close(); } public static void Run() { try { SqlParameter Param1 = _command.Parameters.Add("@Param1", SqlDbType.Int, 300101); Param1.Direction = ParameterDirection.Input; SqlParameter Param2 = _command.Parameters.Add("@Param2", SqlDbType.Int, 100); portal_SiteInfoID.Direction = ParameterDirection.Input; SqlParameter Param3 = _command.Parameters.Add("@Param3", SqlDbType.Int, 200); portal_RoleInfoID.Direction = ParameterDirection.Input; _command.ExecuteScalar(); } catch (Exception e) { Console.WriteLine(e); } } static void Main(string[] args) { try { Prepare(); Thread t1 = new Thread(Program.Run); t1.Start(); t1.Join(); Dispose(); } catch (Exception ex) { Console.WriteLine(ex.Message + "\t" + ex.StackTrace); } } } Thanks in advance, George

    Read the article

  • Access 2007 file picker, replaces all rows with the same choice.

    - by SqlStruggle
    This code is from an Access 2007 project I've been struggling with. The actual mean part is the part where I should put something like "update only current form" DoCmd.RunSQL "Update Korut Set [PikkuKuva]=('" & varFile & "') ;" Could someone please help me with this?` If I use it now, it updates all the tables with the same file picked. Heres the whole code. ' This requires a reference to the Microsoft Office 11.0 Object Library. Dim fDialog As Office.FileDialog Dim varFile As Variant Dim filePath As String ' Set up the File dialog box. Set fDialog = Application.FileDialog(msoFileDialogFilePicker) With fDialog ' Allow the user to make multiple selections in the dialog box. .AllowMultiSelect = False ' Set the title of the dialog box. .Title = "Valitse Tiedosto" ' Clear out the current filters, and then add your own. .Filters.Clear .Filters.Add "All Files", "*.*" ' user picked at least one file. If the .Show method returns ' False, the user clicked Cancel. If .Show = True Then ' Loop through each file that is selected and then add it to the list box. For Each varFile In .SelectedItems DoCmd.SetWarnings True DoCmd.RunSQL "Update Korut Set [PikkuKuva]=('" & varFile & "') ;" Next Else MsgBox "You clicked Cancel in the file dialog box." End If End With

    Read the article

  • archiving strategies and limitations of data in a table

    - by Samuel
    Environment: Jboss, Mysql, JPA, Hibernate Our web application will be catering to a large amount of users (~ 1,000,000) and there are a lots of child table where user specific data are stored (e.g. personal, health, forum contributions ...). What would be the best practice to archive user & user specific information. [a] Would it be wise to move the archived user & user specific information to their respective tables within the same database (e.g. user_archive, user_forum_comments_archive ...) OR [b] Would you just mark the database entries with a flag in the original table(s) and just query only non archived entries. We have a unique constraint on User.loginid, how do you handle this requirement if the users are archived via 1-[a] (i.e if a user with loginid 'samuel' gets moved into the archive table and if a new user gets added with the same name in the original table, how would you prevent this. What would be the best strategy to address the unique key constraints. We have a requirement to selectively archive records and bring it back if necessary, will you rely on database tools are would you handle this via your persistence APIs exposed by the JPA entity model.

    Read the article

  • named_scope or find_by_sql?

    - by keruilin
    I have three models: User Award Trophy The associations are: User has many awards Trophy has many awards Award belongs to user Award belongs to trophy User has many trophies through awards Therefore, user_id is a fk in awards, and trophy_id is a fk in awards. In the Trophy model, which is an STI model, there's a trophy_type column. I want to return a list of users who have been awarded a specific trophy -- (trophy_type = 'GoldTrophy'). Users can be awarded the same trophy more than once. (I don't want distinct results.) Can I use a named_scope? How about chaining them? Or do I need to use find_by_sql? Either way, how would I code it?

    Read the article

  • How To Find Reasons of Why Site Goes Online/Offline

    - by HollerTrain
    Seems today a website I manage has been going online and offline throughout the entire day. I have no idea what is causing the issue so I am seeking guidance on where to start. It is a Wordpress based site. So here is what I DO know: I use a program that pings the server every minute and when the server is not responding me it emails me, so I can know exactly when the site is online and offline. The site between 8pm to 12pm 12.28, and around the 1a hour early morning 12.29 (New York City timezone, and all times below are in same timezone). At the time of the ups/downs I see a lot of strain on the memory usage. Look at the load average when the site is going online/offline (http://screencast.com/t/BRlfXkqrbJII). Then I ran this command to restart http (http://screencast.com/t/usVtYWZ2Qi) and the memory usage then goes down to this (http://screencast.com/t/VdTIy3bgZiQB). An hour after I restarted http, the site then went offline/online so restarting the http didn't do much help. When the site is going offline/online, I ran the top command and get this (http://screencast.com/t/zEwr7YQj3). Here is a top command when the site is at it's lowest (http://screencast.com/t/eaMfha9lbT - so this would be dubbged "normal"). Here is a bandwidth report (http://screencast.com/t/AS0h2CH1Gypq). The traffic doesn't seem to be that much (http://screencast.com/t/s7hrWNNic1K), but looking at my times the site is going up/down this may be one of the reasons? I have the dvp Nitro package at Media Temple (http://mediatemple.net/webhosting/nitro/). So at this point I would request some help in trying to figure out what the cause of this is, and how I can go about pinpointing this issue. ANY HELP is greatly appreciated.

    Read the article

  • Query to find all the nodes that are two steps away from a particular node.

    - by iecut
    Suppose I have two columns in a table that represents a graph, the first column is a FROMNODE and second one is TONODE. What I would like to know is that how will we find all the nodes that are two steps away from a particular node. Lets suppose I have a node numbered '1' and i would like to know all the nodes that are two steps away from it. I have tried(I am assuming the table name as graph) SELECT FROMNODE FROM GRAPH WHERE TONODE=1 (this is to select all the nodes that are connected to node 1, but I couldn't figure out how would I find all the nodes that are two steps away from node 1??)

    Read the article

  • Not getting the right expected output for my Mysql Query?

    - by user1878107
    i've 4 tables as shown below doctors id name ------------ 1 Mathew 2 Praveen 3 Rosie 4 Arjun 5 Denis doctors_appointments id doctors_id patient_name contact date status -------------------------------------------------------------------------------------- 1 5 Nidhin 9876543210 2012-12-10 15:39:41 Registered 2 5 Sunny 9876543210 2012-12-18 15:39:48 Registered 3 5 Mani 9876543210 2012-12-12 15:39:57 Registered 4 2 John 9876543210 2012-12-24 15:40:09 Registered 5 4 Raj 9876543210 2012-12-05 15:41:57 Registered 6 3 Samuel 9876543210 2012-12-14 15:41:33 Registered 7 2 Louis 9876543210 2012-12-24 15:40:23 Registered 8 1 Federick 9876543210 2012-12-28 15:41:05 Registered 9 2 Sam 9876543210 2012-12-12 15:40:38 Registered 10 4 Sita 9876543210 2012-12-12 15:41:00 Registered doctors_dutyplan id doctor_id weeks time no_of_patients ------------------------------------------------------------------ 1 1 3,6,7 9:00am-1:00pm 10 2 2 3,4,5 1:00pm-4:00pm 7 3 3 3,6,7 10:00am-2:00pm 10 4 4 3,4,5,6 8:30am-12:30pm 12 5 5 3,4,5,6,7 9:00am-4:00pm 30 emp_leave id empid leavedate -------------------------------- 1 2 2012-12-05 14:42:36 2 2 2012-12-03 14:42:59 3 3 2012-12-03 14:43:06 4 3 2012-12-06 14:43:14 5 5 2012-12-04 14:43:24 My task is to find all the days in a month in which the doctor is available excluding the leave dates. My query what is wrote is given below: SELECT DATE_ADD( '2012-12-01', INTERVAL ROW DAY ) AS Date, ROW +1 AS DayOfMonth FROM ( SELECT @row := @row +1 AS ROW FROM ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 )t1, ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 )t2, ( SELECT @row := -1 )t3 LIMIT 31 )b WHERE DATE_ADD( '2012-12-01', INTERVAL ROW DAY ) BETWEEN '2012-12-01' AND '2012-12-31' AND DAYOFWEEK( DATE_ADD( '2012-12-01', INTERVAL ROW DAY ) ) =2 AND DATE_ADD( '2012-12-01', INTERVAL ROW DAY ) NOT IN ( SELECT DATE_FORMAT( l.leavedate, '%Y-%m-%d' ) AS date FROM doctors_dutyplan d LEFT JOIN emp_leave AS l ON d.doctor_id = l.empid WHERE doctor_id =2 ) This works fine for all doctors who took any leave in a particular day in a month (here in the example it is Decemeber 2012). and the result is shown below: Date DayOfMonth ----------------------- 2012-12-10 10 2012-12-17 17 2012-12-24 24 2012-12-31 31 But on the other hand for the doctors who did'nt took any leave , for that my query is showing empty table, example for the doctor Mathew whose id is 1, my query returns an empty result can anyone please tell a solution for this problem. Thanks in advance.

    Read the article

  • MySQL query advice

    - by vasion
    I am lost in MySQL documentation. I have a table with votes - it has these columns id song_id user_id created I cannot find the query which will process the information and output the 10 most voted songs in a given time period. What is it?

    Read the article

  • ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'

    - by Nitin Gurram
    I am trying to update the table which has millions of records. However my update query will update around 2-3 millions of records. I am facing below error on executing the update query. I googled and found that I need to update the Table Space as DBA But is there any work around for executing the update without actually extending the UNDO table space or something dba is not required UPDATESERVICE SET CREATION_TIME = LAST_UPDATE_TIME WHERE CREATION_TIME is null

    Read the article

  • mysql query for getting all messages that belong to user's contacts

    - by aharon
    So I have a database that is setup sort of like this (simplified, and in terms of the tables, all are InnoDBs): Users: contains based user authentication information (uid, username, encrypted password, et cetera) Contacts: contains two rows per relationship that exists between users as (uid1, uid2), (uid2, uid1) to allow for a good 1:1 relationship (must be mutual) between users Messages: has messages that consist of a blob, owner-id, message-id (auto_increment) So my question is, what's the best MySQL query to get all messages that belong to all the contacts of a specific user? Is there an efficient way to do this?

    Read the article

  • How to write this Link2Sql select query

    - by RememberME
    I have 3 tables: subcontracts, companies and contacts. Each table has a active_status flags which shows that the item is still active (hasn't been deleted). Each contact has a company_id field which stores the id of the contact's company. Each subcontract has a company_id field which stores the subcontract's company. Each company has a company_id field which holds its guid and a primary_company field, b/c the company could be a subsidiary. If it's a subsidiary, the primary_company field holds the id of the primary company. I have a subcontract form. On the form, I have a drop-down of contacts which are stored in a contact table. Currently the drop-down lists all contacts. I would like to have the drop-down only list contacts which belong to the subcontract's company or any of that company's subsidiaries. I have the following query which I use elsewhere in the program to pass a company_id and get its subsidiaries. public IQueryable<company> GetSubsidiaryCompanies(Guid id) { return from c in db.companies where c.primary_company == id && c.active_status == true select c; } This is my current contact selection public IQueryable<contact> GetContacts() { return from c in db.contacts where c.active_status == true orderby c.contact_name select c; } What I need to do is pass it the subcontract.company_id and then only return the contacts where contact.company_id == subcontract.company_id or contact.company_id == one of the subsidiary ids.

    Read the article

  • Arguments for moving from LINQtoSQL to Nhibernate?

    - by sah302
    Backstory: Hi all, I just spent a lot of time reading many of the LINQ vs Nhibernate threads here and on other sites. I work in a small development team of 4 people and we don't even have really any super experienced developers. We work for a small company that has a lot of technical needs but not enough developers to implement them (and hiring more is out of the question right now). Typically our projects (which individually are fairly small) have been coded separately and weren't really layered in anyway, code wasn't re-used, no class libraries, and we just use the LINQtoSQL .dbml files for our pojects, we really don't even use objects but pass around values and stuff, the only time we use objects is when inserting to a database (heck not even querying since you don't need to assign it to a type and can just bind to gridview). Despite all this as I said our company has a lot of technical needs, no one could come to us for a year and we would have plenty of work to implement requested features. Well I have decided to change that a bit first by creating class libraries and actually adding layers to our applications. I am trying to meet these guys halfway by still using LINQtoSQL as the ORM yet and still use VB as the language. However I am finding it a b***h of a time dealing with so many thing in LINQtoSQL that I found easy in Nhibernate (automatic handling of the session, criteria creation easier than expression trees, generic an dynamic querying easier etc.) So... Question: How can I convince my lead developers and other senior programmers that switching to Nhibernate is a good thing? That being in control of our domain objects is a good thing? That being able to implement interfaces is a good? I've tried exlpaining the advantages of this before but it's not understood by them because they've never programmed in a true OO & layered way. Also one of the counter arguments to this I can see is sqlMetal generates those classes automatically and therefore it saves a lot of time. I can't really counter that other than saying spending more time on infrastructure to make it more scalable and flexible is good, but they can't see how. Again, I know the features and advantages (somewhat enough I believe) of each, but I need arguments applicable to my context, hence why I provided the context. I just am not a very good arguer I guess. (Caveat: For all the LINQtoSQL lovers, I may just not be super proficient as LINQ, but I find it very cumbersome that you are required to download some extra library for dynamic queries which don't by default support guid comparisons, and I also find the way of updating entitites to be cumbersome as well in terms of data context managing, so it could just be that I suck hehe.)

    Read the article

  • how to convert Database Hierarchical Data to XML using ASP.net 3.5 and LINQ

    - by mahdiahmadirad
    hello guys! i have a table with hierarchical structure. like this: and table data shown here: this strategy gives me the ability to have unbounded categories and sub-categories. i use ASP.net 3.5 SP1 and LINQ and MSSQL Server2005. How to convert it to XML? I can Do it with Dataset Object and ".GetXML()" method. but how to implement it with LINQtoSQL or LINQtoXML??? or if there is another simpler way to perform that? what is your suggestion? the best way? I searched the web but found nothing for .net 3.5 featuers.

    Read the article

  • An INSERT conditioned on COUNT

    - by Anders Feder
    How can I construct a MySQL INSERT query that only executes if the number of rows satisfying some condition already in the table is less than 20, and fails otherwise? That is, if the table has 18 rows satisfying the condition, then the INSERT should proceed. If the table has 23 rows satisfying the condition, then the INSERT should fail. For atomicity, I need to express this in a single query, so two requests can not INSERT at the same time, each in the 'belief' that only 19 rows satisfy the condition. Thank you.

    Read the article

  • Can't figure out how to list all the people that don't live in same City as...

    - by AspOnMyNet
    I’d like to list all the people ( Person table ) that don’t live in same city as those cities listed in Location table. Thus, if Location table holds a record with City=’New York’ and State=’Moon’, but Person table holds a record with FirstName=’Someone’, City=’New York’ and Location=’Mars’, then Someone is listed in the resulting set, since she lives in New York located on Mars and not New York located on Moon, thus we’re talking about different cities with the same name. I tried solving it with the following query, but results are wrong: SELECT Person.FirstName, Person.LastName, Person.City, Person.State FROM Person INNER JOIN Location ON (Person.City <> Location.City AND Person.State = Location.State) OR (Person.City = Location.City AND Person.State <> Location.State) OR (Person.City <> Location.City AND Person.State <> Location.State) ORDER BY Person.LastName; Any ideas?

    Read the article

  • Where Not In OR Except simulation of SQL in LINQ to Objects

    - by Thinking
    Suppose I have two lists that holds the list of source file names and destination file names respectively. The Sourcefilenamelist has files as 1.txt, 2.txt,3.txt, 4.txt while the Destinaitonlist has 1.txt,2.txt. I ned to write a linq query to find out which files are in SourceList that are absent in DestinationFile list. e.g. here the out put will be 3.txt and 4.txt. I have done this by a foreach statement. but now I want to do the same by using LINQ(C#). Edit: My Code is List<FileList> sourceFileNames = new List<FileList>(); sourceFileNames.Add(new FileList { fileNames = "1.txt" }); sourceFileNames.Add(new FileList { fileNames = "2.txt" }); sourceFileNames.Add(new FileList { fileNames = "3.txt" }); sourceFileNames.Add(new FileList { fileNames = "4.txt" }); List<FileList> destinationFileNames = new List<FileList>(); destinationFileNames.Add(new FileList { fileNames = "1.txt" }); destinationFileNames.Add(new FileList { fileNames = "2.txt" }); IEnumerable<FileList> except = sourceFileNames.Except(destinationFileNames); And Filelist is a simple class with only one property fileNames of type string.

    Read the article

  • TSQL: finding unique entries in a single table

    - by pcampbell
    Consider a table or CTE structured like this: Name Num ---- ---- Abc 12 Abc 12 XYZ 70 XYZ 80 Bar 50 Bar 55 Foo 44 Foo 44 Baz 88 The requirement is to determine the Name where multiple different Nums exist. The desired resultset is Name ---- XYZ Bar What TSQL statement would you use to derive this resultset?

    Read the article

  • Understand Sql Server connectionstring for asp.net

    - by Eatdoku
    Hi, I am trying to understand the differences between the following 2 connectionstrings. one uses servername\instancename and the other one uses the server ip address. Can I specify port number for "serverName\instanceName". I know you can specify port number for ip address, something like '10.0.0.1,xxx'. thanks, Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True; Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

    Read the article

  • Strange: Planner takes decision with lower cost, but (very) query long runtime

    - by S38
    Facts: PGSQL 8.4.2, Linux I make use of table inheritance Each Table contains 3 million rows Indexes on joining columns are set Table statistics (analyze, vacuum analyze) are up-to-date Only used table is "node" with varios partitioned sub-tables Recursive query (pg = 8.4) Now here is the explained query: WITH RECURSIVE rows AS ( SELECT * FROM ( SELECT r.id, r.set, r.parent, r.masterid FROM d_storage.node_dataset r WHERE masterid = 3533933 ) q UNION ALL SELECT * FROM ( SELECT c.id, c.set, c.parent, r.masterid FROM rows r JOIN a_storage.node c ON c.parent = r.id ) q ) SELECT r.masterid, r.id AS nodeid FROM rows r QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------- CTE Scan on rows r (cost=2742105.92..2862119.94 rows=6000701 width=16) (actual time=0.033..172111.204 rows=4 loops=1) CTE rows -> Recursive Union (cost=0.00..2742105.92 rows=6000701 width=28) (actual time=0.029..172111.183 rows=4 loops=1) -> Index Scan using node_dataset_masterid on node_dataset r (cost=0.00..8.60 rows=1 width=28) (actual time=0.025..0.027 rows=1 loops=1) Index Cond: (masterid = 3533933) -> Hash Join (cost=0.33..262208.33 rows=600070 width=28) (actual time=40628.371..57370.361 rows=1 loops=3) Hash Cond: (c.parent = r.id) -> Append (cost=0.00..211202.04 rows=12001404 width=20) (actual time=0.011..46365.669 rows=12000004 loops=3) -> Seq Scan on node c (cost=0.00..24.00 rows=1400 width=20) (actual time=0.002..0.002 rows=0 loops=3) -> Seq Scan on node_dataset c (cost=0.00..55001.01 rows=3000001 width=20) (actual time=0.007..3426.593 rows=3000001 loops=3) -> Seq Scan on node_stammdaten c (cost=0.00..52059.01 rows=3000001 width=20) (actual time=0.008..9049.189 rows=3000001 loops=3) -> Seq Scan on node_stammdaten_adresse c (cost=0.00..52059.01 rows=3000001 width=20) (actual time=3.455..8381.725 rows=3000001 loops=3) -> Seq Scan on node_testdaten c (cost=0.00..52059.01 rows=3000001 width=20) (actual time=1.810..5259.178 rows=3000001 loops=3) -> Hash (cost=0.20..0.20 rows=10 width=16) (actual time=0.010..0.010 rows=1 loops=3) -> WorkTable Scan on rows r (cost=0.00..0.20 rows=10 width=16) (actual time=0.002..0.004 rows=1 loops=3) Total runtime: 172111.371 ms (16 rows) (END) So far so bad, the planner decides to choose hash joins (good) but no indexes (bad). Now after doing the following: SET enable_hashjoins TO false; The explained query looks like that: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CTE Scan on rows r (cost=15198247.00..15318261.02 rows=6000701 width=16) (actual time=0.038..49.221 rows=4 loops=1) CTE rows -> Recursive Union (cost=0.00..15198247.00 rows=6000701 width=28) (actual time=0.032..49.201 rows=4 loops=1) -> Index Scan using node_dataset_masterid on node_dataset r (cost=0.00..8.60 rows=1 width=28) (actual time=0.028..0.031 rows=1 loops=1) Index Cond: (masterid = 3533933) -> Nested Loop (cost=0.00..1507822.44 rows=600070 width=28) (actual time=10.384..16.382 rows=1 loops=3) Join Filter: (r.id = c.parent) -> WorkTable Scan on rows r (cost=0.00..0.20 rows=10 width=16) (actual time=0.001..0.003 rows=1 loops=3) -> Append (cost=0.00..113264.67 rows=3001404 width=20) (actual time=8.546..12.268 rows=1 loops=4) -> Seq Scan on node c (cost=0.00..24.00 rows=1400 width=20) (actual time=0.001..0.001 rows=0 loops=4) -> Bitmap Heap Scan on node_dataset c (cost=58213.87..113214.88 rows=3000001 width=20) (actual time=1.906..1.906 rows=0 loops=4) Recheck Cond: (c.parent = r.id) -> Bitmap Index Scan on node_dataset_parent (cost=0.00..57463.87 rows=3000001 width=0) (actual time=1.903..1.903 rows=0 loops=4) Index Cond: (c.parent = r.id) -> Index Scan using node_stammdaten_parent on node_stammdaten c (cost=0.00..8.60 rows=1 width=20) (actual time=3.272..3.273 rows=0 loops=4) Index Cond: (c.parent = r.id) -> Index Scan using node_stammdaten_adresse_parent on node_stammdaten_adresse c (cost=0.00..8.60 rows=1 width=20) (actual time=4.333..4.333 rows=0 loops=4) Index Cond: (c.parent = r.id) -> Index Scan using node_testdaten_parent on node_testdaten c (cost=0.00..8.60 rows=1 width=20) (actual time=2.745..2.746 rows=0 loops=4) Index Cond: (c.parent = r.id) Total runtime: 49.349 ms (21 rows) (END) - incredibly faster, because indexes were used. Notice: Cost of the second query ist somewhat higher than for the first query. So the main question is: Why does the planner make the first decision, instead of the second? Also interesing: Via SET enable_seqscan TO false; i temp. disabled seq scans. Than the planner used indexes and hash joins, and the query still was slow. So the problem seems to be the hash join. Maybe someone can help in this confusing situation? thx, R.

    Read the article

  • How to calculate the sum of a column in an MS Access table for a given date (a single day, month or year)

    - by cMinor
    I have a table in Access in a custom format saved as dd/MM/yyyy hh:mm:ss tt , Also A form in VB.NET 2010, I get a specific day, month and year with no problem but the problem comes when I want to query the sum of a column named value depending on a specific month or day or year.... The table is like: +-----+-----------+-------------------------+ | id | value | date | +-----+-----------+-------------------------+ | id1 | 1499 | 01/01/2012 07:30:11 p.m.| | id2 | 1509 | 11/02/2012 07:30:11 p.m.| | id3 | 1611 | 21/10/2012 07:30:11 p.m.| | id1 | 1115 | 11/10/2012 07:30:11 p.m.| | id1 | 1499 | 17/05/2012 07:30:11 p.m.| | id2 | 1709 | 11/06/2012 07:30:11 p.m.| | id3 | 1911 | 30/07/2012 07:30:11 p.m.| | id1 | 1015 | 01/08/2012 07:30:11 p.m.| | id1 | 1000 | 11/05/2012 07:30:11 p.m.| |+-----+-----------+------------------------+ So I Know the query SELECT SUM(value) FROM mytable WHERE date in='01/05/2012 00:00:00' ... How to tell the query I want the month of May so I would get 1499+1000= 2499 Or how to tell I want the year 2012 so I would get the sum of all the table Which would be the correct syntax...

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 623 624 625 626 627 628 629 630 631 632 633 634  | Next Page >