Search Results

Search found 8547 results on 342 pages for 'hash join'.

Page 61/342 | < Previous Page | 57 58 59 60 61 62 63 64 65 66 67 68  | Next Page >

• Minimizing SQL queries using join with one-to-many relationship

  - by Brian

  So let me preface this by saying that I'm not an SQL wizard by any means. What I want to do is simple as a concept, but has presented me with a small challenge when trying to minimize the amount of database queries I'm performing. Let's say I have a table of departments. Within each department is a list of employees. What is the most efficient way of listing all the departments and which employees are in each department. So for example if I have a department table with: id name 1 sales 2 marketing And a people table with: id department_id name 1 1 Tom 2 1 Bill 3 2 Jessica 4 1 Rachel 5 2 John What is the best way list all departments and all employees for each department like so: Sales Tom Bill Rachel Marketing Jessica John Pretend both tables are actually massive. (I want to avoid getting a list of departments, and then looping through the result and doing an individual query for each department). Think similarly of selecting the statuses/comments in a Facebook-like system, when statuses and comments are stored in separate tables.

  Read the article

• Nhibernate join on a table twice

  - by Zuber

  Consider the following Class structure... public class ListViewControl { public int SystemId {get; set;} public List<ControlAction> Actions {get; set;} public List<ControlAction> ListViewActions {get; set;} } public class ControlAction { public string blahBlah {get; set;} } I want to load class ListViewControl eagerly using NHibernate. The mapping using Fluent is as shown below public UIControlMap() { Id(x => x.SystemId); HasMany(x => x.Actions) .KeyColumn("ActionId") .Cascade.AllDeleteOrphan() .AsBag() .Cache.ReadWrite().IncludeAll(); HasMany(x => x.ListViewActions) .KeyColumn("ListViewActionId") .Cascade.AllDeleteOrphan() .AsBag() .Cache.ReadWrite().IncludeAll(); } This is how I am trying to load it eagerly var baseActions = DetachedCriteria.For<ListViewControl>() .CreateCriteria("Actions", JoinType.InnerJoin) .SetFetchMode("BlahBlah", FetchMode.Eager) .SetResultTransformer(new DistinctRootEntityResultTransformer()); var listViewActions = DetachedCriteria.For<ListViewControl>() .CreateCriteria("ListViewActions", JoinType.InnerJoin) .SetFetchMode("BlahBlah", FetchMode.Eager) .SetResultTransformer(new DistinctRootEntityResultTransformer()); var listViews = DetachedCriteria.For<ListViewControl>() .SetFetchMode("Actions", FetchMode.Eager) .SetFetchMode("ListViewActions",FetchMode.Eager) .SetResultTransformer(new DistinctRootEntityResultTransformer()); var result = _session.CreateMultiCriteria() .Add("listViewActions", listViewActions) .Add("baseActions", baseActions) .Add("listViews", listViews) .SetResultTransformer(new DistinctRootEntityResultTransformer()) .GetResult("listViews"); Now, my problem is that the class ListViewControl get the correct records in both Actions and ListViewActions, but there are multiple entries of the same record. The number of records is equal to the number of joins made to the ControlAction table, in this case two. How can I avoid this? If I remove the SetFetchMode from the listViews query, the actions are loaded lazily through a proxy which I don't want.

  Read the article

