Search Results

Search found 20031 results on 802 pages for 'full outer join'.

Page 13/802 | < Previous Page | 9 10 11 12 13 14 15 16 17 18 19 20  | Next Page >

• mysql join table - selecting the newest row

  - by cmancre

  Hi, I have the following two MySQL tables TABLE NAMES NAME_ID NAME 1 name1 2 name2 3 name3 TABLE STATUS STATUS_ID NAME_ID TIMESTAMP 1 1 2010-12-20 12:00 2 2 2010-12-20 10:00 3 3 2010-12-20 10:30 4 3 2010-12-20 14:00 I would like to select all info from table NAMES and add most recent correspondent TIMESTAMP column from table STATUS RESULT NAME_ID NAME TIMESTAMP 1 name1 2010-12-20 12:00 2 name2 2010-12-20 10:00 3 name3 2010-12-20 14:00 Am stuck on this one. How do I left join only on the newer timestamp?

  Read the article

• SQL Join only returning 1 row.

  - by kevin

  Not quite sure what I'm missing, but my SQL statement is only returning one row. SELECT tl.*, (tl.topic_total_rating/tl.topic_rates) as topic_rating, COUNT(pl.post_id) - 1 as reply_count, MIN(pl.post_time) AS topic_time, MAX(pl.post_time) AS topic_bump FROM topic_list tl JOIN post_list pl ON tl.topic_id=pl.post_parent WHERE tl.topic_board_link = %i AND topic_hidden != 1 ORDER BY %s I have two tables (post_list and topic_list), and post_list's post_parent links to a topic_list's topic_id. Instead of returning all the topics (where their board's topic_board_link is n), it only returns one topic.

  Read the article

• Dealing w/ Sqlite Join results in a cursor

  - by Bill

  I have a one-many relationship in my local Sqlite db. Pretty basic stuff. When I do my left outer join I get back results that look like this: the resulting cursor has multiple rows that look like this: A1.id | A1.column1 | A1.column2 | B1.a_id_fk | B1.column1 | B1.column2 A1.id | A1.column1 | A1.column2 | B2.a_id_fk | B2.column1 | B2.column2 and so on... Is there a standard practice or method of dealing with results like this ? Clearly there is only A1, but it has many B-n relationships. I am coming close to using multiple queries instead of the "relational db way". Hopefully I am just not aware of the better way to do things. I intend to expose this query via a content provider and I would hate for all of the consumers to have to write the same aggregation logic.

  Read the article

