Search Results

Search found 7706 results on 309 pages for 'inner join'.

Page 16/309 | < Previous Page | 12 13 14 15 16 17 18 19 20 21 22 23  | Next Page >

• User has many computers, computers have many attributes in different tables, best way to JOIN?

  - by krismeld

  I have a table for users: USERS: ID | NAME | ---------------- 1 | JOHN | 2 | STEVE | a table for computers: COMPUTERS: ID | USER_ID | ------------------ 13 | 1 | 14 | 1 | a table for processors: PROCESSORS: ID | NAME | --------------------------- 27 | PROCESSOR TYPE 1 | 28 | PROCESSOR TYPE 2 | and a table for harddrives: HARDDRIVES: ID | NAME | ---------------------------| 35 | HARDDRIVE TYPE 25 | 36 | HARDDRIVE TYPE 90 | Each computer can have many attributes from the different attributes tables (processors, harddrives etc), so I have intersection tables like this, to link the attributes to the computers: COMPUTER_PROCESSORS: C_ID | P_ID | --------------| 13 | 27 | 13 | 28 | 14 | 27 | COMPUTER_HARDDRIVES: C_ID | H_ID | --------------| 13 | 35 | So user JOHN, with id 1 owns computer 13 and 14. Computer 13 has processor 27 and 28, and computer 13 has harddrive 35. Computer 14 has processor 27 and no harddrive. Given a user's id, I would like to retrieve a list of that user's computers with each computers attributes. I have figured out a query that gives me a somewhat of a result: SELECT computers.id, processors.id AS p_id, processors.name AS p_name, harddrives.id AS h_id, harddrives.name AS h_name, FROM computers JOIN computer_processors ON (computer_processors.c_id = computers.id) JOIN processors ON (processors.id = computer_processors.p_id) JOIN computer_harddrives ON (computer_harddrives.c_id = computers.id) JOIN harddrives ON (harddrives.id = computer_harddrives.h_id) WHERE computers.user_id = 1 Result: ID | P_ID | P_NAME | H_ID | H_NAME | ----------------------------------------------------------- 13 | 27 | PROCESSOR TYPE 1 | 35 | HARDDRIVE TYPE 25 | 13 | 28 | PROCESSOR TYPE 2 | 35 | HARDDRIVE TYPE 25 | But this has several problems... Computer 14 doesnt show up, because it has no harddrive. Can I somehow make an OUTER JOIN to make sure that all computers show up, even if there a some attributes they don't have? Computer 13 shows up twice, with the same harddrive listet for both. When more attributes are added to a computer (like 3 blocks of ram), the number of rows returned for that computer gets pretty big, and it makes it had to sort the result out in application code. Can I somehow make a query, that groups the two returned rows together? Or a query that returns NULL in the h_name column in the second row, so that all values returned are unique? EDIT: What I would like to return is something like this: ID | P_ID | P_NAME | H_ID | H_NAME | ----------------------------------------------------------- 13 | 27 | PROCESSOR TYPE 1 | 35 | HARDDRIVE TYPE 25 | 13 | 28 | PROCESSOR TYPE 2 | 35 | NULL | 14 | 27 | PROCESSOR TYPE 1 | NULL | NULL | Or whatever result that make it easy to turn it into an array like this [13] => [P_NAME] => [0] => PROCESSOR TYPE 1 [1] => PROCESSOR TYPE 2 [H_NAME] => [0] => HARDDRIVE TYPE 25 [14] => [P_NAME] => [0] => PROCESSOR TYPE 1

  Read the article

• What if I made an explicit reference to 'this' for use inside an inner class?

  - by badp

  So far, I've used this approach to access this from the scope of an inner class: class FooManagementWindow extends JFrame { JButton rejectFoo; //... void getFooAcceptingPanel(){ //... final FooManagementWindow referenceToThis = this; rejectFoo = new JButton("Reject"); rejectFoo.addEventListener(new EventListener() { @Override public void actionPerformed(ActionEvent arg) { referenceToThis.setEnabled(false); //this requires a network call //... referenceToThis.setEnabled(true); //the user may resume his work } }); //... } } However, I just learned that instead of declaring referenceToThis, a direct reference is kept for me as: FooManagementWindow.this I have no reason to think my less standard approach may lead to errors or weird corner cases. Or are there?

  Read the article

• best command line tool to join videos

  - by user1079002

  I have used ffmpeg, but with it you have to first make mpg videos then do cat video1.mpg video2.mpg > joined.mpg and then convert to joined.mpg to joined.mp4 with ffmpeg to be able to upload on youtube. I heard there's mencoder which can join avi files without converting to mpg and using cat command. I'm making videos to upload on youtube so it needs to be avi mp4 or flv format. Which tool is the best to join videos from command line?

  Read the article

