Search Results

Search found 6841 results on 274 pages for 'outer join'.

Page 15/274 | < Previous Page | 11 12 13 14 15 16 17 18 19 20 21 22  | 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

• LEFT OUTER JOIN in Linq - How to Force

  - by dodegaard

  I have a LEFT OUTER OUTER join in LINQ that is combining with the outer join condition and not providing the desired results. It is basically limiting my LEFT side result with this combination. Here is the LINQ and resulting SQL. What I'd like is for "AND ([t2].[EligEnd] = @p0" in the LINQ query to not bew part of the join condition but rather a subquery to filter results BEFORE the join. Thanks in advance (samples pulled from LINQPad) - Doug (from l in Users join mr in (from mri in vwMETRemotes where met.EligEnd == Convert.ToDateTime("2009-10-31") select mri) on l.Mahcpid equals mr.Mahcpid into lo from g in lo.DefaultIfEmpty() orderby l.LastName, l.FirstName where l.LastName.StartsWith("smith") && l.DeletedDate == null select g) Here is the resulting SQL -- Region Parameters DECLARE @p0 DateTime = '2009-10-31 00:00:00.000' DECLARE @p1 NVarChar(6) = 'smith%' -- EndRegion SELECT [t2].[test], [t2].[MAHCPID] AS [Mahcpid], [t2].[FirstName], [t2].[LastName], [t2].[Gender], [t2].[Address1], [t2].[Address2], [t2].[City], [t2].[State] AS [State], [t2].[ZipCode], [t2].[Email], [t2].[EligStart], [t2].[EligEnd], [t2].[Dependent], [t2].[DateOfBirth], [t2].[ID], [t2].[MiddleInit], [t2].[Age], [t2].[SSN] AS [Ssn], [t2].[County], [t2].[HomePhone], [t2].[EmpGroupID], [t2].[PopulationIdentifier] FROM [dbo].[User] AS [t0] LEFT OUTER JOIN ( SELECT 1 AS [test], [t1].[MAHCPID], [t1].[FirstName], [t1].[LastName], [t1].[Gender], [t1].[Address1], [t1].[Address2], [t1].[City], [t1].[State], [t1].[ZipCode], [t1].[Email], [t1].[EligStart], [t1].[EligEnd], [t1].[Dependent], [t1].[DateOfBirth], [t1].[ID], [t1].[MiddleInit], [t1].[Age], [t1].[SSN], [t1].[County], [t1].[HomePhone], [t1].[EmpGroupID], [t1].[PopulationIdentifier] FROM [dbo].[vwMETRemote] AS [t1] ) AS [t2] ON ([t0].[MAHCPID] = [t2].[MAHCPID]) AND ([t2].[EligEnd] = @p0) WHERE ([t0].[LastName] LIKE @p1) AND ([t0].[DeletedDate] IS NULL) ORDER BY [t0].[LastName], [t0].[FirstName]

  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

• Need help in SQL and Sequel involving inner join and where/filter

  - by mhd

  Need help transfer sql to sequel: SQL: SELECT table_t.curr_id FROM table_t INNER JOIN table_c ON table_c.curr_id = table_t.curr_id INNER JOIN table_b ON table_b.bic = table_t.bic WHERE table_c.alpha_id = 'XXX' AND table_b.name='Foo'; I'm stuck in the sequel, I don't know how to filter, so far like this: cid= table_t.select(:curr_id). join(:table_c, :curr_id=>:curr_id). join(:table_b, :bic=>:bic). filter( ????? ) Answer with better idiom than above is appreciated as well.Tnx. UPDATE: I have to modify a little to make it works cid = DB[:table_t].select(:table_t__curr_id). join(:table_c, :curr_id=>:curr_id). join(:table_b, :bic=>:table_t__bic). #add table_t or else ERROR: column table_c.bic does not exist filter(:table_c__alpha_id => 'XXX', :table_b__name => 'Foo') without filter, cid = DB[:table_t].select(:table_t__curr_id). join(:table_c, :curr_id=>:curr_id, :alpha_id=>'XXX'). join(:table_b, :bic=>:table_t__bic, :name=>'Foo') btw I use pgsql 9.0

  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 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