• Reducing Time Complexity in Java

  - by Koeneuze

  Right, this is from an older exam which i'm using to prepare my own exam in january. We are given the following method: public static void Oorspronkelijk() { String bs = "Dit is een boodschap aan de wereld"; int max = -1; char let = '*'; for (int i=0;i<bs.length();i++) { int tel = 1; for (int j=i+1;j<bs.length();j++) { if (bs.charAt(j) == bs.charAt(i)) tel++; } if (tel > max) { max = tel; let = bs.charAt(i); } } System.out.println(max + " keer " + let); } The questions are: what is the output? - Since the code is just an algorithm to determine the most occuring character, the output is "6 keer " (6 times space) What is the time complexity of this code? Fairly sure it's O(n²), unless someone thinks otherwise? Can you reduce the time complexity, and if so, how? Well, you can. I've received some help already and managed to get the following code: public static void Nieuw() { String bs = "Dit is een boodschap aan de wereld"; HashMap<Character, Integer> letters = new HashMap<Character, Integer>(); char max = bs.charAt(0); for (int i=0;i<bs.length();i++) { char let = bs.charAt(i); if(!letters.containsKey(let)) { letters.put(let,0); } int tel = letters.get(let)+1; letters.put(let,tel); if(letters.get(max)<tel) { max = let; } } System.out.println(letters.get(max) + " keer " + max); } However, I'm uncertain of the time complexity of this new code: Is it O(n) because you only use one for-loop, or does the fact we require the use of the HashMap's get methods make it O(n log n) ? And if someone knows an even better way of reducing the time complexity, please do tell! :)

  Read the article

• limiting consecutive values of one column in a sql query.

  - by Brad Smith

  I have a table with the following structure: id -int(11) event_id -int(11) photo_id -int(11) created_at -datetime How do I write a query that will return the 100 most recent rows, but insuring that there are no more than 4 consecutive rows with the same value in photo_id

  Read the article

• MySQL - optimising selection across two linked tables

  - by user293594

  I have two MySQL tables, states and trans: states (200,000 entries) looks like: id (INT) - also the primary key energy (DOUBLE) [other stuff] trans (14,000,000 entries) looks like: i (INT) - a foreign key referencing states.id j (INT) - a foreign key referencing states.id A (DOUBLE) I'd like to search for all entries in trans with trans.A 30. (say), and then return the energy entries from the (unique) states referenced by each matching entry. So I do it with two intermediate tables: CREATE TABLE ij SELECT i,j FROM trans WHERE A30.; CREATE TABLE temp SELECT DISTINCT i FROM ij UNION SELECT DISTINCT j FROM ij; SELECT energy from states,temp WHERE id=temp.i; This seems to work, but is there any way to do it without the intermediate tables? When I tried to create the temp table with a single command straight from trans: CREATE TABLE temp SELECT DISTINCT i FROM trans WHERE A30. UNION SELECT DISTINCT j FROM trans WHERE A30.; it took a longer (presumably because it had to search the large trans table twice. I'm new to MySQL and I can't seem to find an equivalent problem and answer out there on the interwebs. Many thanks, Christian

  Read the article

• Freebase Query with "JOINS"

  - by codemonkey

  ... Yeah, yeah, I know traditional joins don't exist. I actually like the freebase query methodology in theory, just having a little trouble getting it to actually work for me : ) Anyone have a dumb-simple example of getting Freebase data via MQL that pulls from two different "tables"? In particular, I'm trying to get automotive data... so for example, pulling fields from both /automotive/model_year and /automotive/trim_year. I've read the documentation (for hours actually). There's a distinct possibility that I'm looking right at such an example somewhere and just not seeing it because my OLTP brain just doesn't comprehend what it's seeing. * Note * ... that the two "types" I'm working with above are siblings, not parent/child. Does freebase even allow joining data between sibling nodes... I see examples of queries pulling from parent/child, but not from siblings I don't think (or I've overlooked them).

  Read the article

• Treat multiple IEnumerable objects as a single IEnumerable

  - by MainMa

  Hi, My question seems to be something easy, but I can't figure it out. Let's say I have a "root" IEnumerable of objects. Each object has IEnumerable of strings. How can I obtain a single IEnumerable of those strings? A possible solution is to do: public IEnumerable<string> DoExample() { foreach (var c in rootSetOfObjects) { foreach (var n in c.childSetOfStrings) { yield return n; } } } But maybe there is a magic solution with Linq?

  Read the article

