Search Results

Search found 7240 results on 290 pages for 'natural join'.

Page 42/290 | < Previous Page | 38 39 40 41 42 43 44 45 46 47 48 49  | Next Page >

• Efficient way to store order in mySQL for list of items

  - by ninumedia

  I want to code cleaner and more efficiently and I wanted to know any other suggestions for the following problem: I have a mySQL database that holds data about a set of photograph names. Oh, say 100 photograph names Table 1: (photos) has the following fields: photo_id, photo_name Ex data: 1 | sunshine.jpg 2 | cloudy.jpg 3 | rainy.jpg 4 | hazy.jpg ... Table 2: (categories) has the following fields: category_id, category_name, category_order Ex data: 1 | Summer Shots | 1,2,4 2 | Winter Shots | 2,3 3 | All Seasons | 1,2,3,4 ... Is it efficient to store the order of the photos in this manner per entry via comma delimited values? It's one approach I have seen used before but I wanted to know if something else is faster in run time. Using this way I don't think it is possible to do a direct INNER JOIN on the category table and photo table to get a single matched list of all the photographs per category. Ex: Summer shots - sunshine.jpg, cloudy.jpg, hazy.jpg because it was matched against 1,2,4 The iteration through all the categories and then the photos will have a O(n^2) and there has to be a better/faster way. Please educate me :)

  Read the article