• Left Join not returning all rows

  - by DisgruntledGoat

  I have this query in MySQL: SELECT pr.*, pr7.value AS `room_price_high` FROM `jos_hp_properties` pr LEFT OUTER JOIN `jos_hp_properties2` pr7 ON pr7.property=pr.id WHERE pr7.field=23 The jos_hp_properties table has 27 rows but the query only returns one. Based on this question I think it may be because of the WHERE clause. The jos_hp_properties2 table has fields id, property, field, value, where field is a foreign key to a third table (which I don't need to get data from). Is there a way to select all the rows from the first table, including the value from table #2 where the field is 23 (or NULL if there is no field 23)?

  Read the article

• MySQL Join a Table if Exists Question

  - by swhitlow

  I have a DB which has a table called "config". This table could possibly have a "config_cstm" table that is related by the id to the config table. I was wondering - is there a way to dynamically check for the existence of this table in one simple select statement? In other words, something like: "select * from config (IF EXISTS config_cstm THEN LEFT OUTER JOIN config_cstm ON config.id = config_cstm.id_c" I know how I would go about checking for the existence of an existing table in PHP. I was wanting to do this all in one MySQL statement though. Any suggestions? Thanks!

  Read the article

• left join without duplicate values using MIN()

  - by Clipper87

  I have a table_1: id custno 1 1 2 2 3 3 and a table_2: id custno qty descr 1 1 10 a 2 1 7 b 3 2 4 c 4 3 7 d 5 1 5 e 6 1 5 f When I run this query to show the minimum order quantities from every customer: SELECT DISTINCT table_1.custno,table_2.qty,table_2.descr FROM table_1 LEFT OUTER JOIN table_2 ON table_1.custno = table_2.custno AND qty = (SELECT MIN(qty) FROM table_2 WHERE table_2.custno = table_1.custno ) Then I get this result: custno qty descr 1 5 e 1 5 f 2 4 c 3 7 d Customer 1 appears twice each time with the same minimum qty (& a different description) but I only want to see customer 1 appear once. I don't care if that is the record with 'e' as a description or 'f' as a description. How could I do this ? Thx!

  Read the article

• Best full text search for mysql?

  - by ConroyP

  We're currently running MySQL on a LAMP stack and have been looking at implementing a more thorough, full-text search on our site. We've looked at MySQL's own freetext search, but it doesn't seem to cope well with large databases, which makes it far too slow for our needs. Our main requirements are: speed returning results simple updating of index In addition to the above, our "nice to have"s are: ideally not something that requires adding a module to MySQL plays nicely with PHP (majority of our dev work done using PHP) There seems to be quite a few healthy open-source projects to add fast, reliable full-text search to MySQL, so I'm basically looking for recommendations/suggestions on what you've found to be the most useful product out there, easiest to set up, etc. So far, the list of ones we've been starting to play around with are: Sphinx, C++ based, used by craigslist, thepiratebay Lucene, Java-based Apache project, powers zeoh.com and zoomf.com Solr, Java-based offshoot of Lucene, used to power searches on Digg, CNet & AOL Channels Are there any better ones out there that we haven't come across yet? Can you recommend / suggest against any of the options we've gathered so far? Thanks for your help! Update @Cletus suggested Google's Custom Search Engine. We recently trialled this on a couple of projects, and it's an almost-perfect fit for our needs. The problem is that entries on our site are updated quite regularly, and unfortunately the speed at which entries go in/get updated in Google's index was just too slow and erratic for us to rely on, even with the addition of sitemaps and requested crawl rate changes.

  Read the article

• SQL Full-Text Indexing Issue

  - by Phil

  UPDATE: I have figured out a way using a form of dynamic sql to fix this problem, thanks anyway for any help. Hi, there is something that I need to accomplish with the use of Full-Text Indexing. This is it: The fact of the matter is when I run a query (with a stored procedure) that looks like (with a parameter (@name) that was obviously defined above (not shown here), this parameter is sent to the stored procedure by an asp.net page, from user input): SELECT Name FROMdbo.UsersTable WHERE FREETEXT(Name, @name) Well, the fact of the matter is that a query like this will return values if, say the parameter @name's value is Joe, and say, there are 10 records of names with Joe in them, but if @name's value is just Jo, then it returns nothing, and this is the problem. Say that there are other records in this table that have Jo in them, like for example, Jole, or John. So the real question is, how do I get it to return values that are not full words, or phrases, but just from part of the word/phrase (like I said above)? Like FREETEXT(Name, @name*), which is not allowed to be used as a query, but, you get the idea. Is there a way to accomplish this? I'm sure there must be, I need to figure this out. Thanks for any help.

  Read the article

• Can you recommend a full-text search engine?

  - by Jen

  Can you recommend a full-text search engine? (Preferably open source) I have a database of many (though relatively short) HTML documents. I want users to be able to search this database by entering one or more search words in my C++ desktop application. Hence, I’m looking for a fast full-text search solution to integrate with my app. Ideally, it should: Skip common words, such as the, of, and, etc. Support stemming, i.e. search for run also finds documents containing runner, running and ran. Be able to update its index in the background as new documents are added to the database. Be able to provide search word suggestions (like Google Suggest) Have a well-documented API To illustrate, assume the database has just two documents: Document 1: This is a test of text search. Document 2: Testing is fun. The following words should be in the index: fun, search, test, testing, text. If the user types t in the search box, I want the application to be able to suggest test, testing and text (Ideally, the application should be able to query the search engine for the 10 most common search words starting with t). A search for testing should return both documents. Other points: I don't need multi-user support I don't need support for complex queries The database resides on the user's computer, so the indexing should be performed locally. Can you suggest a C or C++ based solution? (I’ve briefly reviewed CLucene and Xapian, but I’m not sure if either will address my needs, especially querying the search word indexes for the suggest feature).

  Read the article

• Full Text Search like Google

  - by Eduardo

  I would like to implement full-text-search in my off-line (android) application to search the user generated list of notes. I would like it to behave just like Google (since most people are already used to querying to Google) My initial requirements are: Fast: like Google or as fast as possible, having 100000 documents with 200 hundred words each. Searching for two words should only return documents that contain both words (not just one word) (unless the OR operator is used) Case insensitive (aka: normalization): If I have the word 'Hello' and I search for 'hello' it should match. Diacritical mark insensitive: If I have the word 'así' a search for 'asi' should match. In Spanish, many people, incorrectly, either do not put diacritical marks or fail in correctly putting them. Stop word elimination: To not have a huge index meaningless words like 'and', 'the' or 'for' should not be indexed at all. Dictionary substitution (aka: stem words): Similar words should be indexed as one. For example, instances of 'hungrily' and 'hungry' should be replaced with 'hunger'. Phrase search: If I have the text 'Hello world!' a search of '"world hello"' should not match it but a search of '"hello world"' should match. Search all fields (in multifield documents) if no field specified (not just a default field) Auto-completion in search results while typing to give popular searches. (just like Google Suggest) How may I configure a full-text-search engine to behave as much as possible as Google? (I am mostly interested in Open Source, Java and in particular Lucene)

  Read the article

• Simple Self Join Query Bad Performance

  - by user1514042

  Could anyone advice on how do I improve the performance of the following query. Note, the problem seems to be caused by where clause. Data (table contains a huge set of rows - 500K+, the set of parameters it's called with assums the return of 2-5K records per query, which takes 8-10 minutes currently): USE [SomeDb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Data]( [x] [money] NOT NULL, [y] [money] NOT NULL, CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED ( [x] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO The Query select top 10000 s.x as sx, e.x as ex, s.y as sy, e.y as ey, e.y - s.y as y_delta, e.x - s.x as x_delta from Data s inner join Data e on e.x > s.x and e.x - s.x between xFrom and xTo --where e.y - s.y > @yDelta -- when uncommented causes a huge delay Update 1 - Execution Plan <?xml version="1.0" encoding="utf-16"?> <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2100.60" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="100" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0263655" StatementText="select top 100&#xD;&#xA;s.x as sx,&#xD;&#xA;e.x as ex,&#xD;&#xA;s.y as sy,&#xD;&#xA;e.y as ey,&#xD;&#xA;e.y - s.y as y_delta,&#xD;&#xA;e.x - s.x as x_delta&#xD;&#xA;from Data s &#xD;&#xA; inner join Data e&#xD;&#xA; on e.x &gt; s.x and e.x - s.x between 100 and 105&#xD;&#xA;where e.y - s.y &gt; 0.01&#xD;&#xA;" StatementType="SELECT" QueryHash="0xAAAC02AC2D78CB56" QueryPlanHash="0x747994153CB2D637" RetrievedFromCache="true"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="13" CompileCPU="13" CompileMemory="424"> <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /> <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="52199" EstimatedPagesCached="14561" EstimatedAvailableDegreeOfParallelism="4" /> <RelOp AvgRowSize="55" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0263655"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> <ColumnReference Column="Expr1004" /> <ColumnReference Column="Expr1005" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1004" /> <ScalarOperator ScalarString="[SomeDb].[dbo].[Data].[y] as [e].[y]-[SomeDb].[dbo].[Data].[y] as [s].[y]"> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1005" /> <ScalarOperator ScalarString="[SomeDb].[dbo].[Data].[x] as [e].[x]-[SomeDb].[dbo].[Data].[x] as [s].[x]"> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="39" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0263555"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Top RowCount="false" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(100)"> <Const ConstValue="(100)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="39" EstimateCPU="151828" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0263455"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="0" ActualExecutions="1" /> </RunTimeInformation> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </OuterReferences> <RelOp AvgRowSize="23" EstimateCPU="1.80448" EstimateIO="3.76461" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1640290"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="15225" ActualEndOfScans="0" ActualExecutions="1" /> </RunTimeInformation> <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </DefinedValue> </DefinedValues> <Object Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Index="[PK_Data]" Alias="[e]" IndexKind="Clustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="23" EstimateCPU="0.902317" EstimateIO="1.88387" EstimateRebinds="1" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0263655" TableCardinality="1640290"> <OutputList> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> </OutputList> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="15224" ActualExecutions="15225" /> </RunTimeInformation> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> </DefinedValue> </DefinedValues> <Object Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Index="[PK_Data]" Alias="[s]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[SomeDb].[dbo].[Data].[x] as [e].[x]"> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </Identifier> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="([SomeDb].[dbo].[Data].[x] as [e].[x]-[SomeDb].[dbo].[Data].[x] as [s].[x])&gt;=($100.0000) AND ([SomeDb].[dbo].[Data].[x] as [e].[x]-[SomeDb].[dbo].[Data].[x] as [s].[x])&lt;=($105.0000) AND ([SomeDb].[dbo].[Data].[y] as [e].[y]-[SomeDb].[dbo].[Data].[y] as [s].[y])&gt;(0.01)"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="GE"> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> <ScalarOperator> <Const ConstValue="($100.0000)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="LE"> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="x" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="x" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> <ScalarOperator> <Const ConstValue="($105.0000)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="GT"> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[e]" Column="y" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[SomeDb]" Schema="[dbo]" Table="[Data]" Alias="[s]" Column="y" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> <ScalarOperator> <Const ConstValue="(0.01)" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Top> </RelOp> </ComputeScalar> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>

  Read the article

• Nhibernate Criteria Query with Join

  - by John Peters

  I am looking to do the following using an NHibernate Criteria Query I have "Product"s which has 0 to Many "Media"s A product can be associated with 1 to Many ProductCategories These use a table in the middled to create the join ProductCategories Id Title ProductsProductCategories ProductCategoryId ProductId Products Id Title ProductMedias ProductId MediaId Medias Id MediaType I need to implement a criteria query to return All Products in a ProductCategory and the top 1 associated Media or no media if none exists. So although for example a "T Shirt" may have 10 Medias associated, my result should be something similar to this Product.Id Product.Title MediaId 1 T Shirt 21 2 Shoes Null 3 Hat 43 I have tried the following solutions using JoinType.LeftOuterJoin 1) productCriteria.SetResultTransformer(Transformers.DistinctRootEntity); This hasnt worked as the transform is done code side and as I have .SetFirstResult() and .SetMaxResults() for paging purposes it wont work. 2) .SetProjection( Projections.Distinct( Projections.ProjectionList() .Add(Projections.Alias(Projections.Property("Id"), "Id")) ... .SetResultTransformer(Transformers.AliasToBean()); This hasn't worked as I cannot seem to populate a value for Medias.Id in the projections. (Similar to http://stackoverflow.com/questions/1036116/nhibernate-criteria-api-projections) Any help would be greatly appreciated

  Read the article

• CakePHP: 2-level JOIN with one Query

  - by Daniel Magliola

  I have the following models in CakePHP: A Deposit belongs to an Account An Account belongs to a Customer I want to have a list of Deposits, and I need to show the name of the customer (so I have to join through the Customer). I also need to paginate this list. If I set Deposit->recursive = 2, I can get the Customer, however, CakePHP runs one query joining Deposit and Account, and then runs one query per each Deposit, to get the Customer. How can I make it get both models with only one query? I tried this, but it didn't work: $this->paginate = array('joins' => array( array( 'table' => 'customers', 'alias' => 'AccountCustomer', 'type' => 'inner', 'foreignKey' => false, 'conditions' => array('Account.customer_id = AccountCustomer.id') ) )); Any ideas? Thanks! Daniel

  Read the article

• Join 3 tables in 1 LINQ-EF

  - by user100161

  I have to fill warehouse table cOrders with program using Ado.NET EF. I have SQL command but i don't know how to do this with LINQ. static void Main(string[] args) { var SPcontex = new PI_NorthwindSPEntities(); var contex = new NorthwindEntities(); dCustomers dimenzijaCustomers = new dCustomers(); dDatum dimenzijaDatum = new dDatum(); ... CREATE TABLE PoslovnaInteligencija.dbo.cOrders( cOrdersID int PRIMARY KEY IDENTITY(1,1), OrderID int NOT NULL, dCustomersID int FOREIGN KEY REFERENCES PoslovnaInteligencija.dbo.dCustomers(dCustomersID), dEmployeesID int FOREIGN KEY REFERENCES PoslovnaInteligencija.dbo.dEmployees(dEmployeesID), OrderDateID int FOREIGN KEY REFERENCES PoslovnaInteligencija.dbo.dDatum(sifDatum), RequiredDateID int FOREIGN KEY REFERENCES PoslovnaInteligencija.dbo.dDatum(sifDatum), ShippedDateID int FOREIGN KEY REFERENCES PoslovnaInteligencija.dbo.dDatum(sifDatum), dShippersID int FOREIGN KEY REFERENCES PoslovnaInteligencija.dbo.dShippers(dShippersID), dShipID int FOREIGN KEY REFERENCES PoslovnaInteligencija.dbo.dShip(dShipID), Freight money, WaitingDay int ) INSERT INTO PoslovnaInteligencija.dbo.cOrders (OrderID, dCustomersID, dEmployeesID, OrderDateID, RequiredDateID, dShippersID, dShipID, Freight, ShippedDateID, WaitingDay) SELECT OrderID, dc.dCustomersID, de.dEmployeesID, orderD.sifDatum, requiredD.sifDatum, dShippersID, ds.dShipID, Freight, ShippedDateID=CASE WHEN (ShippedDate IS NULL) THEN -1 ELSE shippedD.sifDatum END, WaitingDay=CASE WHEN (shippedD.sifDatum - orderD.sifDatum) IS NULL THEN -1 ELSE shippedD.sifDatum - orderD.sifDatum END FROM PoslovnaInteligencija.dbo.dShippers AS s, PoslovnaInteligencija.dbo.dCustomers AS dc, PoslovnaInteligencija.dbo.dEmployees AS de, PoslovnaInteligencija.dbo.dShip AS ds,PoslovnaInteligencija.dbo.dDatum AS orderD, PoslovnaInteligencija.dbo.dDatum AS requiredD, PoslovnaInteligencija.dbo.Orders AS o LEFT OUTER JOIN PoslovnaInteligencija.dbo.dDatum AS shippedD ON shippedD.datum=DATEADD(dd, 0, DATEDIFF(dd, 0, o.ShippedDate)) WHERE o.ShipVia=s.ShipperID AND dc.CustomerID=o.CustomerID AND de.EmployeeID=o.EmployeeID AND ds.ShipName=o.ShipName AND orderD.datum=DATEADD(dd, 0, DATEDIFF(dd, 0, o.OrderDate)) AND requiredD.datum=DATEADD(dd, 0, DATEDIFF(dd, 0, o.RequiredDate));

  Read the article

• SQL Join to only the maximum row puzzle

  - by Billy ONeal

  Given the following example data: Users +--------------------------------------------------+ | ID | First Name | Last Name | Network Identifier | +--------------------------------------------------+ | 1 | Billy | O'Neal | bro4 | +----+------------+-----------+--------------------+ | 2 | John | Skeet | jsk1 | +----+------------+-----------+--------------------+ Hardware +----+-------------------+---------------+ | ID | Hardware Name | Serial Number | +----+-------------------+---------------+ | 1 | Latitude E6500 | 5555555 | +----+-------------------+---------------+ | 2 | Latitude E6200 | 2222222 | +----+-------------------+---------------+ HardwareAssignments +---------+-------------+-------------+ | User ID | Hardware ID | Assigned On | +---------+-------------+-------------+ | 1 | 1 | April 1 | +---------+-------------+-------------+ | 1 | 2 | April 10 | +---------+-------------+-------------+ | 2 | 2 | April 1 | +---------+-------------+-------------+ | 2 | 1 | April 11 | +---------+-------------+-------------+ I'd like to write a SQL query which would give the following result: +--------------------+------------+-----------+----------------+---------------+-------------+ | Network Identifier | First Name | Last Name | Hardware Name | Serial Number | Assigned On | +--------------------+------------+-----------+----------------+---------------+-------------+ | bro4 | Billy | O'Neal | Latitude E6200 | 2222222 | April 10 | +--------------------+------------+-----------+----------------+---------------+-------------+ | jsk1 | John | Skeet | Latitude E6500 | 5555555 | April 11 | +--------------------+------------+-----------+----------------+---------------+-------------+ My trouble is that the maximum "Assigned On" date for each user needs to be selected for each individual user and used for the actual join ... Is there a clever way accomplish this in SQL?

  Read the article

• Mysql : get data from 2 tables (need help)

  - by quangtruong1985

  Assume that I have 2 tables : members and orders (Mysql) Members : id | name 1 | Lee 2 | brad Orders : id | member_id | status (1: paid, 2: unpaid) | total 1 | 1 | 1 | 1000000 2 | 1 | 1 | 1500000 3 | 1 | 2 | 1300000 4 | 2 | 1 | 3000000 5 | 2 | 2 | 3500000 6 | 2 | 2 | 3300000 I have a sql query : SELECT m.name, COUNT(o.id) as number_of_order, SUM(o.total) as total2 FROM orders o LEFT JOIN members m ON o.member_id=m.id GROUP BY o.member_id which give me this: name | number_of_order | total2 Lee | 3 | 3800000 brad | 3 | 9800000 All that I want is something like this : name | number_of_order | total2 | Paid Unpaid | Paid Unpaid ------------------------------------------------ Lee | 3 | 3800000 | 2 1 | 2500000 1300000 ------------------------------------------------ brad | 3 | 9800000 | 1 2 | 3000000 6800000 ------------------------------------------------ How to make a query that can give me that result? Thanks for your time!

  Read the article

• Help with a Join in Rails 3

  - by Adam Albrecht

  I have the following models: class Event < ActiveRecord::Base has_many :action_items end class ActionItem < ActiveRecord::Base belongs_to :event belongs_to :action_item_type end class ActionItemType < ActiveRecord::Base has_many :action_items end And what I want to do is, for a given event, find all the action items that have an action item type with a name of "foo" (for example). So I think the SQL would go something like this: SELECT * FROM action_items a INNER JOIN action_item_types t ON a.action_item_type_id = t.id WHERE a.event_id = 1 AND t.name = "foo" Can anybody help me translate this into a nice active record query? (Rails 3 - Arel) Thanks!

  Read the article

• Join Query Help

  - by John

  Hello, The query below works well. It pulls data from two MySQL tables, "submission" and "login." I would like to also pull data from a third table called "comment" in the same database. The table "comment" has the following fields: commentid, loginid, submissionid, comment, datecommented Two of the fields in the table "login" are called "loginid" and "username." In the query below, I would like to count all "commentid" in "comment" where "loginid" equals the "loginid" in "login" where "username" equals "$profile." How can I do this? Thanks in advance, John $sqlStr1 = "SELECT l.username, l.loginid, s.loginid, s.submissionid, s.title, s.url, s.datesubmitted, s.displayurl, l.created, count(s.submissionid) countSubmissions FROM submission AS s INNER JOIN login AS l ON s.loginid = l.loginid WHERE l.username = '$profile'";

  Read the article

• MySql left join on several regs

  - by egidiocs

  Hi there! I have this table1 idproduct(PK) | date_to_go 1 2010-01-18 2 2010-02-01 3 2010-02-21 4 2010-02-03 and this other table2 that controls date_to_go updates id | idproduct(FK) | prev_date_to_go | date_to_go | update_date 1 1 2010-01-01 2010-01-05 2009-12-01 2 1 2010-01-05 2010-01-10 2009-12-20 3 1 2010-01-10 2010-01-18 2009-12-20 4 3 2010-01-20 2010-02-03 2010-01-05 So, in this example, for table1.idproduct #1 2010-01-18 is the actual date_to_go and 2010-01-01 (table2.prev_date_to_go, first reg) is the original date_to_go . using this query select v.idproduct, v.date_to_go, p.prev_date_to_go original_date_to_go from table1 v left join produto_datas p on p.idproduto = v.idproduto group by (v.idproduto) order by v.idproduto can I assume that original_date_to_go will be the first related reg of table2? idproduct | date_to_go | original_date_to_go 1 2010-01-18 2010-01-01 2 2010-02-01 NULL 3 2010-02-21 2010-01-20 4 2010-02-03 NULL

  Read the article

• Customizing Hibernate Criteria - Adding conditions to a left join

  - by Douglas Ferguson

  I need to be able to do the following: Select * from Table1 left join Table2 on id1 = id2 AND i1 = ? Hibernate criteria doesn't allow be to specify the i1 = ? part. The existing code is using hibernate criteria and it would be a huge refactor to swap out for HQL Does anybody have any tips how I could implement this differently or any way to override the Hibernate Criteria? I'm not opposed to cracking open hibernate and modifying, but when I began to dig it, there seems to be layers upon layers of abstractions. I never found the the point where SQL is actually generated...

  Read the article

• Doctrine Join DQL

  - by koko

  I's like to do a join between 2 tables on a specific ID. At the moment, I have this DQL: $q = Doctrine_Query::create() ->select('e.*, i.itemName, i.itemtypeId') ->from('Model_EventItem e') ->leftJoin('Model_Item i ON e.itemId = i.itemId') ->where('e.eventitemId = ?', $event->eventId) ->orderBy('i.itemName ASC'); The result is empty, although my eventId has a value ... Can you help me please? I there somewhere a tutorial on DQL-joins? I don't get it right with the help of the Doctrine documentation. Thanks! PS I have doctrine working in combination with Zend Framework.

  Read the article

• mySQL Left Join on multiple tables

  - by Jarrod

  Hi I'm really struggling with this query. I have 4 tables (http://oberto.co.nz/db-sql.png): Invoice_Payement, Invoice, Client and Calendar. I'm trying to create a report by summing up the 'paid_amount' col, in Invoice_Payment, by month/year. The query needs to include all months, even those with no data There query needs the condition (Invoice table): registered_id = [id] I have tried with the below query, which works, but falls short when 'paid_date' does not have any records for a month. The outcome is that month does not show in the results I added a Calendar table to resolved this but not sure how to left join to it. SELECT MONTHNAME(Invoice_Payments.date_paid) as month, SUM(Invoice_Payments.paid_amount) AS total FROM Invoice, Client, Invoice_Payments WHERE Client.registered_id = 1 AND Client.id = Invoice.client_id And Invoice.id = Invoice_Payments.invoice_id AND date_paid IS NOT NULL GROUP BY YEAR(Invoice_Payments.date_paid), MONTH(Invoice_Payments.date_paid) Please see the above link for a basic ERD diagram of my scenario. Thanks for reading. I've posted this Q before but I think I worded it badly.

  Read the article

• HQL join after output of subquery

  - by user350374

  Hey, Say I have 3 classes class foo1 { List prop1;} class foo2 { foo3 prop2;} class foo3{ int Id;} now I want to select distinct foo2's from foo1 and get Id of foo3. I need to write a HQL stmt for the same. I tried the following select c.Id from (select distinct(a.prop1) from foo1.prop1 as a ) as b inner join b.prop2 as c This throws an 'Antlr.Runtime.NoViableAltException How do you suggest I should go about the same. Please note the inner subquery is working fine. So if I have made some mistakes in there please ignore that. I need a method to do the latter. Thanks in Advance.

  Read the article

• Rails has_and_belongs_to_many join across 3 tables

  - by Birdman

  I have a three models: listing, category, and site. There is a many to many relationship between listing and site and there is a many to many relationship between listing and category. A listing thus belongs to one or more sites and one or more categories (a listing can appear on multiple sites and multiple categories). Given a site id and a category id, I want a set of all the listings. I understand how to get the listings from a site id (listings = site.listings) and how to get the listings from a category id (listings = category.listings), but how do I get the triple join of the set of listings from both a site id and category id? The sql would look something like (sit_id and cat_id are input): select l.name from listings l, categories_listings cl, listings_sites, ls where cl.category_id = cat_id and ls.site_id = sit_id and cl.listing_id = l.id and ls.listing_id = l.id Thanks in advance

  Read the article

• join two oracle queries

  - by coder247

  I've to query from two tables and want one result.. how can i join these two queries? First query is querying from two tables and the second one is only from one. select pt.id,pt.promorow,pt.promocolumn,pt.type,pt.image,pt.style,pt.quota_allowed,ptc.text,pq.quota_left from promotables pt,promogroups pg ,promotablecontents ptc ,promoquotas pq where pt.id_promogroup = 1 and ptc.country ='049' and ptc.id_promotable = pt.id and pt.id_promogroup = pg.id and pq.id_promotable = pt.id order by pt.promorow,pt.promocolumn select pt.id,pt.promorow,pt.promocolumn,pt.type,pt.image,pt.style,pt.quota_allowed from promotables pt where pt.type='heading'

  Read the article

< Previous Page | 9 10 11 12 13 14 15 16 17 18 19 20  | Next Page >