Group Columns with Left Outer Joins involved
Im trying to group/summarize all my information data together but I can't get me head around it.
Is it because I have Inner joins?
SELECT DISTINCT jh."job-date",jh."company", jh."job-dept", jh."job-route", rc.COSTS, rh.SALES, rh.SALES - rc.COSTS as PROFIT
FROM MSN.PUB."job-hdr" jh
left outer join
(select rh."rec-id", SUM(rh."base-sales-value") as SALES
from MSN.PUB."rec-chg" rh
group by rh."rec-id") rh
on jh."job-id" = rh."rec-id"
left outer join
(select rc."rec-id", SUM(rc."base-cost-value") as COSTS
from MSN.PUB."rec-cost" rc
group by rc."rec-id") rc
on jh."job-id" = rc."rec-id"
jh."job-status" <> 'D' and
jh."job-date" between '2012-04-01' and '2013-03-31'
Group by jh."job-route", jh."company", jh."job-dept",jh."job-date", rc."COSTS", rh."SALES"
This is the results i'm getting out.
job-date company job-dept job-route COSTS SALES PROFIT
2012-04-01 03 AII IBD 793.48 820.53 27.05
2012-04-01 03 AII ICH 20.87 43.35 22.48
2012-04-01 03 AII ICH 130.97 143.64 12.67
2012-04-01 03 AII ICH 291.52 363.63 72.11
2012-04-01 03 AII ICH 384.90 437.44 52.54
I need them summariszed so I don't have so many lines of data?
This question already has an answer here: FULL OUTER JOIN with sqlite 1 answer Of course with the dupes collapsed. I need to do a FULL OUTER JOIN in SQLite and it doesn't seem to support it.
I am using InnoDB. QUERY, EXPLAIN & INDEXES SELECT stories.*, count(comments.id) AS comments, GROUP_CONCAT( DISTINCT classifications2.name SEPARATOR ';' ) AS classifications_name, GROUP_CONCAT( D
Can somebody tell me, why does Linq to Entities translate many to 1 relationships to left outer join instead of inner join? Because there's referential constraint on DB itself that ensures there's a r
I have an SQL query which is working ok to an extent. The LEFT OUTER JOINs work ok, but if there is no record in mdl_modules OR mdl_resource it comes up with NULL. How can I exclude the records from m
I am working with a legacy database tying together multiple tables (4 INNER JOINS and an OUTER LEFT JOIN); because of this and how the tables have to be joined together, it's really hard to consolidat
I have an application that successfully utilizes LINQ to perform LEFT OUTER JOINs several instances; however, in one case it fails to work as expected. Testing in LINQPad (using LINQ-to_SQL) produced
I have a hbm file, which defines the left outer join. Now, I need to create a query which does not require any left outer join. Please help me, what criteria should I use to explicitly remove left out
I have an issue on Oracle (126.96.36.199.0) where too many left outer joins (or rather too many columns in the joined tables) results in the following error: ORA-03113: end-of-file on communication channel
In the SQL query below, I have 2 tables, documents and document user map. In documents table it has columns documentId, documentname and userid; and in doumnent usermap table it has documentid and use
I recently switched from using a local MySQL instance to using a Xeround MySQL instance for my C# application. As such, I've noticed that queries run much more slowly. I'm currently running a left joi
I'm trying to group by multiple columns here - one on each table. It's a scenario where I want to find the top portfolio value for each client by adding their current portfolio and cash together but a
I have the following schema. I can run two queries fairly simply select * from booking_model_assignment join booking_model on booking_model_assignment.booking_model_id = booking_model.id left outer
I know the usage of joins, but sometimes I come across such a situation when I am not able to decide which join will be suitable, a left or right. Here is the query where I am stuck. SELECT count(Ima
I have a situation where I have a stored procedure that does a lot of LEFT OUTER JOINs because I am retrieving two types of games, pools and brackets. They diverge off a main entity called game. As o
I'm trying to get a certain number of records from a table along with their associated data from another table: SELECT a.*, b.* FROM tblA a LEFT OUTER JOIN tblB b ON a.id = b.target WHERE ... ORDER BY
I am trying to develop a query that counts certain values for multiple tables. The query works fine when I am taking the aggregate count of a field with just one left join in place. But when I add ano
When I use left outer join with order by in the below query the query executes infinitely without giving a resultset. However - individually they work fine that is if I change it from left outer join
I have this use case where I get the symbolized deep associations from a certain model, and I have to perform certain queries that involve using outer joins. How can one do it WITHOUT resorting to wri
In the MySQL documentation for joins, a coworker pointed out this gem to me today: RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LE
Suppose I have following tables: T1,T2 and T3. How could I rephrase the following query using only left joins. Select * From T1 Right join T2 On T1.FK2=T2.PK Right join T3 On T1.FK3=T3.PK Following
I'm a bit surprised that these two queries give different results: First Query: SELECT a.number, a.name , b.* FROM Atable a LEFT OUTER JOIN Btable b JOIN Ctable c ON c.number = b.number ON b.number =
I'm experiencing big differences in timeperformance in my query, and it seems the order of which the joins (inner and left outer) occur in the query makes all the difference. Are there some ground ru
I am trying to understand joins and im a bit confused. I know how to join tables using = <= IN exists and not exists I was trying to try and understand the use of INNER JOIN, LEFT OUTER JOIN, USI
I have a table which needs to left outer joined with two different tables. When I put the table twice in the query and join it with it self in the where clause (like the sentence below) it works. I th
I would like to paginate a complex request with at least 2 left joins, but the pagination bundle I'm using (KnpPaginationBundle) can't tell Doctrine how to count the result (which is needed for the pa
I have the following Access 2002 query I'm running through OLE DB in .NET: SELECT TOP 25 tblClient.ClientCode, tblRegion.Region FROM (tblClient LEFT OUTER JOIN tblRegion ON tblClient.RegionCode = tblR
I am attempting outer joins for 3 tables and was wondering if my method is correct and efficient? Models: class GuestCategory(models.Model): profile = models.ForeignKey(UserProfile) category = models.
How can I write the following SQL in LINQ to Entities? SELECT r.rolename, ( CASE WHEN ur.username IS NULL THEN 0 ELSE 1 END ) AS isinrole FROM bgt.roles r LEFT OUTER JOIN bgt.usersinroles ur ON ur.rol
Is there a reason MySQL doesn't support FULL OUTER JOINS? I've tried full outer join syntax in mysql many times and it never worked, just found out its not supported by mysql so just curious as to why
I have a table say table1 which has 3 columns column1, column2 and column3. The column1 and column2 are a FOREIGN KEY with 2 other tables. However the data in column3 is from n number of tables. For
I'm using SQL Server Express and I'm trying to pull different columns from different tables using LEFT OUTER JOIN. It's working great but only if all columns exist. So I've been reading for the past h
I don't get it. I've tried: SELECT table1.name, CONCAT( country, '.', id ) AS table1.code, table2.name FROM tabel1 LEFT OUTER JOIN table2 ON ( table1.code = table2.code ) I need to combine country an
which one has better performance? SELECT *,(SELECT MAX(old) FROM employee WHERE employee.CompanyId = Company.Id) FROM Company or SELECT *,MAX(old) FROM Company LEFT OUTER JOIN employee ON Company.Id
how can i implement left outer join in following code: var showmenu = from pag in pagerepository.GetAllPages() join pgmt in pagerepository.GetAllPageMeta() on pag.int_PageId equals pgmt.int_PageId whe
I dont understand the concept of a left outer join, a right outer join, or indeed why we need to use a join at all! The question I am struggling with, and the table I am working from is here : http://
So I have a fairly involved sql query here. SELECT links_link.id, links_link.created, links_link.url, links_link.title, links_category.title, SUM(links_vote.karma_delta) AS karma, SUM(CASE WHEN links_
I have this code User.find(:all, :limit => 10, :joins => :user_points, :select => users.*, count(user_points.id), :group => user_points.user_id) which generates following sql SELECT u
Below is a relationship between Comments and a user. Each comment has one user so I'm building out a join in the code below. I was wondering how to build this code to only include specific columns in
I'm having a problem moving from a situation where an Outer Join works, to where it fails. Working (pseudo code example) SELECT a.number, a.name, b.ref, c.ref, c.firmref FROM jobs a, teams b LEFT OUTE
SQL FIDDLE LINK FOR THIS DATA What is a jpql equivalent of following SQL query : select * from App left outer join App_Child on (App.id=App_Child.id and App_Child.status = 'active') where App.status='
I'm curious: do databases return JOINs as the results of two (or more) SELECT statements with pointers from (segments of) rows in the left side to (segments of) rows in the right, or do they return re
I have a NewsStories tables which I'm left joining with some related tables. Each News story can have multiple images, categories and addresses. So the query is essentially: SELECT * FROM NewStories L
I have a product entity, which has 0 or 1 BestSeller entities. For some reason when I say: db.Products.OrderBy(p => p.BestSeller.rating).ToList(); the SQL I get has an extra outer join (below)
Cross posted form the codeigniter forum, where I got crickets. I’ve got a query which I use two left joins and several inner joins, it consistently returns a connection reset error in firefox and no e
So I have one table which has employee number (empno), name(ename) and manager number(mgr). The question: List the names of all employees along with the name of their manager and the name of their man
Ok... I always struggle with LEFT JOINS for some reason in SQL. I have a simple query SELECT COUNT(*) as OpenedToday, c.Product_Line, c.Product_Code FROM SFCase as c LEFT OUTER JOIN (SELECT DISTINCT
I'm looking for ways to optimize the following SQL query. I'm using MS SQL Server 2008. I'm thinking if there's any way to consolidate the outer joins it would speed things up...If anyone has any inpu
I am trying to do a FULL OUTER JOIN in MySQL and as we all know that is not possible with their syntax. So, like any normal tinkerer I am using: (T1 LEFT OUTER JOIN T2) UNION (T1 RIGHT OUTER JOIN T2)
I have Table1 and Table2 in the form of IEnumerable. Both the tables have columns Column1 and Column2 I would like to do a left outer join on Column1 and would like to get a count of the rows present
i'm trying to run a query resulting in 2 outer joins on the same table profile_data. So eventually i want a table resulting user | friend | degree | user_value | friend_value, where friend_value is de