• How to Plug a Small Hole in NetBeans JSF (Join Table) Code Generation

  - by MarkH

  I was asked recently to provide an assist with designing and building a small-but-vital application that had at its heart some basic CRUD (Create, Read, Update, & Delete) functionality, built upon an Oracle database, to be accessible from various locations. Working from the stated requirements, I fleshed out the basic application and database designs and, once validated, set out to complete the first iteration for review. Using SQL Developer, I created the requisite tables, indices, and sequences for our first run. One of the tables was a many-to-many join table with three fields: one a primary key for that table, the other two being primary keys for the other tables, represented as foreign keys in the join table. Here is a simplified example of the trio of tables: Once the database was in decent shape, I fired up NetBeans to let it have first shot at the code. NetBeans does a great job of generating a mountain of essential code, saving developers what must be millions of hours of effort each year by building a basic foundation with a few clicks and keystrokes. Lest you think it (or any tool) can do everything for you, however, occasionally something tosses a paper clip into the delicate machinery and makes you open things up to fix them. Join tables apparently qualify.  :-) In the case above, the entity class generated for the join table (New Entity Classes from Database) included an embedded object consisting solely of the two foreign key fields as attributes, in addition to an object referencing each one of the "component" tables. The Create page generated (New JSF Pages from Entity Classes) worked well to a point, but when trying to save, we were greeted with an error: Transaction aborted. Hmm. A quick debugger session later and I'd identified the issue: when trying to persist the new join-table object, the embedded "foreign-keys-only" object still had null values for its two (required value) attributes...even though the embedded table objects had populated key attributes. Here's the simple fix: In the join-table controller class, find the public String create() method. It will look something like this:     public String create() {        try {            getFacade().create(current);            JsfUtil.addSuccessMessage(ResourceBundle.getBundle("/Bundle").getString("JoinEntityCreated"));            return prepareCreate();        } catch (Exception e) {            JsfUtil.addErrorMessage(e, ResourceBundle.getBundle("/Bundle").getString("PersistenceErrorOccured"));            return null;        }    } To restore balance to the force, modify the create() method as follows (changes in red):     public String create() {         try {            // Add the next two lines to resolve:            current.getJoinEntityPK().setTbl1id(current.getTbl1().getId().toBigInteger());            current.getJoinEntityPK().setTbl2id(current.getTbl2().getId().toBigInteger());            getFacade().create(current);            JsfUtil.addSuccessMessage(ResourceBundle.getBundle("/Bundle").getString("JoinEntityCreated"));            return prepareCreate();        } catch (Exception e) {            JsfUtil.addErrorMessage(e, ResourceBundle.getBundle("/Bundle").getString("PersistenceErrorOccured"));            return null;        }    } I'll be refactoring this code shortly, but for now, it works. Iteration one is complete and being reviewed, and we've met the milestone. Here's to happy endings (and customers)! All the best,Mark

  Read the article

• Is there limit of "join" or the "where" or length of SQL query ?

  - by Chetan sharma

  Actually i was trying to get data from elgg database based on multiple joins. It generated very big query with lots of JOIN statements and query never respond back. SELECT distinct e.* from test_entities e JOIN test_metadata m1 on e.guid = m1.entity_guid JOIN test_metastrings ms1 on ms1.id = m1.name_id JOIN test_metastrings mv1 on mv1.id = m1.value_id JOIN test_objects_entity obj on e.guid = obj.guid JOIN test_metadata m2 on e.guid = m2.entity_guid JOIN test_metastrings ms2 on ms2.id = m2.name_id JOIN test_metastrings mv2 on mv2.id = m2.value_id JOIN test_metadata m3 on e.guid = m3.entity_guid JOIN test_metastrings ms3 on ms3.id = m3.name_id JOIN test_metastrings mv3 on mv3.id = m3.value_id JOIN test_metadata m4 on e.guid = m4.entity_guid JOIN test_metastrings ms4 on ms4.id = m4.name_id JOIN test_metastrings mv4 on mv4.id = m4.value_id JOIN test_metadata m5 on e.guid = m5.entity_guid JOIN test_metastrings ms5 on ms5.id = m5.name_id JOIN test_metastrings mv5 on mv5.id = m5.value_id JOIN test_metadata m6 on e.guid = m6.entity_guid JOIN test_metastrings ms6 on ms6.id = m6.name_id JOIN test_metastrings mv6 on mv6.id = m6.value_id where ms1.string='expire_date' and mv1.string <= 1272565800 and ms2.string='homecity' and mv2.string LIKE "%dasf%" and ms3.string='schoolname' and mv3.string LIKE "%asdf%" and ms4.string='award_amount' and mv4.string <= 123 and ms5.string='no_of_awards' and mv5.string <= 7 and ms6.string='avg_rating' and mv6.string <= 2 and e.type = 'object' and e.subtype = 5 and e.site_guid = 1 and (obj.title like '%asdf%') OR (obj.description like '%asdf%') and ( (e.access_id = -2 AND e.owner_guid IN ( SELECT guid_one FROM test_entity_relationships WHERE relationship='friend' AND guid_two=5 )) OR (e.access_id IN (2,1) OR (e.owner_guid = 5) OR ( e.access_id = 0 AND e.owner_guid = 5 ) ) and e.enabled='yes') and ( (m1.access_id = -2 AND m1.owner_guid IN ( SELECT guid_one FROM test_entity_relationships WHERE relationship='friend' AND guid_two=5 )) OR (m1.access_id IN (2,1) OR (m1.owner_guid = 5) OR ( m1.access_id = 0 AND m1.owner_guid = 5 ) ) and m1.enabled='yes') and ( (m2.access_id = -2 AND m2.owner_guid IN ( SELECT guid_one FROM test_entity_relationships WHERE relationship='friend' AND guid_two=5 )) OR (m2.access_id IN (2,1) OR (m2.owner_guid = 5) OR ( m2.access_id = 0 AND m2.owner_guid = 5 ) ) and m2.enabled='yes') and ( (m3.access_id = -2 AND m3.owner_guid IN ( SELECT guid_one FROM test_entity_relationships WHERE relationship='friend' AND guid_two=5 )) OR (m3.access_id IN (2,1) OR (m3.owner_guid = 5) OR ( m3.access_id = 0 AND m3.owner_guid = 5 ) ) and m3.enabled='yes') and ( (m4.access_id = -2 AND m4.owner_guid IN ( SELECT guid_one FROM test_entity_relationships WHERE relationship='friend' AND guid_two=5 )) OR (m4.access_id IN (2,1) OR (m4.owner_guid = 5) OR ( m4.access_id = 0 AND m4.owner_guid = 5 ) ) and m4.enabled='yes') and ( (m5.access_id = -2 AND m5.owner_guid IN ( SELECT guid_one FROM test_entity_relationships WHERE relationship='friend' AND guid_two=5 )) OR (m5.access_id IN (2,1) OR (m5.owner_guid = 5) OR ( m5.access_id = 0 AND m5.owner_guid = 5 ) ) and m5.enabled='yes') and ( (m6.access_id = -2 AND m6.owner_guid IN ( SELECT guid_one FROM test_entity_relationships WHERE relationship='friend' AND guid_two=5 )) OR (m6.access_id IN (2,1) OR (m6.owner_guid = 5) OR ( m6.access_id = 0 AND m6.owner_guid = 5 ) ) and m6.enabled='yes') order by obj.title limit 0, 10 this is the query that i am running.

  Read the article

• Can I expect a performance gain from removing this JOIN?

  - by makeee

  I have a "items" table with 1 million rows and a "users" table with 20,000 rows. When I select from the "items" table I do a join on the "users" table (items.user_id = user.id), so that I can grab the "username" from the users table. I'm considering adding a username column to the items table and removing the join. Can I expect a decent performance increase from this? It's already quite fast, but it would be nice to decrease my load (which is pretty high). The downside is that if the user changes their username, items will still reflect their old username, but this is okay with me if I can expect a decent performance increase. I'm asking stackoverflow because benchmarks aren't telling me too much. Both queries finish very quickly. Regardless, I'm wondering if removing the join would lighten load on the database to any significant degree.

  Read the article

• python sqlite3 won't execute a join, but sqlite3 alone will

  - by Francis Davey

  Using the sqlite3 standard library in python 2.6.4, the following query works fine on sqlite3 command line: select segmentid, node_t, start, number,title from ((segments inner join position using (segmentid)) left outer join titles using (legid, segmentid)) left outer join numbers using (start, legid, version); But If I execute it via the sqlite3 library in python I get an error: >>> conn=sqlite3.connect('data/test.db') >>> conn.execute('''select segmentid, node_t, start, number,title from ((segments inner join position using (segmentid)) left outer join titles using (legid, segmentid)) left outer join numbers using (start, legid, version)''') Traceback (most recent call last): File "<stdin>", line 1, in <module> sqlite3.OperationalError: cannot join using column start - column not present in both tables The (computed) table on the left hand side of the join appears to have the relevant column because if I check it by itself I get: >>> conn.execute('''select * from ((segments inner join position using (segmentid)) left outer join titles using (legid, segmentid)) limit 20''').description (('segmentid', None, None, None, None, None, None), ('html', None, None, None, None, None, None), ('node_t', None, None, None, None, None, None), ('legid', None, None, None, None, None, None), ('version', None, None, None, None, None, None), ('start', None, None, None, None, None, None), ('title', None, None, None, None, None, None)) My schema is: CREATE TABLE leg (legid integer primary key, t char(16), year char(16), no char(16)); CREATE TABLE numbers ( number char(16), legid integer, version integer, start integer, end integer, prev integer, prev_number char(16), next integer, next_number char(16), primary key (number, legid, version)); CREATE TABLE position ( segmentid integer, legid integer, version integer, start integer, primary key (segmentid, legid, version)); CREATE TABLE 'segments' (segmentid integer primary key, html text, node_t integer); CREATE TABLE titles (legid integer, segmentid integer, title text, primary key (legid, segmentid)); CREATE TABLE versions (legid integer, version integer, primary key (legid, version)); CREATE INDEX idx_numbers_start on numbers (legid, version, start); I am baffled as to what I am doing wrong. I have tried quitting/restarting both the python and sqlite command lines and can't see what I'm doing wrong. It may be completely obvious.

  Read the article

• Hibernate noob fetch join problem

  - by Bruce

  Hi all I have two classes, Test2 and Test3. Test2 has an attribute test3 that is an instance of Test3. In other words, I have a unidirectional OneToOne association, with test2 having a reference to test3. When I select Test2 from the db, I can see that a separate select is being made to get the details of the associated test3 class. This is the famous 1+N selects problem. To fix this to use a single select, I am trying to use the fetch=join annotation, which I understand to be @Fetch(FetchMode.JOIN) However, with fetch set to join, I still see separate selects. Here are the relevant portions of my setup.. hibernate.cfg.xml: <property name="max_fetch_depth">2</property> Test2: public class Test2 { @OneToOne (cascade=CascadeType.ALL , fetch=FetchType.EAGER) @JoinColumn (name="test3_id") @Fetch(FetchMode.JOIN) public Test3 getTest3() { return test3; } NB I set the FetchType to EAGER out of desperation, even though it defaults to EAGER anyway for OneToOne mappings, but it made no difference. Thanks for any help! Edit: I've pretty much given up on trying to use FetchMode.JOIN - can anyone confirm that they have got it to work ie produce a left outer join? In the docs I see that "Usually, the mapping document is not used to customize fetching. Instead, we keep the default behavior, and override it for a particular transaction, using left join fetch in HQL" If I do a left join fetch instead: query = session.createQuery("from Test2 t2 left join fetch t2.test3"); then I do indeed get the results I want - ie a left outer join in the query.

  Read the article

• c++ defining a static member of a template class with type inner class pointer

  - by Jack

  I have a template class like here (in a header) with a inner class and a static member of type pointer to inner class template <class t> class outer { class inner { int a; }; static inner *m; }; template <class t> outer <t>::inner *outer <t>::m; when i want to define that static member i says "error: expected constructor, destructor, or type conversion before '*' token" on the last line (mingw32-g++ 3.4.5)

  Read the article

• What is wrong with my SQL syntax for an UPDATE with a JOIN?

  - by Phil H

  I have two tables, related by a common key. So TableA has key AID and value Name and TableB has keys AID, BID and values Name, Value: AID Name 74 Alpha AID BID Name Value 74 4 Beta Brilliance I would like to update the TableB Value here from Brilliance to Barmy, using just the Name fields. I thought I could do it via an UPDATE containing a JOIN, but Access (I know...) is complaining with 'Syntax error (missing operator) in query expression ' and then everything from 'Barmy' here: UPDATE tB SET tB.BValue='Barmy' FROM TableB tB INNER JOIN TableA tA ON tB.AID=tA.AID WHERE tB.Name='Beta' AND tA.Name='Alpha'; What is my heinous crime? Or is it just Access not conforming?

  Read the article

• JavaScript inner function scope chain?

  - by Ding

  In this example var a = 1; ( function(x) { function inner() { alert(a); alert(x); alert(y); } var y = 3; inner(); })(2); When does function inner get created? during execution time or parsing time of outer anonymous function? What is in the scope chain of function inner? What is in the execution context of function inner? I know it is not a simple question, thanks for enlighting me in advance!

  Read the article

• Change query to use a LEFT join

  - by Craig

  I have a query which is failing, as it needs to be using LEFT JOIN, as opposed to the default INNER JOIN used by the 'join' syntax: var users = (from u in this._context.Users join p in this._context.Profiles on u.ProfileID equals p.ID join vw in this._context.vw_Contacts on u.ContactID equals vw.ID orderby u.Code select new { ID = u.ID, profileId = p.ID, u.ContactID, u.Code, u.UserName, vw.FileAs, p.Name, u.LastLogout, u.Inactive, u.Disabled }).ToList(); How would i re-write this so that is utilises a LEFT join?

  Read the article

• How do I write to an outer truecrypt volume when the inner volume protection prevents writng?

  - by con-f-use

  In a nutshell After some time using the outer volume of a hidden volume in Truecrypt I cannot write to the outer volume anymore. The protection of the inner volume always kicks in before. How do I fix this? Details I'm using truecrypt's two layered encryption of a USB stick. The outer container carries my semi-sensitive stuff while the inner hidden values has a bit more valuable information. I use both, the inner and outer volume regularly and that is part of the problem. Truecrypt can mount the outer volume for writing while protecting the inner. Usually the inner volume, when not protected this way (or mounted read-only) would be indistinguishable from free space. That is of course part of the plausible deniability scheme of truecrypt. At the beginning, everything worked as expected. I could copy and delete data to the outer volume as I pleased. Now it seams that I have written and deleted enough data to have filled the outer volume once. Despite the write protection Ubuntu tries now to write to the continuous "free space" that is the inner volume. It does that although enough other free space is on the outer volume. But on this free space there used to be data so its fragmented and the file system write prefers continuous space. The write on the continuous free space of the outer volume of course fails (with the error message in the picture above) as Truecrypt's inner-volume-protection kicks in. The Question I know this is expected behaviour, but is there a better way to write to the outer volume that does not attempt to write to the hidden free space at the end? The whole question could be more generally rephrased to: How do I control, where on a partition data is written in Ubuntu?

  Read the article

• How can you access two identically-named columns in a MySQL LEFT JOIN query?

  - by George Edison

  I have two tables. table_x: id INT(11) tag INT(11) table_tags: id INT(11) name VARCHAR(255) Then I use PHP to perform the following query: SELECT * FROM table_x LEFT JOIN table_tags ON table_x.tag = table_tags.id The only problem is: how do I access table_x.id and table_tags.id in the results? Here is the PHP code: $query = "SELECT * FROM table_x LEFT JOIN table_tags ON table_x.tag = table_tags.id"; $results = mysql_query($query); while($row = mysql_fetch_array($results)) { // how do I now access table_x.id and table_tags.id ??? }

  Read the article

• Inequality joins, Asynchronous transformations and Lookups : SSIS

  - by jamiet

  It is pretty much accepted by SQL Server Integration Services (SSIS) developers that synchronous transformations are generally quicker than asynchronous transformations (for a description of synchronous and asynchronous transformations go read Asynchronous and synchronous data flow components). Notice I said “generally” and not “always”; there are circumstances where using asynchronous transformations can be beneficial and in this blog post I’ll demonstrate such a scenario, one that is pretty common when building data warehouses. Imagine I have a [Customer] dimension table that manages information about all of my customers as a slowly-changing dimension. If that is a type 2 slowly changing dimension then you will likely have multiple rows per customer in that table. Furthermore you might also have datetime fields that indicate the effective time period of each member record. Here is such a table that contains data for four dimension members {Terry, Max, Henry, Horace}: Notice that we have multiple records per customer and that the [SCDStartDate] of a record is equivalent to the [SCDEndDate] of the record that preceded it (if there was one). (Note that I am on record as saying I am not a fan of this technique of storing an [SCDEndDate] but for the purposes of clarity I have included it here.) Anyway, the idea here is that we will have some incoming data containing [CustomerName] & [EffectiveDate] and we need to use those values to lookup [Customer].[CustomerId]. The logic will be: Lookup a [CustomerId] WHERE [CustomerName]=[CustomerName] AND [SCDStartDate] <= [EffectiveDate] AND [EffectiveDate] <= [SCDEndDate] The conventional approach to this would be to use a full cached lookup but that isn’t an option here because we are using inequality conditions. The obvious next step then is to use a non-cached lookup which enables us to change the SQL statement to use inequality operators: Let’s take a look at the dataflow: Notice these are all synchronous components. This approach works just fine however it does have the limitation that it has to issue a SQL statement against your lookup set for every row thus we can expect the execution time of our dataflow to increase linearly in line with the number of rows in our dataflow; that’s not good. OK, that’s the obvious method. Let’s now look at a different way of achieving this using an asynchronous Merge Join transform coupled with a Conditional Split. I’ve shown it post-execution so that I can include the row counts which help to illustrate what is going on here: Notice that there are more rows output from our Merge Join component than on the input. That is because we are joining on [CustomerName] and, as we know, we have multiple records per [CustomerName] in our lookup set. Notice also that there are two asynchronous components in here (the Sort and the Merge Join). I have embedded a video below that compares the execution times for each of these two methods. The video is just over 8minutes long. View on Vimeo  For those that can’t be bothered watching the video I’ll tell you the results here. The dataflow that used the Lookup transform took 36 seconds whereas the dataflow that used the Merge Join took less than two seconds. An illustration in case it is needed: Pretty conclusive proof that in some scenarios it may be quicker to use an asynchronous component than a synchronous one. Your mileage may of course vary. The scenario outlined here is analogous to performance tuning procedural SQL that uses cursors. It is common to eliminate cursors by converting them to set-based operations and that is effectively what we have done here. Our non-cached lookup is performing a discrete operation for every single row of data, exactly like a cursor does. By eliminating this cursor-in-disguise we have dramatically sped up our dataflow. I hope all of that proves useful. You can download the package that I demonstrated in the video from my SkyDrive at http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20100514/20100514%20Lookups%20and%20Merge%20Joins.zip Comments are welcome as always. @Jamiet Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

  Read the article

• Inkscape: how to create inner border?

  - by Anton

  I create figure and set border width to 1px. My image actual size is 100px. But with border this is 102px. How to set inner border instead of o*uther border*? In Protoshop I can select type of border - inner, outer or center. But in Inkscape I not found this option. Please, help me. Thanks!

  Read the article

• SQL Server 2008: Comparing similar records - Need to still display an ID for a record when the JOIN has no matches

  - by aleppke

  I'm writing a SQL Server 2008 report that will compare genetic test results for animals. A genetic test consists of an animalId, a gene and a result. Not all animals will have the same genes tested but I need to be able to display the results side-by-side for a given set of animals and only include the genes that are present for at least one of the selected animals. My TestResult table has the following data in it: animalId gene result 1 a CC 1 b CT 1 d TT 2 a CT 2 b CT 2 c TT 3 a CT 3 b TT 3 c CC 3 d CC 3 e TT I need to generate a result set that looks like the following. Note that Animal 3 is not being displayed (user doesn't want to see its results) and neither are results for Gene "e" since neither Animal 1 nor Animal 2 have a result for that gene: SireID SireResult CalfID CalfResult Gene 1 CC 2 CT a 1 CT 2 CT b 1 NULL 2 TT c 1 TT 2 NULL d But I can only manage to get this: SireID SireResult CalfID CalfResult Gene 1 CC 2 CT a 1 CT 2 CT b NULL NULL 2 TT c 1 TT NULL NULL d This is the query I'm using. SELECT sire.animalId AS 'SireID' ,sire.result AS 'SireResult' ,calf.animalId AS 'CalfID' ,calf.result AS 'CalfResult' ,sire.gene AS 'Gene' FROM (SELECT s.animalId ,s.result ,m1.gene FROM (SELECT [animalId ] ,result ,gene FROM TestResult WHERE animalId IN (1)) s FULL JOIN (SELECT DISTINCT gene FROM TestResult WHERE animalId IN (1, 2)) m1 ON s.marker = m1.marker) sire FULL JOIN (SELECT c.animalId ,c.result ,m2.gene FROM (SELECT animalId ,result ,gene FROM TestResult WHERE animalId IN (2)) c FULL JOIN (SELECT DISTINCT gene FROM TestResult WHERE animalId IN (1, 2)) m2 ON c.gene = m2.gene) calf ON sire.gene = calf.gene How do I get the SireIDs and CalfIDs to display their values when they don't have a record associated with a particular Gene? I was thinking of using COALESCE but I can't figure out how to specify the correct animalId to pass in. Any help would be appreciated.

  Read the article

• How to join multiple tables using LINQ-to-SQL?

  - by user603245

  Hi! I'm quite new to linq, so please bear with me. I'm working on a asp.net webpage and I want to add a "search function" (textbox where user inputs name or surname or both or just parts of it and gets back all related information). I have two tables ("Person" and "Application") and I want to display some columns from Person (name and surname) and some from Application (score, position,...). I know how I could do it using sql, but I want to learn more about linq and thus I want to do it using linq. For now I got two main ideas: 1.) var person = dataContext.GetTable<Person>(); var application = dataContext.GetTable<Application>(); var p1 = from p in Person where(p.Name.Contains(tokens[0]) || p.Surname.Contains(tokens[1])) select new {Id = p.Id, Name = p.Name, Surname = p.Surname}; //or maybe without this line //I don't know how to do the following properly var result = from a in Application where a.FK_Application.Equals(index) //just to get the "right" type of application //this is not right, but I don't know how to do it better join p1 on p1.Id == a.FK_Person 2.) The other idea is just to go through "Application" and instead of "join p1 ..." to use var result = from a in Application where a.FK_Application.Equals(index) //just to get the "right" type of application join p from Person on p.Id == a.FK_Person where p.Name.Contains(tokens[0]) || p.Surname.Contains(tokens[1]) I think that first idea is better for queries without the first "where" condition, which I also intended to use. Regardless of what is better (faster), I still don't know how to do it using linq. Also in the end I wanted to display / select just some parts (columns) of the result (joined tables + filtering conditions). I really want to know how to do such things using linq as I'll be dealing also with some similar problems with local data, where I can use only linq. Could somebody please explain me how to do it, I spent days trying to figure it out and searching on the internet for answers. Thank you for your time.

  Read the article

• How can I join 3 tables with mysql & php?

  - by steven

  check out the page [url]http://www.mujak.com/test/test3.php[/url] It pulls the users Post,username,xbc/xlk tags etc which is perfect... BUT since I am pulling information from a MyBB bulletin board system, its quite different. When replying, people are are allowed to change the "Thread Subject" by simplying replying and changing it. I dont want it to SHOW the changed subject title, just the original title of all posts in that thread. By default it repies with "RE:thread title". They can easily edit this and it will show up in the "Subject" cell & people wont know which thread it was posted in because they changed their thread to when replying to the post. So I just want to keep the orginial thread title when they are replying. Make sense~?? Tables:mybb_users Fields:uid,username Tables:mybb_userfields Fields:ufid Tables:mybb_posts Fields:pid,tid,replyto,subject,ufid,username,uid,message Tables:mybb_threads Fields:tid,fid,subject,uid,username,lastpost,lastposter,lastposteruid I haev tried multiple queries with no success: $result = mysql_query(" SELECT * FROM mybb_users LEFT JOIN (mybb_posts, mybb_userfields, mybb_threads) ON ( mybb_userfields.ufid=mybb_posts.uid AND mybb_threads.tid=mybb_posts.tid AND mybb_users.uid=mybb_userfields.ufid ) WHERE mybb_posts.fid=42"); $result = mysql_query(" SELECT * FROM mybb_users LEFT JOIN (mybb_posts, mybb_userfields, mybb_threads) ON ( mybb_userfields.ufid=mybb_posts.uid AND mybb_threads.tid=mybb_posts.tid AND mybb_users.uid=mybb_posts.uid ) WHERE mybb_threads.fid=42"); $result = mysql_query(" SELECT * FROM mybb_posts LEFT JOIN (mybb_userfields, mybb_threads) ON ( mybb_userfields.ufid=mybb_posts.uid AND mybb_threads.tid=mybb_posts.tid ) WHERE mybb_posts.fid=42");

  Read the article

• T-SQL - Left Outer Joins - Filters in the where clause versus the on clause.

  - by Greg Potter

  I am trying to compare two tables to find rows in each table that is not in the other. Table 1 has a groupby column to create 2 sets of data within table one. groupby number ----------- ----------- 1 1 1 2 2 1 2 2 2 4 Table 2 has only one column. number ----------- 1 3 4 So Table 1 has the values 1,2,4 in group 2 and Table 2 has the values 1,3,4. I expect the following result when joining for Group 2: `Table 1 LEFT OUTER Join Table 2` T1_Groupby T1_Number T2_Number ----------- ----------- ----------- 2 2 NULL `Table 2 LEFT OUTER Join Table 1` T1_Groupby T1_Number T2_Number ----------- ----------- ----------- NULL NULL 3 The only way I can get this to work is if I put a where clause for the first join: PRINT 'Table 1 LEFT OUTER Join Table 2, with WHERE clause' select table1.groupby as [T1_Groupby], table1.number as [T1_Number], table2.number as [T2_Number] from table1 LEFT OUTER join table2 --****************************** on table1.number = table2.number --****************************** WHERE table1.groupby = 2 AND table2.number IS NULL and a filter in the ON for the second: PRINT 'Table 2 LEFT OUTER Join Table 1, with ON clause' select table1.groupby as [T1_Groupby], table1.number as [T1_Number], table2.number as [T2_Number] from table2 LEFT OUTER join table1 --****************************** on table2.number = table1.number AND table1.groupby = 2 --****************************** WHERE table1.number IS NULL Can anyone come up with a way of not using the filter in the on clause but in the where clause? The context of this is I have a staging area in a database and I want to identify new records and records that have been deleted. The groupby field is the equivalent of a batchid for an extract and I am comparing the latest extract in a temp table to a the batch from yesterday stored in a partioneds table, which also has all the previously extracted batches as well. Code to create table 1 and 2: create table table1 (number int, groupby int) create table table2 (number int) insert into table1 (number, groupby) values (1, 1) insert into table1 (number, groupby) values (2, 1) insert into table1 (number, groupby) values (1, 2) insert into table2 (number) values (1) insert into table1 (number, groupby) values (2, 2) insert into table2 (number) values (3) insert into table1 (number, groupby) values (4, 2) insert into table2 (number) values (4) EDIT: A bit more context - depending on where I put the filter I different results. As stated above the where clause gives me the correct result in one state and the ON in the other. I am looking for a consistent way of doing this. Where - select table1.groupby as [T1_Groupby], table1.number as [T1_Number], table2.number as [T2_Number] from table1 LEFT OUTER join table2 --****************************** on table1.number = table2.number --****************************** WHERE table1.groupby = 2 AND table2.number IS NULL Result: T1_Groupby T1_Number T2_Number ----------- ----------- ----------- 2 2 NULL On - select table1.groupby as [T1_Groupby], table1.number as [T1_Number], table2.number as [T2_Number] from table1 LEFT OUTER join table2 --****************************** on table1.number = table2.number AND table1.groupby = 2 --****************************** WHERE table2.number IS NULL Result: T1_Groupby T1_Number T2_Number ----------- ----------- ----------- 1 1 NULL 2 2 NULL 1 2 NULL Where (table 2 this time) - select table1.groupby as [T1_Groupby], table1.number as [T1_Number], table2.number as [T2_Number] from table2 LEFT OUTER join table1 --****************************** on table2.number = table1.number AND table1.groupby = 2 --****************************** WHERE table1.number IS NULL Result: T1_Groupby T1_Number T2_Number ----------- ----------- ----------- NULL NULL 3 On - select table1.groupby as [T1_Groupby], table1.number as [T1_Number], table2.number as [T2_Number] from table2 LEFT OUTER join table1 --****************************** on table2.number = table1.number --****************************** WHERE table1.number IS NULL AND table1.groupby = 2 Result: T1_Groupby T1_Number T2_Number ----------- ----------- ----------- (0) rows returned

  Read the article

• Is this Where condition in Linq-to-sql join correct?

  - by Pandiya Chendur

  I have the following Iqueryable method to show details of a singl material, public IQueryable<Materials> GetMaterial(int id) { return from m in db.Materials join Mt in db.MeasurementTypes on m.MeasurementTypeId equals Mt.Id where m.Mat_id equals id select new Materials() { Id = Convert.ToInt64(m.Mat_id), Mat_Name = m.Mat_Name, Mes_Name = Mt.Name, }; } Any suggestion....

  Read the article

• How to create a dynamic Linq Join extension method

  - by Royd Brayshay

  There was a library of dynamic Linq extensions methods released as a sample with VS2008. I'd like to extend it with a Join method. The code below fails with a parameter miss match exception at run time. Can anyone find the problem? public static IQueryable Join(this IQueryable outer, IEnumerable inner, string outerSelector, string innerSelector, string resultsSelector, params object[] values) { if (inner == null) throw new ArgumentNullException("inner"); if (outerSelector == null) throw new ArgumentNullException("outerSelector"); if (innerSelector == null) throw new ArgumentNullException("innerSelector"); if (resultsSelector == null) throw new ArgumentNullException("resultsSelctor"); LambdaExpression outerSelectorLambda = DynamicExpression.ParseLambda(outer.ElementType, null, outerSelector, values); LambdaExpression innerSelectorLambda = DynamicExpression.ParseLambda(inner.AsQueryable().ElementType, null, innerSelector, values); ParameterExpression[] parameters = new ParameterExpression[] { Expression.Parameter(outer.ElementType, "outer"), Expression.Parameter(inner.AsQueryable().ElementType, "inner") }; LambdaExpression resultsSelectorLambda = DynamicExpression.ParseLambda(parameters, null, resultsSelector, values); return outer.Provider.CreateQuery( Expression.Call( typeof(Queryable), "Join", new Type[] { outer.ElementType, inner.AsQueryable().ElementType, outerSelectorLambda.Body.Type, innerSelectorLambda.Body.Type, resultsSelectorLambda.Body.Type }, outer.Expression, inner.AsQueryable().Expression, Expression.Quote(outerSelectorLambda), Expression.Quote(innerSelectorLambda), Expression.Quote(resultsSelectorLambda))); } I've now fixed it myself, here's the answer. Please vote it up or add a better one.

  Read the article

• Clean file separators in Ruby without File.join

  - by kerry

  I love anything that can be done to clean up source code and make it more readable.  So, when I came upon this post, I was pretty excited.  This is precisely the kind of thing I love. I have never felt good about ‘file separator’ strings b/c of their ugliness and verbosity. In Java we have: 1: String path = "lib"+File.separator+"etc"; And in Ruby a popular method is: 1: path = File.join("lib","etc") Now, by overloading the ‘/’ operator on a String in Ruby: 1: class String 2: def /(str_to_join) 3: File.join(self, str_to_join) 4: end 5: end We can now write: 1: path = 'lib'/'src'/'main' Brilliant!

  Read the article

• MySQL 5 multiple JOIN syntax not working in MySQL 4

  - by draco

  Hello all, the current SQL query works fine locally on MAMP 1.8.4 running MySQL 5.1.37. SELECT EL.log_actions, EL.log_date, EL.log_value, EL.log_type, EA.admins_name, EU.users_name, EU.users_matric FROM events_log EL JOIN events_users EU USING (users_id) JOIN events_admins EA USING (admins_id) ORDER BY EL.log_id DESC LIMIT 0, 10 However, when I bring this query live to production server which is running MySQL 4.1.22-standard, the following error occurred (whether or not there are data in the entry). A Database Error Occurred Error Number: 1054 Unknown column 'sceclub_exclaim2007.EU.admins_id' in 'on clause' SELECT EL.log_actions, EL.log_date, EL.log_value, EL.log_type, EA.admins_name, EU.users_name, EU.users_matric FROM events_log EL JOIN events_users EU USING (users_id) JOIN events_admins EA USING (admins_id) ORDER BY EL.log_id DESC LIMIT 0, 20 This is based on CodeIgniter 1.7.2 and both production and development are running the same set of database. Database tables events_users: users_id users_name users_credits users_matric users_redeem events_admins: admins_id admins_email admins_name admins_pass admins_date admins_modified admins_last_login events_attendance: attendance_id users_id events_id events_events: events_id events_name events_venue events_time events_desc events_pass events_log:log_id admins_id log_actions log_date log_value users_id log_type I'm new to MySQL so I'm not aware of any difference in versions or what could be a possible cause, thank you in advance! Tried googling for MySQL4 difference to no avail too. Also tried using SELECT EL.log_actions, EL.log_date, EL.log_value, EL.log_type, EA.admins_name, EU.users_name, EU.users_matric FROM events_log EL JOIN events_users EU where EL.users_id = EU.users_id JOIN events_admins EA USING EL.admins_id = EA.admins_id ORDER BY EL.log_id DESC LIMIT 0, 10 But then I got the error in both production and development. A Database Error Occurred Error Number: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN events_admins EA USING EL.admins_id = EA.admins_id ORDER BY' at line 8 SELECT EL.log_actions, EL.log_date, EL.log_value, EL.log_type, EA.admins_name, EU.users_name, EU.users_matric FROM events_log EL JOIN events_users EU where EL.users_id = EU.users_id JOIN events_admins EA USING EL.admins_id = EA.admins_id ORDER BY EL.log_id DESC LIMIT 0, 20 If you can point me to some resources where I can read up more on MySQL 4 syntax to achieve the same thing effect like I did with MySQL 5 syntax, please let me know. Thanks again!

  Read the article

• PostgreSQL JOIN with array type with array elements order, how to implement?

  - by Adiasz

  Hello I have two tables in database: CREATE TABLE items( id SERIAL PRIMARy KEY, ... some other fields ); This table contains come data row with unique ID. CREATE TABLE some_choosen_data_in_order( id SERIAL PRIMARy KEY, id_items INTEGER[], ); This table contains array type field. Each row contains values of IDs from table "items" in specyfic order. For example: {2,4,233,5}. Now, I want to get data from table "items" for choosen row from table "some_choosen_data_in_order" with order for elements in array type. The my attempt is JOIN: SELECT I.* FROM items AS I JOIN some_choosen_data_in_order AS S ON I.id = ANY(S.id_items) WHERE S.id = ? Second attempt was subquery like: SELECT I.* FROM items AS I WHERE I.id = ANY (ARRAY[SELECT S.id_items FROM some_choosen_data_in_order WHERE id = ?]) But none of them keep IDs order in array field. Could You help me, how to get data from "items" table with correspond with array IDs order from "some_choosen_data_in_order" table for specyfic row?

  Read the article

< Previous Page | 12 13 14 15 16 17 18 19 20 21 22 23  | Next Page >