• sybase - values from one table that aren't on another, on opposite ends of a 3-table join

  - by Lazy Bob

  Hypothetical situation: I work for a custom sign-making company, and some of our clients have submitted more sign designs than they're currently using. I want to know what signs have never been used. 3 tables involved: table A - signs for a company sign_pk(unique) | company_pk | sign_description 1 --------------------1 ---------------- small 2 --------------------1 ---------------- large 3 --------------------2 ---------------- medium 4 --------------------2 ---------------- jumbo 5 --------------------3 ---------------- banner table B - company locations company_pk | company_location(unique) 1 ------|------ 987 1 ------|------ 876 2 ------|------ 456 2 ------|------ 123 table C - signs at locations (it's a bit of a stretch, but each row can have 2 signs, and it's a one to many relationship from company location to signs at locations) company_location | front_sign | back_sign 987 ------------ 1 ------------ 2 987 ------------ 2 ------------ 1 876 ------------ 2 ------------ 1 456 ------------ 3 ------------ 4 123 ------------ 4 ------------ 3 So, a.company_pk = b.company_pk and b.company_location = c.company_location. What I want to try and find is how to query and get back that sign_pk 5 isn't at any location. Querying each sign_pk against all of the front_sign and back_sign values is a little impractical, since all the tables have millions of rows. Table a is indexed on sign_pk and company_pk, table b on both fields, and table c only on company locations. The way I'm trying to write it is along the lines of "each sign belongs to a company, so find the signs that are not the front or back sign at any of the locations that belong to the company tied to that sign." My original plan was: Select a.sign_pk from a, b, c where a.company_pk = b.company_pk and b.company_location = c.company_location and a.sign_pk *= c.front_sign group by a.sign_pk having count(c.front_sign) = 0 just to do the front sign, and then repeat for the back, but that won't run because c is an inner member of an outer join, and also in an inner join. This whole thing is fairly convoluted, but if anyone can make sense of it, I'll be your best friend.

  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