• Showing all rows for keys with more than one row

  - by Leif Neland

  Table kal id integer primary key init char 4 indexed job char4 id init job --+----+------ 1 | aa | job1 2 | aa | job2 3 | bb | job1 4 | cc | job3 5 | cc | job5 I want to show all rows where init has more than one row id init job --+----+------ 1 | aa | job1 2 | aa | job2 4 | cc | job3 5 | cc | job5 I tried select * from kal where init in (select init from kal group by init having count(init)2); Actually, the table has 60000 rows, and the query was count(init)<40, but it takes humongus time, phpmyadmin and my patience runs out. Both select init from kal group by init having count(init)2) and select * from kal where init in ('aa','bb','cc') runs in "no time", less than 0.02 seconds. I've tried different subqueries, but all takes "infinite" time, more than a few minutes; I've actually never let them finish. Leif

  Read the article

• MongoDB - how to join parent and child products by reference

  - by Jaro

  my mongo collection stores products. There are two product types: child and parent. Parent product holds array of its child as reference. Use case: use mydb; child1 = { _id: 1, name: "Child 1", is_child: true, is_parent: false, children : [] } child2 = { _id: 2, name: "Child 2", is_child: true, is_parent: false, children : [] } parent = { _id: 3, name: "Parent product", is_child: false, is_parent: true, children : [1, 2] } db.product.insert( [child1, child2, parent] ); And I'm looking for any query returning { _id: 3, name: "Parent product", is_child: false, is_parent: true, children: [ { _id: 1, name: "Child 1", is_child: true, is_parent: false, children : [] }, { _id: 2, name: "Child 2", is_child: true, is_parent: false, children : [] } ] } I'm newbie to mongodb, but I guess an usage of map-reduce could solve the problem. Can anyone advice? Thx

  Read the article