• Multi-Column Join in Hibernate/JPA Annotations

  - by bowsie

  I have two entities which I would like to join through multiple columns. These columns are shared by an @Embeddable object that is shared by both entities. In the example below, Foo can have only one Bar but Bar can have multiple Foos (where AnEmbeddableObject is a unique key for Bar). Here is an example: @Entity @Table(name = "foo") public class Foo { @Id @Column(name = "id") @GeneratedValue(generator = "seqGen") @SequenceGenerator(name = "seqGen", sequenceName = "FOO_ID_SEQ", allocationSize = 1) private Long id; @Embedded private AnEmbeddableObject anEmbeddableObject; @ManyToOne(targetEntity = Bar.class, fetch = FetchType.LAZY) @JoinColumns( { @JoinColumn(name = "column_1", referencedColumnName = "column_1"), @JoinColumn(name = "column_2", referencedColumnName = "column_2"), @JoinColumn(name = "column_3", referencedColumnName = "column_3"), @JoinColumn(name = "column_4", referencedColumnName = "column_4") }) private Bar bar; // ... rest of class } And the Bar class: @Entity @Table(name = "bar") public class Bar { @Id @Column(name = "id") @GeneratedValue(generator = "seqGen") @SequenceGenerator(name = "seqGen", sequenceName = "BAR_ID_SEQ", allocationSize = 1) private Long id; @Embedded private AnEmbeddableObject anEmbeddableObject; // ... rest of class } Finally the AnEmbeddedObject class: @Embeddable public class AnEmbeddedObject { @Column(name = "column_1") private Long column1; @Column(name = "column_2") private Long column2; @Column(name = "column_3") private Long column3; @Column(name = "column_4") private Long column4; // ... rest of class } Obviously the schema is poorly normalised, it is a restriction that AnEmbeddedObject's fields are repeated in each table. The problem I have is that I receive this error when I try to start up Hibernate: org.hibernate.AnnotationException: referencedColumnNames(column_1, column_2, column_3, column_4) of Foo.bar referencing Bar not mapped to a single property I have tried marking the JoinColumns are not insertable and updatable, but with no luck. Is there a way to express this with Hibernate/JPA annotations? Thank you.

  Read the article

• Performance of Multiple Joins

  - by geeko

  Greetings Overflowers, I need to query against objects with many/complex spacial conditions. In relational databases that is translated to many joins (possibly 10+). I'm new to this business and wondering whether to go with MS SQL Server 2008 R2 or Oracle 11g or document-based solutions such as RavenDB or simply go with some spacial database (GIS)... Any thoughts ? Regards UPDATE: Thank you all for your answers. Would anybody opt for document/spatial databases ? My database would consist of tens of millions to few billion records. Mostly read-only. Almost no updates unless in case of mistakes in input. Overnight inserts and not that frequent. The join tables are predicted beforehand but the number of self joins (tables joining themselves multiple times) is not. Small pages of results from such queries are going to be viewed on an highly interactive website so response time is critical. Any predictions on how this can perform on MS SQL Server 2008 R2 or Oracle 11g ? I'm also concerned about boosting performance by adding more servers, which one scales better ? How about PostgresQL ?

  Read the article

• Non standard interaction among two tables to avoid very large merge

  - by riko

  Suppose I have two tables A and B. Table A has a multi-level index (a, b) and one column (ts). b determines univocally ts. A = pd.DataFrame( [('a', 'x', 4), ('a', 'y', 6), ('a', 'z', 5), ('b', 'x', 4), ('b', 'z', 5), ('c', 'y', 6)], columns=['a', 'b', 'ts']).set_index(['a', 'b']) AA = A.reset_index() Table B is another one-column (ts) table with non-unique index (a). The ts's are sorted "inside" each group, i.e., B.ix[x] is sorted for each x. Moreover, there is always a value in B.ix[x] that is greater than or equal to the values in A. B = pd.DataFrame( dict(a=list('aaaaabbcccccc'), ts=[1, 2, 4, 5, 7, 7, 8, 1, 2, 4, 5, 8, 9])).set_index('a') The semantics in this is that B contains observations of occurrences of an event of type indicated by the index. I would like to find from B the timestamp of the first occurrence of each event type after the timestamp specified in A for each value of b. In other words, I would like to get a table with the same shape of A, that instead of ts contains the "minimum value occurring after ts" as specified by table B. So, my goal would be: C: ('a', 'x') 4 ('a', 'y') 7 ('a', 'z') 5 ('b', 'x') 7 ('b', 'z') 7 ('c', 'y') 8 I have some working code, but is terribly slow. C = AA.apply(lambda row: ( row[0], row[1], B.ix[row[0]].irow(np.searchsorted(B.ts[row[0]], row[2]))), axis=1).set_index(['a', 'b']) Profiling shows the culprit is obviously B.ix[row[0]].irow(np.searchsorted(B.ts[row[0]], row[2]))). However, standard solutions using merge/join would take too much RAM in the long run. Consider that now I have 1000 a's, assume constant the average number of b's per a (probably 100-200), and consider that the number of observations per a is probably in the order of 300. In production I will have 1000 more a's. 1,000,000 x 200 x 300 = 60,000,000,000 rows may be a bit too much to keep in RAM, especially considering that the data I need is perfectly described by a C like the one I discussed above. How would I improve the performance?

  Read the article

• Combining two-part SQL query into one query

  - by user332523

  Hello, I have a SQL query that I'm currently solving by doing two queries. I am wondering if there is a way to do it in a single query that makes it more efficient. Consider two tables: Transaction_Entries table and Transactions, each one defined below: Transactions - id - reference_number (varchar) Transaction_Entries - id - account_id - transaction_id (references Transactions table) Notes: There are multiple transaction entries per transaction. Some transactions are related, and will have the same reference_number string. To get all transaction entries for Account X, then I would do SELECT E.*, T.reference_number FROM Transaction_Entries E JOIN Transactions T ON (E.transaction_id=T.id) where E.account_id = X The next part is the hard part. I want to find all related transactions, regardless of the account id. First I make a list of all the unique reference numbers I found in the previous result set. Then for each one, I can query all the transactions that have that reference number. Assume that I hold all the rows from the previous query in PreviousResultSet UniqueReferenceNumbers = GetUniqueReferenceNumbers(PreviousResultSet) // in Java foreach R in UniqueReferenceNumbers // in Java SELECT * FROM Transaction_Entries where transaction_id IN (SELECT * FROM Transactions WHERE reference_number=R Any suggestions how I can put this into a single efficient query?

  Read the article

• NHibernate many-to-many relationship does not update join-table

  - by Davide Vosti

  I'm having trouble saving a many-to-many relationship with nhibernate. I searched and looked at almost every same question in SO and google but nothing works. The single classes are update correctly but nothing is inserted in the join-table. I have 2 classes: Event and Category. The mapping is this: Event: <bag name="Categories" access="field.camelcase-underscore" table="EventCategories" inverse="true" cascade="all-delete-orphan" lazy="true"> <key column="Event"/> <many-to-many class="Category" column="Category"/> </bag> Category: <bag name="Events" table="EventCategories" access="field.camelcase-underscore" cascade="all-delete-orphan" lazy="true" > <key column="Category"/> <many-to-many class="Event" column="Event"/> </bag> I tried to swap every parameter in both mappings but nothing works... Do you know what I'm doing wrong or have a right mapping for this case? Thank you very much!

  Read the article

• How to exclude rows where matching join is in an SQL tree

  - by Greg K

  Sorry for the poor title, I couldn't think how to concisely describe this problem. I have a set of items that should have a 1-to-1 relationship with an attribute. I have a query to return those rows where the data is wrong and this relationship has been broken (1-to-many). I'm gathering these rows to fix them and restore this 1-to-1 relationship. This is a theoretical simplification of my actual problem but I'll post example table schema here as it was requested. item table: +------------+------------+-----------+ | item_id | name | attr_id | +------------+------------+-----------+ | 1 | BMW 320d | 20 | | 1 | BMW 320d | 21 | | 2 | BMW 335i | 23 | | 2 | BMW 335i | 34 | +------------+------------+-----------+ attribute table: +---------+-----------------+------------+ | attr_id | value | parent_id | +---------+-----------------+------------+ | 20 | SE | 21 | | 21 | M Sport | 0 | | 23 | AC | 24 | | 24 | Climate control | 0 | .... | 34 | Leather seats | 0 | +---------+-----------------+------------+ A simple query to return items with more than one attribute. SELECT item_id, COUNT(DISTINCT(attr_id)) AS attributes FROM item GROUP BY item_id HAVING attributes > 1 This gets me a result set like so: +-----------+------------+ | item_id | attributes | +-----------+------------+ | 1 | 2 | | 2 | 2 | | 3 | 2 | -- etc. -- However, there's an exception. The attribute table can hold a tree structure, via parent links in the table. For certain rows, parent_id can hold the ID of another attribute. There's only one level to this tree. Example: +---------+-----------------+------------+ | attr_id | value | parent_id | +---------+-----------------+------------+ | 20 | SE | 21 | | 21 | M Sport | 0 | .... I do not want to retrieve items in my original query where, for a pair of associated attributes, they related like attributes 20 & 21. I do want to retrieve items where: the attributes have no parent for two or more attributes they are not related (e.g. attributes 23 & 34) Example result desired, just the item ID: +------------+ | item_id | +------------+ | 2 | +------------+ How can I join against attributes from items and exclude these rows? Do I use a temporary table or can I achieve this from a single query? Thanks.

  Read the article

• How to write a JOIN statement to combine data from disparate tables

  - by Amarundo

  I have the following 2 procedures that I use as my source for a report. As of now, I'm presenting 2 different tables in my SQL Server Reporting Services 2008 R2 report, because it doesn't let me put them together as they belong to 2 different data sets. I want to present them in a single table, but I have not been successful trying to use JOIN here. How do I do that? NOTE: cName in IAgentQueueStats corresponds to UserId in AgentActivityLog. /*** Aggregate values for Call Center Agents for calls, talk and hold time ***/ /*** The detail/row values is per 30-minute interval ***/ ALTER PROCEDURE [dbo].[sp_IAgentQueueStats_OnlyCalls_Grouped] @p_StartDate datetime, @p_EndDate datetime, @p_Agents varchar(8000) AS SELECT [cName] ,sum([nAnswered]) SumNAnswered ,sum([nAnsweredAcd]) SumNAnsweredAcd ,sum([tTalkAcd]) SumTTalkAcd ,sum([nHoldAcd]) SumNHoldAcd ,sum([tHoldAcd]) SumTHoldAcd ,sum([tAcw]) SumTAcw FROM [I3_IC].[dbo].[IAgentQueueStats] WHERE dIntervalStart between @p_StartDate and DATEADD(s, 86400-1, @p_EndDate) AND CHARINDEX ( cName ,@p_Agents)> 0 AND cReportGroup <> '*' AND cHKey3 = '*' and cHKey4 ='*' AND nEnteredAcd > 0 AND cReportGroup <> 'CCFax Email' GROUP BY cName And here is the second one: /*** Aggregate values for Call Center Agents for status/activity time ***/ /*** The detail/row values is per start-time/end-time ***/ ALTER PROCEDURE [dbo].[sp_AgentActivity_Grouped] @p_StartDate datetime, @p_EndDate datetime, @p_Agents varchar(8000) AS SELECT [UserId],[StatusCategory],SUM([StateDuration]) [StatusDuration] FROM ( SELECT [UserId] ,[StatusGroup] ,[StatusKey] , CASE [StatusKey] WHEN 'Available' THEN 'Productive' WHEN 'Follow Up' THEN 'Productive' WHEN 'Campaign Call' THEN 'Productive' WHEN 'Awaiting Callback' THEN 'Productive' WHEN 'In a Meeting' THEN 'Not Your Fault' WHEN 'Project Work' THEN 'Not Your Fault' WHEN 'At a Training Session'THEN 'Not Your Fault' WHEN 'System Issues' THEN 'Not Your Fault' WHEN 'Test' THEN 'Not Your Fault' WHEN 'At Lunch' THEN 'Non Productive' WHEN 'Available, Forward' THEN 'Non Productive' WHEN 'Available, Follow-Me' THEN 'Non Productive' WHEN 'At Play' THEN 'Non Productive' WHEN 'AcdAgentNotAnswering' THEN 'Non Productive' WHEN 'Do Not Disturb' THEN 'Non Productive' WHEN 'Available, No ACD' THEN 'Non Productive' WHEN 'Away from desk' THEN 'Non Productive' ELSE [StatusKey] END StatusCategory ,stateduration FROM [I3_IC].[dbo].[AgentActivityLog] WHERE [StatusDateTime] between @p_StartDate and DATEADD(s, 86400-1, @p_EndDate) AND CHARINDEX ( [UserId] ,@p_Agents)> 0 AND [StatusKey] not in ('Gone Home','Out of the Office','On Vacation','Out of Town') ) a GROUP BY [UserId],[StatusCategory] ORDER BY [UserId], [StatusCategory] desc BTW, if I take some time to comment/reply on your posts, it's not lack of interest, but of understanding...

  Read the article

• Linq, should I join those two queries together?

  - by 5YrsLaterDBA

  I have a Logins table which records when user is login, logout or loginFailed and its timestamp. Now I want to get the list of loginFailed after last login and the loginFailed happened within 24 hrs. What I am doing now is get the last login timestamp first. then use second query to get the final list. do you think I should join those two queris together? why not? why yes? var lastLoginTime = (from inRecord in db.Logins where inRecord.Users.UserId == userId && inRecord.Action == "I" orderby inRecord.Timestamp descending select inRecord.Timestamp).Take(1); if (lastLoginTime.Count() == 1) { DateTime lastInTime = (DateTime)lastLoginTime.First(); DateTime since = DateTime.Now.AddHours(-24); String actionStr = "F"; var records = from record in db.Logins where record.Users.UserId == userId && record.Timestamp >= since && record.Action == actionStr && record.Timestamp > lastInTime orderby record.Timestamp select record; }

  Read the article

• Populate JOIN into a list in one database query

  - by axio

  I am trying to get the records from the 'many' table of a one-to-many relationship and add them as a list to the relevant record from the 'one' table. I am also trying to do this in a single database request. Code derived from http://stackoverflow.com/questions/1580199/linq-to-sql-populate-join-result-into-a-list almost achieves the intended result, but makes one database request per entry in the 'one' table which is unacceptable. That failing code is here: var res = from variable in _dc.GetTable<VARIABLE>() select new { x = variable, y = variable.VARIABLE_VALUEs }; However if I do a similar query but loop through all the results, then only a single database request is made. This code achieves all goals: var res = from variable in _dc.GetTable<VARIABLE>() select variable; List<GDO.Variable> output = new List<GDO.Variable>(); foreach (var v2 in res) { List<GDO.VariableValue> values = new List<GDO.VariableValue>(); foreach (var vv in v2.VARIABLE_VALUEs) { values.Add(VariableValue.EntityToGDO(vv)); } output.Add(EntityToGDO(v2)); output[output.Count - 1].VariableValues = values; } However the latter code is ugly as hell, and it really feels like something that should be do-able in a single linq query. So, how can this be done in a single linq query that makes only a single database query? In both cases the table is set to preload using the following code: _dc = _db.CreateLinqDataContext(); var loadOptions = new DataLoadOptions(); loadOptions.LoadWith<VARIABLE>(v => v.VARIABLE_VALUEs); _dc.LoadOptions = loadOptions; I am using .NET 3.5, and the database back-end was generated using SqlMetal.

  Read the article

• Codeigniter - Active record - sql - complex join

  - by Jack

  I have a function that retrieves all tags from a table: function global_popular_tags() { $this->db->select('tags.*, COUNT(tags.id) AS count'); $this->db->from('tags'); $this->db->join('tags_to_work', 'tags.id = tags_to_work.tag_id'); $this->db->group_by('tags.id'); $this->db->order_by('count', 'desc'); $query = $this->db->get()->result_array(); return $query; } I have another table called 'work'. The 'work' table has a 'draft' column with values of either 1 or 0. I want the COUNT(tags.id) to take into account whether the work with the specific tag is in draft mode (1) or not. Say there are 10 pieces of work tagged with, for example, 'design'. The COUNT will be 10. But 2 of these pieces of work are in draft mode, so the COUNT should really be 8. How do I manage this?

  Read the article

• INSERT INTO ...SELECT syntax error in join operator

  - by user1477356

  I'm trying to write a shopping basket into a order + orderline in a sql database from C# asp.net. the orderline will contain a ordernumber, total price, productid, quantity etc. for every item in the basket. The order itself will contain the ordernumber as primary key and will be linked to the different lines through it. Everything worked fine yesterday, but now as i tried to use a SELECT command in the insert into statement to get things more dynamic i'm getting the above described syntax error. Does anybody know what's wrong with this statement: INSERT INTO [order] (klant_id,totaalprijs,btw,subtotaal,verzendkosten) SELECT klant.id , SUM(orderregel.totaalprijs) , SUM(orderregel.btw) , SUM(orderregel.totaalprijs) - SUM(orderregel.btw) , 7.50 FROM orderregel INNER JOIN klant ON [order].klant_id = klant.id WHERE klant.username = 'jerry' GROUP BY id; the ordernumber in the "order" table is on autonumber, in the asp codebehind there is a for each which handles the lines being written for every product, there's an index set on 0 outside of this loop and is heightened with 1 every end of it. The executenonquery of the order is only executed once at the beginning of the first loop and the lines are added after with MAX(ordernumber) as ordernumber. I hope i have provided enough information and somebody is capable of helping me. Thanks in advance!

  Read the article

• Linq-to-sql join/where?

  - by Curtis White

  I've the following table structures Users id Types id isBool UsersTypes userid types I want to select all the UserTypes based on id and isBool. I tried this query var q = from usertype in usertypes from type in types where type.isBool == false where userstypes.user == id select usertype; But this did not work as expected. My questions are: Why? Is there any difference in using the join on syntax vs where, where vs where cond1 && cond2? My understanding is query optimizer will optimize. Is there any difference in using where cond1 == var1 && cond2 == var2 with and without the parenthesis? This seems peculiar that it is possible to build this without parenthesis What type of query do I need in this case? I can see that I could do a subquery or use a group but not 100% sure if it is required. An example might be helpful. I'm thinking a subquery may be required in this case.

  Read the article

• Trying to join two independent forms

  - by user248959

  Hi, i'm trying to join two independent forms (login and register) in the same page. My idea is (just looking at the signin form): Create an action that shows both forms (partials): public function executeLoginAndRegister(sfWebRequest $request){ $this->form_signin = $this->getUser()->getAttribute('form_signin'); } Each partial calls to its action: form action="php? echo url_for('@sf_guard_signin') ?" method="post" In the actions i write this code public function executeSignin($request) { //... $this->form = new $MyFormclass(); if ($this->form->isValid()) { //... }else{ // save the form to show the error messages. $this-&gt;getUser()-&gt;setAttribute('form_signin', $this-&gt;form); return $this-&gt;forward('sfGuardAuth', 'loginAndRegister'); } } It works, but, for example, if i execute LoginAndRegister and submit incorrectly the signin form and I go to another page and then return to LoginAndRegister, i will find the submiting error messages... If i execute LoginAndRegister and submit incorrectly the signin form and open another browser tab, i will find the submiting error messages in the signin form of the second tab... Any idea? any better approach?

  Read the article

• Select Query Joined on Two Fields?

  - by btollett

  I've got a few tables in an access database: ID | LocationName 1 | Location1 2 | Location2 ID | LocationID | Date | NumProductsDelivered 1 | 1 | 12/10 | 3 2 | 1 | 01/11 | 2 3 | 1 | 02/11 | 2 4 | 2 | 11/10 | 1 5 | 2 | 12/10 | 1 ID | LocationID | Date | NumEmployees | EmployeeType 1 | 1 | 12/10 | 10 | 1 (=Permanent) 2 | 1 | 12/10 | 3 | 2 (=Temporary) 3 | 1 | 12/10 | 1 | 3 (=Support) 4 | 2 | 10/10 | 1 | 1 5 | 2 | 11/10 | 2 | 1 6 | 2 | 11/10 | 1 | 2 7 | 2 | 11/10 | 1 | 3 8 | 2 | 12/10 | 2 | 1 9 | 2 | 12/10 | 1 | 3 What I want to do is pass in the LocationID as a parameter and get back something like the following table. So, if I pass in 2 as my LocationID, I should get: Date | NumProductsDelivered | NumPermanentEmployees | NumSupportEmployees 10/10 | | 1 | 11/10 | 1 | 2 | 1 12/10 | 1 | 2 | 1 It seems like this should be a pretty simple query. I really don't even need the first table except as a way to fill in the combo box on the form from which the user chooses which location they want a report for. Unfortunately, everything I've done has resulted in me getting a lot more data than I should be getting. My confusion is in how to set up the join (presumably that's what I'm looking for here) given that I want both the date and locationID to be the same for each row in the result set. Any help would be much appreciated. Thanks.

  Read the article

• SQL inner join from field defined table?

  - by Wolftousen

  I have a, currently, a total of 6 tables that are part of this question. The primary table, tableA, contains columns that all the entries in the other 5 tables have in common. The other 5 tables have columns which define the entry in tableA in more detail. For example: TableA ID|Name|Volumn|Weight|Description 0 |T1 |0.4 |0.1 |Random text 1 |R1 |5.3 |25 |Random text TableB ID|Color|Shape 0 |Blue |Sphere TableC ID|Direction|Velocity 1 |North |3.4 (column names are just examples don't take them for what they mean...) The ID field in Table A is unique to all other tables (i.e. TableB will have 0, but TableC will not, nor any other Tables). What I would like to do is select all the fields from TableA and the corresponding (according to ID field) detail Table (TableB-F). What I have currently done and not tested is added a field to TableA so it looks like this: TableA ID|Name|Volumn|Weight|Description|Table 0 |T1 |0.4 |0.1 |Random text|TableB 1 |R1 |5.3 |25 |Random text|TableC I have a few questions about this: 1.Is it proper to do such a thing to TableA, as foreign keys wont work in this situation since they all need to link to different tables? 2.If this is proper, would the SQL query look like this (ID would be input by the user)? SELECT * FROM TableA AS a INNER JOIN a.Table AS t ON a.ID = ID; 3.Is there a better way to do this? Thanks for the help.

  Read the article

• has_many through and saving to join table

  - by Paul

  I have the following: class Invite < ActiveRecord::Base belongs_to :user has_many :invite_recipients has_many :recipients, :through => :invite_recipients end class InviteRecipient < ActiveRecord::Base belongs_to :invite belongs_to :user_comm validates_associated :user_comm, :invite validates_uniqueness_of :user_comm_id, :scope => :invite_id end class UserComm < ActiveRecord::Base end I'd like to create a method for Invite with invite_text and a list of UserComms as the variables and then have it create a new invite with the following validations: 1. All UserComms are unique 2. The invite isn't saved unless all the associated InviteRecipients are saved as well (in other words, the invite isn't valid unless all the created InviteRecipients are valid) I'm not familiar with how to create model functions. Moreover, when I try something like this: i = Invite.new(:invite_text => 'come join') ir1 = InviteRecipient.new(:invite => i, :user_comm => user_comm1) ir2 = InviteRecipient.new(:invite => i, :user_comm => user_comm2) i.invite_recipients = [uc1, uc2] i.save! I get: SystemStackError: stack level too deep

  Read the article

• Select query 2-3 times faster than view

  - by Richard Knop

  This query run alone: SELECT -- lots of columns FROM (((((((((((`table1` `t1` LEFT JOIN `table2` `t2` ON(( `t2`.`userid` = `t1`.`userid` ))) LEFT JOIN `table3` `t3` ON(( `t1`.`orderid` = `t3`.`orderid` ))) LEFT JOIN `table4` `t4` ON(( `t4`.`orderitemlicenseid` = `t3`.`orderitemlicenseid` ))) LEFT JOIN `table5` `t5` ON(( `t1`.`orderid` = `t5`.`orderid` ))) LEFT JOIN `table6` `t6` ON(( `t5`.`transactionid` = `t6`.`transactionid` ))) LEFT JOIN `table7` `t7` ON(( `t7`.`transactionid` = `t5`.`transactionid` ))) LEFT JOIN `table8` `t8` ON(( `t8`.`voucherid` = `t7`.`voucherid` ))) LEFT JOIN `table9` `t9` ON(( `t8`.`voucherid` = `t9`.`voucherid` ))) LEFT JOIN `table10` `t10` ON(( ( `t10`.`vouchergroupid` = `t9`.`vouchergroupid` ) AND ( `t2`.`territoryid` = `t10`.`territoryid` ) ))) LEFT JOIN `table11` `t11` ON(( `t11`.`voucherid` = `t8`.`voucherid` ))) LEFT JOIN `table12` `t12` ON(( `t12`.`orderid` = `t1`.`orderid` ))) GROUP BY `t5`.`transactionid` Takes about 2.5 seconds to finish. When I save it to a view and run it as: SELECT * FROM viewName; It takes 7 seconds to finish. What is the reason and how can I make the view faster?

  Read the article

• Is there simple way how to join two RouteValueDictionary values to pass parameters to Html.ActionLin

  - by atagaew

  Hi. Look on my code that i created in a partial View: <% foreach (Customer customerInfo in Model.DataRows) {%> <tr> <td> <%=Html.ActionLink( customerInfo.FullName , ((string)ViewData["ActionNameForSelectedCustomer"]) , JoinParameters(customerInfo.id, (RouteValueDictionary) ViewData["AdditionalSelectionParameters"]) , null)%> </td> <td> <%=customerInfo.LegalGuardianName %> </td> <td> <%=customerInfo.HomePhone %> </td> <td> <%=customerInfo.CellPhone %> </td> </tr> <%}%> Here I'm building simple table that showing customer's details. As you may see, in each row, I'm trying to build a link that will redirect to another action. That action requires customerId and some additional parameters. Additional parameters are different for each page where this partial View is using. So, i decided to make Action methods to pass that additional parameters in the ViewData as RouteValueDictionary instance. Now, on the view i have a problem, i need to pass customerId and that RouteValueDictionary together into Html.ActionLink method. That makes me to figure out some way of how to combine all that params into one object (either object or new RouteValueDictionary instance) Because of the way the MVC does, i can't create create a method in the codebehind class (there is no codebihind in MVC) that will join that parameters. So, i used ugly way - inserted inline code: ...script runat="server"... private RouteValueDictionary JoinParameters(int customerId, RouteValueDictionary defaultValues) { RouteValueDictionary routeValueDictionary = new RouteValueDictionary(defaultValues); routeValueDictionary.Add("customerId", customerId); return routeValueDictionary; } ...script... This way is very ugly for me, because i hate to use inline code in the View part. My question is - is there any better way of how i can mix parameters passed from the action (in ViewData, TempData, other...) and the parameter from the view when building action links. May be i can build this link in other way ? Thanks!

  Read the article

• (mySQL) Unable to query 2 tables properly for data

  - by Devner

  I have 2 tables. One is 'page_links' and the other is 'rpp'. Table page_links is the superset of table rpp. The following is the schema of my tables: -- Table structure for table `page_links` -- CREATE TABLE IF NOT EXISTS `page_links` ( `page` varchar(255) NOT NULL, `page_link` varchar(100) NOT NULL, `heading_id` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`page`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `page_links` -- INSERT INTO `page_links` (`page`, `page_link`, `heading_id`) VALUES ('a1.php', 'A1', 8), ('b1.php', 'B1', 8), ('c1.php', 'C1', 5), ('d1.php', 'D1', 5), ('e1.php', 'E1', 8), ('f1.php', 'F1', 8), ('g1.php', 'G1', 8), ('h1.php', 'H1', 1), ('i1.php', 'I1', 1), ('j1.php', 'J1', 8), ('k1.php', 'K1', 8), ('l1.php', 'L1', 8), ('m1.php', 'M1', 8), ('n1.php', 'N1', 8), ('o1.php', 'O1', 8), ('p1.php', 'P1', 4), ('q1.php', 'Q1', 5), ('r1.php', 'R1', 4); -- Table structure for table `rpp` -- CREATE TABLE IF NOT EXISTS `rpp` ( `role_id` tinyint(3) unsigned NOT NULL, `page` varchar(255) NOT NULL, `is_allowed` tinyint(1) NOT NULL, PRIMARY KEY (`role_id`,`page`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `rpp` -- INSERT INTO `rpp` (`role_id`, `page`, `is_allowed`) VALUES (3, 'a1.php', 1), (3, 'b1.php', 1), (3, 'c1.php', 1), (3, 'd1.php', 1), (3, 'e1.php', 1), (3, 'f1.php', 1), (3, 'h1.php', 1), (3, 'i1.php', 1), (3, 'l1.php', 1), (3, 'm1.php', 1), (3, 'n1.php', 1), (4, 'a1.php', 1), (4, 'b1.php', 1), (4, 'q1.php', 1), (5, 'r1.php', 1); WHAT I AM TRYING TO DO: I am trying to query both the above tables (in a single query) in such a way that all the pages from page_links are displayed along with the is_allowed value from rpp for a particular role. For example, I want to get the is_allowed value of all the pages from rpp for role_id = 3 and at the same time, list all the available pages from page_links. A clear example of my expected result would be: page is_allowed role_id ---------------------------------------- a1.php 1 3 b1.php 1 3 c1.php 1 3 d1.php 1 3 e1.php 1 3 f1.php 1 3 g1.php NULL NULL h1.php 1 3 i1.php 1 3 j1.php NULL NULL k1.php NULL NULL l1.php 1 3 m1.php 1 3 n1.php 1 3 o1.php NULL NULL p1.php NULL NULL q1.php NULL NULL r1.php NULL NULL One more example of my desired result could be achieved by doing a LEFT JOIN rpp ON page_links.page = rpp.page but we need to omit using role_id = 3 (or any value) to be able to get that. But I do want to specify the role_id as well and get the results. I need the query to be able to get this result. I would appreciate any replies that could help me with this. If you can suggest me any changes as well to the table(s) design to be able to achieve the desired result, that's good as well. Thanks in advance.

  Read the article

• What is natural deduction used for outside of academia?

  - by Danny King

  Hello, I am studying natural deduction as a part of my Formal Specification & Verification Computer Science course at University/College. I find it interesting, however I learn much better when I can find a practical use for things. Could anyone explain to me if and how natural deduction is used other than for formally verifying bits of code? Thanks!

  Read the article

• How to select chosen columns from two different entities into one DTO using NHibernate?

  - by Pawel Krakowiak

  I have two classes (just recreating the problem): public class User { public virtual int Id { get; set; } public virtual string FirstName { get; set; } public virtual string LastName { get; set; } public virtual IList<OrgUnitMembership> OrgUnitMemberships { get; set; } } public class OrgUnitMembership { public virtual int UserId { get; set; } public virtual int OrgUnitId { get; set; } public virtual DateTime JoinDate { get; set; } public virtual DateTime LeaveDate { get; set; } } There's a Fluent NHibernate map for both, of course: public class UserMapping : ClassMap<User> { public UserMapping() { Table("Users"); Id(e => e.Id).GeneratedBy.Identity(); Map(e => e.FirstName); Map(e => e.LastName); HasMany(x => x.OrgUnitMemberships) .KeyColumn(TypeReflector<OrgUnitMembership> .GetPropertyName(p => p.UserId))).ReadOnly().Inverse(); } } public class OrgUnitMembershipMapping : ClassMap<OrgUnitMembership> { public OrgUnitMembershipMapping() { Table("OrgUnitMembership"); CompositeId() .KeyProperty(x=>x.UserId) .KeyProperty(x=>x.OrgUnitId); Map(x => x.JoinDate); Map(x => x.LeaveDate); References(oum => oum.OrgUnit) .Column(TypeReflector<OrgUnitMembership> .GetPropertyName(oum => oum.OrgUnitId)).ReadOnly(); References(oum => oum.User) .Column(TypeReflector<OrgUnitMembership> .GetPropertyName(oum => oum.UserId)).ReadOnly(); } } What I want to do is to retrieve some users based on criteria, but I would like to combine all columns from the Users table with some columns from the OrgUnitMemberships table, analogous to a SQL query: select u.*, m.JoinDate, m.LeaveDate from Users u inner join OrgUnitMemberships m on u.Id = m.UserId where m.OrgUnitId = :ouid I am totally lost, I tried many different options. Using a plain SQL query almost works, but because there are some nullable enums in the User class AliasToBean fails to transform, otherwise wrapping a SQL query would work like this: return Session .CreateSQLQuery(sql) .SetParameter("ouid", orgUnitId) .SetResultTransformer(Transformers.AliasToBean<UserDTO>()) .List<UserDTO>() I tried the code below as a test (a few different variants), but I'm not sure what I'm doing. It works partially, I get instances of UserDTO back, the properties coming from OrgUnitMembership (dates) are filled, but all properties from User are null: User user = null; OrgUnitMembership membership = null; UserDTO dto = null; var users = Session.QueryOver(() => user) .SelectList(list => list .Select(() => user.Id) .Select(() => user.FirstName) .Select(() => user.LastName)) .JoinAlias(u => u.OrgUnitMemberships, () => membership) //.JoinQueryOver<OrgUnitMembership>(u => u.OrgUnitMemberships) .SelectList(list => list .Select(() => membership.JoinDate).WithAlias(() => dto.JoinDate) .Select(() => membership.LeaveDate).WithAlias(() => dto.LeaveDate)) .TransformUsing(Transformers.AliasToBean<UserDTO>()) .List<UserDTO>();

  Read the article

• Is there an high quality natural text reader for the mac?

  - by Another Registered User

  I'm reading about 150 pages of text on screen, every day. I will have to read about 15.000 in the next upcoming months. No joke. Well, the problem is this: I suffer from a sort of attention deficit hyperactivity disorder which forces me to read every sentence up to 10 times until I really get it. Mac OS X Snow Leopard has a built-in text reader with the name "Alex". Although it is already pretty good quality, I know there are far better natural sounding voices out there. I have heard already voices that are absolutely amazing compared to Alex. They're so good, that you can't tell anymore the difference between a real person or a computer. Alex still has this "metal factor" in its voice, which makes my ears hurt after 8 hours of listening. The next problem with Alex is, that he never makes a break after a sentence. Also, it's not possible to think about a sentence and then continue reading. It's also not possible to have him repeat a sentence, without tedious text selection and shortcut usage. Actually, the best tool I can imagine would have the option to read a sentence and move on to the next one after pressing a special key, OR repeating the previously one after pressing a special key. That would help so much! And if that's even with one of those bell lab / AT&T / whatever super-natural voices, even better! But it would be already a great relief if there was just a better tool to control Alex. To let him make breaks after sentences or let him speak big chunks of text sentence-by-sentence with fine-grained control over repetition and moving on. Is there anything?

  Read the article

• SQLAuthority News – Whitepaper Download – Using Star Join and Few-Outer-Row Optimizations to Improve Data Warehousing Queries

  - by pinaldave

  Size of the database is growing every day. Many organizations now a days have more than TB of the Data in their system. Performance is always part of the issue. Microsoft is really paying attention to the same and also focusing on improving performance for Data Warehousing. Microsoft has recently released whitepaper on the performance tuning subject of Data Warehousing. Here is the abstract about the whitepaper from official site: In this white paper we discuss two of the new features introduced in SQL Server 2008, Star Join and Few-Outer-Row optimizations. These two features are in SQL Server 2008 R2 as well.  We test the performance of SQL Server 2008 on a set of complex data warehouse queries designed to highlight the effect of these two features and observed a significant performance gain over SQL Server 2005 (without these two features). The results observed also apply to SQL Server 2008 R2.  On average, about 75 percent of the query execution time has been reduced, compared to SQL Server 2005. We also include data that shows a reduction in the number of rows processed and improved balance in parallel queries, both of which highlight the important role the Star Join and Few Outer-Row features played. I encouraged all of those interested in Data Warehouse to read it and see if they can learn the tricks. Using Star Join and Few-Outer-Row Optimizations to Improve Data Warehousing Queries Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Documentation, SQL Download, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority News, T SQL, Technology

  Read the article

• How Do You Attain Permanent Results in Natural Search Engine Listings?

  SEO expert is your apt professional to help you attain search engine traffic through organic methods. Organic results last longer unlike the non organic methods. Search engine optimisation expert with his rich online experience will provide you the right strategies to promote your north east web portal.

  Read the article

< Previous Page | 38 39 40 41 42 43 44 45 46 47 48 49  | Next Page >