• Can I join two tables whereby the joined table is sorted by a certain column?

  - by Ferdy

  I'm not much of a database guru so I need some help on a query I'm working on. In my photo community project I want to richly visualize tags by not only showing the tag name and counter (# of images inside them), I also want to show a thumb of the most popular image inside the tag (most karma). The table setup is as follow: Image table holds basic image metadata, important is the karma field Imagefile table holds multiple entries per image, one for each format Tag table holds tag definitions Tag_map table maps tags to images In my usual trial and error query authoring I have come this far: SELECT * FROM (SELECT tag.name, tag.id, COUNT(tag_map.tag_id) as cnt FROM tag INNER JOIN tag_map ON (tag.id = tag_map.tag_id) INNER JOIN image ON tag_map.image_id = image.id INNER JOIN imagefile on image.id = imagefile.image_id WHERE imagefile.type = 'smallthumb' GROUP BY tag.name ORDER BY cnt DESC) as T1 WHERE cnt > 0 ORDER BY cnt DESC [column clause of inner query snipped for the sake of simplicity] This query gives me somewhat what I need. The outer query makes sure that only tags are returned for which there is at least 1 image. The inner query returns the tag details, such as its name, count (# of images) and the thumb. In addition, I can sort the inner query as I want (by most images, alphabetically, most recent, etc) So far so good. The problem however is that this query does not match the most popular image (most karma) of the tag, it seems to always take the most recent one in the tag. How can I make sure that the most popular image is matched with the tag?

  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

• Reporting Services Sum of Inner Group in Outer Group

  - by Spoonybard

  I have a report in Reporting Services 2008 using ASP.net 3.5 and SQL Server 2008. The report has 2 groupings and a detail row. This is the current format: Outer Group Inner Group Detail Row The Detail Row represents an item on a receipt and a receipt can have multiple items. Each receipt was paid with a certain payment method. So the Outer Group is grouped by payment type, the Inner Group is grouped by the receipt's ID, and the Detail Row is each item for the given receipt. My raw data result set has two important columns: The Amount Received and the Amount Applied. The Amount Received is how much money in total was collected for all the items on the receipt. The Amount Applied is how much money each item got from the total Amount Received. Sample Result Set: ReceiptID Item ItemID AmountReceived AmountApplied Payment Method ------------------------------------------------------------------------------------------ 1 Book 1 $200.00 $40.00 Cash 1 CD 2 $200.00 $20.00 Cash 1 Software 3 $200.00 $100.00 Cash 1 Backpack 4 $200.00 $40.00 Cash The Inner Group displays the AmountReceived correctly as $200. However, the Outer Group displays the AmountReceived as $800, because I believe that it is going off each detail row which in this case is a count of 4 items. What I want is to see in the Outer Group that the Amount Received is $200. I tried restricting the scope in my SUM function to be the Inner Group, but I get the error "The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset." Does anyone have any suggestions on how to solve this issue? Thanks.

  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

• Convert SQL with Inner AND Outer Join to L2S

  - by Refracted Paladin

  I need to convert the below Sproc to a Linq query. At the very bottom is what I have so far. For reference the fields behind the "splat"(not my sproc) are ImmunizationID int, HAReviewID int, ImmunizationMaintID int, ImmunizationOther varchar(50), ImmunizationDate smalldatetime, ImmunizationReasonID int The first two are PK and FK, respectively. The other two ints are linke to the Maint Table where there description is stored. That is what I am stuck on, the INNER JOIN AND the LEFT OUTER JOIN Thanks, SELECT tblHAReviewImmunizations.*, tblMaintItem.ItemDescription, tblMaintItem2.ItemDescription as Reason FROM dbo.tblHAReviewImmunizations INNER JOIN dbo.tblMaintItem ON dbo.tblHAReviewImmunizations.ImmunizationMaintID = dbo.tblMaintItem.ItemID LEFT OUTER JOIN dbo.tblMaintItem as tblMaintItem2 ON dbo.tblHAReviewImmunizations.ImmunizationReasonID = tblMaintItem2.ItemID WHERE HAReviewID = @haReviewID My attempt so far -- public static DataTable GetImmunizations(int haReviewID) { using (var context = McpDataContext.Create()) { var currentImmunizations = from haReviewImmunization in context.tblHAReviewImmunizations where haReviewImmunization.HAReviewID == haReviewID join maintItem in context.tblMaintItems on haReviewImmunization.ImmunizationReasonID equals maintItem.ItemID into g from maintItem in g.DefaultIfEmpty() let Immunization = GetImmunizationNameByID( haReviewImmunization.ImmunizationMaintID) select new { haReviewImmunization.ImmunizationDate, haReviewImmunization.ImmunizationOther, Immunization, Reason = maintItem == null ? " " : maintItem.ItemDescription }; return currentImmunizations.CopyLinqToDataTable(); } } private static string GetImmunizationNameByID(int? immunizationID) { using (var context = McpDataContext.Create()) { var domainName = from maintItem in context.tblMaintItems where maintItem.ItemID == immunizationID select maintItem.ItemDescription; return domainName.SingleOrDefault(); } }

  Read the article

• Single line diff

  - by Ollie Saunders

  Is there a diff tool I can run from the command line or maybe just an online webpage that will compare single lines? GitHub diffs do this but I'm don't want to publish things to GitHub just to read the diffs! This is the sort of thing I'm wanting to diff: a:2:{i:0;O:10:"FuTreeNode":6:{s:5:"outer";r:2;s:6:"inners";O:20:"FuLinkedTreeNodeList":2:{s:5:"outer";r:2;s:5:"items";a:0:{}}s:5:"value";s:3:"foo";s:4:"next";O:10:"FuTreeNode":6:{s:5:"outer";r:2;s:6:"inners";O:20:"FuLinkedTreeNodeList":2:{s:5:"outer";r:8;s:5:"items";a:0:{}}s:5:"value";s:3:"bar";s:4:"next";N;s:4:"prev";O:10:"FuTreeNode":6:{s:5:"outer";N;s:6:"inners";O:20:"FuLinkedTreeNodeList":2:{s:5:"outer";r:15;s:5:"items";a:0:{}}s:5:"value";s:3:"zim";s:4:"next";r:8;s:4:"prev";O:10:"FuTreeNode":6:{s:5:"outer";N;s:6:"inners";O:20:"FuLinkedTreeNodeList":2:{s:5:"outer";r:22;s:5:"items";a:0:{}}s:5:"value";s:3:"foo";s:4:"next";r:15;s:4:"prev";r:8;s:6:"ofList";O:12:"FuLinkedList":1:{s:5:"items";a:4:{i:0;r:2;i:1;r:8;i:2;r:22;i:3;r:15;}}}s:6:"ofList";r:30;}s:6:"ofList";O:20:"FuLinkedTreeNodeList":2:{s:5:"outer";r:2;s:5:"items";a:2:{i:0;r:2;i:1;r:8;}}}s:4:"prev";N;s:6:"ofList";r:37;}i:1;r:8;} a:2:{i:0;O:10:"FuTreeNode":6:{s:5:"outer";r:2;s:6:"inners";O:20:"FuLinkedTreeNodeList":2:{s:5:"outer";r:2;s:5:"items";a:0:{}}s:5:"value";s:3:"foo";s:4:"next";O:10:"FuTreeNode":6:{s:5:"outer";r:2;s:6:"inners";O:20:"FuLinkedTreeNodeList":2:{s:5:"outer";r:8;s:5:"items";a:0:{}}s:5:"value";s:3:"bar";s:4:"next";N;s:4:"prev";O:10:"FuTreeNode":6:{s:5:"outer";N;s:6:"inners";O:20:"FuLinkedTreeNodeList":2:{s:5:"outer";r:15;s:5:"items";a:0:{}}s:5:"value";s:3:"zim";s:4:"next";r:8;s:4:"prev";O:10:"FuTreeNode":6:{s:5:"outer";N;s:6:"inners";O:20:"FuLinkedTreeNodeList":2:{s:5:"outer";r:22;s:5:"items";a:0:{}}s:5:"value";s:3:"foo";s:4:"next";r:15;s:4:"prev";r:8;s:6:"ofList";O:12:"FuLinkedList":1:{s:5:"items";a:4:{i:0;r:2;i:1;r:8;i:2;r:22;i:3;r:15;}}}s:6:"ofList";r:30;}s:6:"ofList";O:20:"FuLinkedTreeNodeList":2:{s:5:"outer";r:2;s:5:"items";a:2:{i:0;r:2;i:1;r:8;}}}s:4:"prev";N;s:6:"ofList";r:37;}i:1;r:22;} I'm on Mac OS X.

  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

• 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

• 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

• How to avoid Cartesian product in an INNER JOIN query?

  - by flhe

  I have 6 tables, let's call them a,b,c,d,e,f. Now I want to search all the colums (except the ID columns) of all tables for a certain word, let's say 'Joe'. What I did was, I made INNER JOINS over all the tables and then used LIKE to search the columns. INNER JOIN ... ON INNER JOIN ... ON.......etc. WHERE a.firstname ~* 'Joe' OR a.lastname ~* 'Joe' OR b.favorite_food ~* 'Joe' OR c.job ~* 'Joe'.......etc. The results are correct, I get all the colums I was looking for. But I also get some kind of cartesian product, I get 2 or more lines with almost the same results. How can i avoid this? I want so have each line only once, since the results should appear on a web search.

  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 | 11 12 13 14 15 16 17 18 19 20 21 22  | Next Page >