• How can I sort a Perl array of array of hashes?

  - by srk

  @aoaoh; $aoaoh[0][0]{21} = 31; $aoaoh[0][0]{22} = 31; $aoaoh[0][0]{23} = 17; for $k (0 .. $#aoaoh) { for $i(0.. $#aoaoh) { for $val (keys %{$aoaoh[$i][$k]}) { print "$val=$aoaoh[$i][$k]{$val}\n"; } } } The output is: 22=31 21=31 23=17 but i expect it to be 21=31 22=31 23=17 Please tell me where is this wrong. Also how do I sort the values so that i get the output as 23=17 22=31 21=31 (if 2 keys have same value then key with higher value come first)

  Read the article

• MySQL query pulling from two tables, display in correct fields

  - by Matt Nathanson

  I'm trying to select all fields in two separate tables as long as they're sharing a common ID. //mysql query $result = mysql_query("SELECT * FROM project, links WHERE project.id = links.id and project.id = $clientID") //displaying the link if ($row['url'] != null){ echo "<div class='clientsection' id='links'>Links</div>"; echo "<a class='clientlink' id='link1' href='" . $row['url'] . "'>" . $row['name'] . "</a>"; } else { echo "<a class='clientlink' id='link1' href='" . $row['url'] . "' style='display:none;'>" . $row['name'] . "</a>"; }; As you can see, my tables are "projects", and "links" Each is sharing a common field "id" for reference. It looks as though where both links.id and project.id are equal, it outputs anything, but when there is no links.id associated with a given $clientID the container relative to the $clientID doesn't display at all. Essentially I'm using this to add links dynamically to a specific client in this CMS and if there are no links, I want the container to show up anyway. Hopefully I've expressed myself clearly, any pointers in the right direction are appreciated. Thanks!

  Read the article

• SQL query through an intermediate table

  - by Bryan

  Given the following tables: Recipes | id | name | 1 | 'chocolate cream pie' | 2 | 'banana cream pie' | 3 | 'chocolate banana surprise' Ingredients | id | name | 1 | 'banana' | 2 | 'cream' | 3 | 'chocolate' RecipeIngredients | recipe_id | ingredient_id | 1 | 2 | 1 | 3 | 2 | 1 | 2 | 2 | 3 | 1 | 3 | 3 How do I construct a SQL query to find recipes where ingredients.name = 'chocolate' and ingredients.name = 'cream'?

  Read the article

• Why this code generates different numbers?

  - by frbry

  Hello, I have this function that creates a unique number for hard-disk and CPU combination. DWORD hw_hash() { char drv[4]; char szNameBuffer[256]; DWORD dwHddUnique; DWORD dwProcessorUnique; DWORD dwUniqueKey; char *sysDrive = getenv ("SystemDrive"); strcpy(drv, sysDrive); drv[2] = '\\'; drv[3] = 0; GetVolumeInformation(drv, szNameBuffer, 256, &dwHddUnique, NULL, NULL, NULL, NULL); SYSTEM_INFO si; GetSystemInfo(&si); dwProcessorUnique = si.dwProcessorType + si.wProcessorArchitecture + si.wProcessorRevision; dwUniqueKey = dwProcessorUnique + dwHddUnique; return dwUniqueKey; } It returns different numbers if I format my hard-disk and install a new Windows. Any ideas, why? Thank you. Edit: OK, Got it: This function returns the volume serial number that the operating system assigns when a hard disk is formatted. To programmatically obtain the hard disk's serial number that the manufacturer assigns, use the Windows Management Instrumentation (WMI) Win32_PhysicalMedia property SerialNumber. I should do more research before posting my problems online. Sorry to bother you, let's keep this here in case anybody else can need it.

  Read the article

• MySQL Select statement Where table1.id != table2.id

  - by Michael

  I have a table of data which has posts, then I have a separate table of data which has deleted posts. What happens when a post is deleted is that it's ID get's added to the deleted table rather than removing the post entry. What is a clean efficient way of selecting all the posts from the posts table without selecting the ones that have their ID in the deleted table

  Read the article

• Inner join on 2 arrays?

  - by russ

  I'm trying to find a solution to the following (I'm thinking with Linq) to the following: I need to pull down certain files from a larger list of files on an ftp server that have a similar file name. For example, we send an order file to some company for processing then they return a response file that we can download. So, I could send them the files "order_123.txt" and "order_456.txt". After a certain amount of time I need to go look for and download the responses for those files that will be named "order_123.resp" and "order_456.resp". The kicker is that in some cases I can have multiple responses in which case they would create "order_123-1.resp" and "order_123-2.resp" and also the files don't get removed from the server. I know this can be accomplished by looping through the files I know I need responses to then loop through all the files on the server until I find files that match but I'm hoping that I don't have to loop through the files on the server more than once. This example may help clarify: I've sent "order_222.txt" and "order_333.txt" they processed them and the ftp server contains: "order_111-1.resp" "order_001.resp" "order_222-1.resp" "order_222-2.resp" "order_333.resp" I need to download the 3rd, 4th, and 5th file. Thanks.

  Read the article

• SQL query to return data from two separate rows in a table joined to a master table

  - by Ali

  I have a TWO tables of data with following fields table1=(ITTAG,ITCODE,ITDESC,SUPcode) table2=(ACCODE,ACNAME,ROUTE,SALMAN) This is my customer master table that contains my customer data such as customer code, customer name and so on... Every Route has a supervisor (table1=supcode) and I need to know the supervisor name in my table which both supervisor name and code exist in one table. table1 has contain all names separated by ITTAG. For example, supervisor's name has ITTAG='K'; also salesman's name has ITTAG='S'. ITTAG ITCODE ITDESC SUPCODE ------ ------ ------ ------- S JT JOHN TOMAS TF K WK VIKI KOO NULL Now this is the result which I want ACCODE ACNAME ROUTE SALEMANNAME SUPERVISORNAME ------- ------ ------ ------------ --------------- IMC1010 ABC HOTEL 01 JOHN TOMAS VIKI KOO I hope this this information is sufficient to get the query..

  Read the article

• SQL How to join multiplue columns with same name to one column

  - by Choi Shun Chi

  There is a super class account {User, TYPE} and subclasses saving{User, ID, balance,TYPE,interest,curency_TYPE} time{User,ID,balance,TYPE,interest,curency_TYPE,start_date,due_date,period} fore{User,ID,balance,interest,curency_TYPE} User and TYPE is the primary key of account and foreign key of three subclasses ID is primary key of three subclasses how to make a list of showing all IDs in one column?Also the same as balance and TYPE meet the problem I considered a.ID as saving, b.ID as time but it showing them separately

  Read the article

• Simplifying and reducing the cost of an anti-join query

  - by Savitha

  Hi, Could you please help me in simplifying and reducing the cost of the below query? I tried making it a co-related subquery with NOT EXISTS but it didn't give me any output. Please note that the table in both main and inner query is the same 'table_1". SELECT * FROM Table_1 A WHERE A.Col1 = 'abcd' AND (A.Col2, A.Col3) NOT IN (SELECT Col2, Col3 FROM Table_1 B WHERE (B.Col4 IN (1,2,3) And B.Col5 In ('x','y')) OR (B.Col4 = 1 AND B.Col5 = 'z' AND B.Col6 = 'f') )) Thanks in advance, Savitha

  Read the article

• Vertically Merge Multiple Tables in MySQL by Joint Primary Key

  - by world

  Hello, I'll attempt to make my question as clear as possible. I'm fairly unexperienced with SQL, only know the really basic queries. In order to have a better idea I'd been reading the MySQL manual for the past few days, but I couldn't really find a concrete solution and my needs are quite specific. I've got 3 MySQL MyISAM tables: table1, table2 and table3. Each table has an ID column (ID, ID2, ID3 respectively), and different data columns. For example table1 has [ID, Name, Birthday, Status, ...] columns, table2 has [ID2, Country, Zip, ...], table3 has [ID3, Source, Phone, ...] you get the idea. The ID, ID2, ID3 columns are common to all three tables... if there's an ID value in table1 it will also appear in table2 and table3. The number of rows in these tables is identical, about 10m rows in each table. What I'd like to do is create a new table that contains (most of) the columns of all three tables and merge them into it. The dates, for instance, must be converted because right now they're in VARCHAR YYYYMMDD format. Reading the MySQL manual I figured STR_TO_DATE() would do the job, but I don't know how to write the query itself in the first place so I have no idea how to integrate the date conversion. So basically, after I create the new table (which I do know how to do), how can I merge the three tables into it, integrating into the query the date conversion?

  Read the article

• Good PHP / MYSQL hashing solution for large number of text values

  - by Dave

  Short descriptio: Need hashing algorithm solution in php for large number of text values. Long description. PRODUCT_OWNER_TABLE serial_number (auto_inc), product_name, owner_id OWNER_TABLE owner_id (auto_inc), owener_name I need to maintain a database of 200000 unique products and their owners (AND all subsequent changes to ownership). Each product has one owner, but an owner may have MANY different products. Owner names are "Adam Smith", "John Reeves", etc, just text values (quite likely to be unicode as well). I want to optimize the database design, so what i was thinking was, every week when i run this script, it fetchs the owner of a proudct, then checks against a table i suppose similar to PRODUCT_OWNER_TABLE, fetching the owner_id. It then looks up owner_id in OWNER_TABLE. If it matches, then its the same, so it moves on. The problem is when its different... To optimize the database, i think i should be checking against the other "owner_name" entries in OWNER_TABLE to see if that value exists there. If it does, then i should use that owner_id. If it doesnt, then i should add another entry. Note that there is nothing special about the "name". as long as i maintain the correct linkagaes AND make the OWNER_TABLE "read-only, append-new" type table - I should be able create a historical archive of ownership. I need to do this check for 200000 entries, with i dont know how many unique owner names (~50000?). I think i need a hashing solution - the OWNER_TABLE wont be sorted, so search algos wont be optimal. programming language is PHP. database is MYSQL.

  Read the article

• Mapping words to numbers with respect to definition

  - by thornate

  As part of a larger project, I need to read in text and represent each word as a number. For example, if the program reads in "Every good boy deserves fruit", then I would get a table that converts 'every' to '1742', 'good' to '977513', etc. Now, obviously I can just use a hashing algorithm to get these numbers. However, it would be more useful if words with similar meanings had numerical values close to each other, so that 'good' becomes '6827' and 'great' becomes '6835', etc. As another option, instead of a simple integer representing each number, it would be even better to have a vector made up of multiple numbers, eg (lexical_category, tense, classification, specific_word) where lexical_category is noun/verb/adjective/etc, tense is future/past/present, classification defines a wide set of general topics and specific_word is much the same as described in the previous paragraph. Does any such an algorithm exist? If not, can you give me any tips on how to get started on developing one myself? I code in C++.

  Read the article

• How to reference other documents in a couchDB view (joining like functionality)

  - by Surfrdan

  We have a CouchDB representation of an XML database which we use to power a javascript based frontend for manipulating the XML documents. The basic structure is a simple 3 level hierachy. i.e. A - B - C A: Parent doucument (type A) B: any number of child documents of parent type A C: any number of child documents of parent type B We represent these 3 document types in CouchDB with a 'type' attribute: e.g. { "_id":"llgc-id:433", "_rev":"1-3760f3e01d7752a7508b047e0d094301", "type":"A", "label":"Top Level A document", "logicalMap":{ "issues":{ "1":{ "URL":"http://hdl.handle.net/10107/434-0", "FILE":"llgc-id:434" }, "2":{ "URL":"http://hdl.handle.net/10107/467-0", "FILE":"llgc-id:467" etc... } } } } { "_id":"llgc-id:433", "_rev":"1-3760f3e01d7752a7508b047e0d094301", "type":"B", "label":"a B document", } What I want to do is produce a view which returns documents just like the A type but includes the label attribute from the B document within the logicalMap list e.g. { "_id":"llgc-id:433", "_rev":"1-3760f3e01d7752a7508b047e0d094301", "type":"A", "label":"Top Level A document", "logicalMap":{ "issues":{ "1":{ "URL":"http://hdl.handle.net/10107/434-0", "FILE":"llgc-id:434", "LABEL":"a B document" }, "2":{ "URL":"http://hdl.handle.net/10107/467-0", "FILE":"llgc-id:467", "LABEL":"another B document" etc... } } } } I'm struggling to get my head around the best way to perform this. It looks like it should be fairly simple though!

  Read the article

• Oracle Procedure to join two tables with latest status

  - by Sony

  Please help me make an oracle stored procedure ; I have two tables tblLead: lead_id Name 1 x 2 y 3 z tblTransaction: Tran_id lead_id date status 1 1 04/20/2010 call Later 2 1 05/05/2010 confirmed I want a result like lead_id Name status 1 x confirmed 2 y not available ! 3 z not available !

  Read the article

• Rails advanced queries with join and sum calculation

  - by Dustin Brewer

  I have two models: companies and expenses. Companies have many expenses and expenses belong to companies. My expense model has an 'amount' column. I was wondering if there is a way to perform a find based on a date range and the amount column of the expenses. Something like top 3 companies by total expense amounts over a 7 day period. I've tried for the better part of the day to get this to work, I've attempted joins, chaining named scopes, raw sql, etc. and I'm not having any luck. Thanks for the help.

  Read the article

• SQL join to grab data from same table via intermediate table

  - by Sergio

  Hi Could someone help me with building the following query. I have a table called Sites, and one called Site_H. The two are joined by a foreign key relationship on page_id. So the Sites table contains pages, and the Site_H table shows which pages any given page is a child of by having another foreign key relation back to the site table with a column called ParentOf. So, a page can be have another page as a parent. Other data is stored in the Site_H table such as position etc, hence why it is separated out. I would like a query that returns the details of a page along with the details of its parent page. I just cant quite think about how to structure the SQL. Thanks

  Read the article

< Previous Page | 57 58 59 60 61 62 63 64 65 66 67 68  | Next Page >