FAQ Database Discussion Community


Left outer join in SQL Server 2012 LocalDB not working?

sql,sql-server,outer-join,sql-server-2012-localdb
Using SQL Server 2012 (LocalDB), I have three tables: BESEXT.COMPUTER BESEXT.ANALYSIS_PROPERTY BESEXT.ANALYSIS_PROPERTY_RESULT These contains following info: BESEXT.COMPUTER: Mapping between ComputerIDs and ComputerNames BESEXT.ANALYSIS_PROPERTY: List of properties that can be mapped to a computer BESEXT.ANALYSIS_PROPERTY_RESULT: List of values of properties for a computer First, I perform the following query: SELECT AR.ComputerID,...

SQL: left join and sum: Arithmetic overflow error converting expression to data type int

sql-server,aggregate-functions,outer-join
I have this query: select p.UserName, sum(b.PercentRials) as amount, sum(r.Amount) as Pays from bills b inner join UserProfiles p on b.PayerUserName=p.UserName left outer join PayReceipts r on p.UserName=r.UserName where p.[Percent]>0 and b.PayDate>'2014-11-20' group by p.UserName I get this error when running it: Msg 8115, Level 16, State 2, Line 1...

Query joining null values (left outer join)

sql,oracle,join,outer-join
In continuation of what was asked on the post: Ora SQL Query: joining without references Now I have the following case: The idea is to get the combination from TABLE_M by the inputs in TABLE_A. For example. The record 1 (CODE = 1) in TABLE_A corresponds to COMBINATION1 while going...

MS Access multi (INNER, LEFT & RIGHT) JOIN query

sql,ms-access,join,ms-access-2010,outer-join
Ok, here's the thing. I have the Following tables involved : > YEARS ------------------------------ ID YEAR ACTUAL ------------------------------ 1 2014-15 TRUE 2 2015-16 FALSE > SHOPS ------------------------------ ID NAME ... ------------------------------ 1 ThisShop ... > ITA ------------------------------ ID YEAR_ID SHOP_ID ------------------------------ 1 1 1 2 1 2 ... > INSPECTORS...

Full outer join on multiple tables in PostgreSQL

postgresql,join,outer-join
In PostgreSQL, I have N tables, each consisting of two columns: id and value. Within each table, id is a unique identifier and value is numeric. I would like to join all the tables using id and, for each id, create a sum of values of all the tables where...

SQL OUTER JOIN with empty fields (null) where no result

mysql,outer-join
Hulloes SO! Okay, so my case is this: I have a db where I need to SELECT from 4 different tables and get results where my foregin keys in one table matches the ids in my other 3 tables. Structure: CREATE TABLE IF NOT EXISTS `languages` ( `id` int(11) NOT...

TSQL: Count on outer joined tables is producing incorrect results

sql-server-2008,tsql,count,outer-join
Producing a report on SQL Server 2008. I have joined one table with five others using LEFT OUTER JOIN. When I run a count against those other tables, I get incorrect data. I know WHY, but I'm unsure how to fix it. The query is tracking admissions candidates for a...

outer join to select

sql,oracle,outer-join
I have select that output 2 columns, region and count. In another table (site_to_site_address) I have full list of regions and I need to join column with full list, but save COUNT condition, if count < 0, it should be space in front of region, if count > 0, should...

How can I get a count from a table joined to a Left Outer Join?

sql,sql-server,join,outer-join
I have three tables. A table of tests (AdminTest), a table of tests that belong to a user (UserTest) and a table of questions that belongs to each of the user tests (UserTestQuestion): AdminTest CREATE TABLE [dbo].[AdminTest] ( [AdminTestId] INT IDENTITY (1, 1) NOT NULL, [Title] NVARCHAR (100) NOT NULL...

LEFT OUTER JOIN on 3 tables. Why does this SQL code give error 106

sql,outer-join
Why does the MySQL query below give error 1066 - Not unique table/alias: 'MOZ' SELECT * FROM GA, MOZ, SF LEFT OUTER JOIN MOZ ON GA.Page = MOZ.URL LEFT OUTER JOIN SF ON GA.Page = SF.Address ...

Get unmatched records without using oracle minus except not in

oracle,plsql,inner-join,outer-join
Actually I have two table and each having column name, I just want the result which are not there in Table2 Table1 ---- Name --- |A| |B| |C| |D| Table2 ------ |Name| ----- |A| |B| Answer |C| |D| I am able to do it by using minus select name from...

SQL Outer Join - improper execution

mysql,sql,join,left-join,outer-join
I am working with learning SQL, I have taken the basics course on pluralsight, and now I am using MySQL through Treehouse, with dummy databases they've set up, through the MySQL server. Once my training is complete I will be using SQLServer daily at work. I ran into a two-part...

RIGHT OUTER JOIN and WHERE when there are matching rows

sql,sql-server,join,outer-join
I am having issues understanding the RIGHT OUTER JOIN logic. We have two tables of data - Process and Resource Process Resource ProcessID ResourceID Date ResourceID ResourceName 1 1 01/01/2015 1 Resource 1 2 1 02/01/2015 2 Resource 2 3 2 02/01/2015 We created the SELECT statement SELECT * FROM...

Django ORM Cross Product

sql,django,orm,outer-join
I have three models: class Customer(models.Model): pass class IssueType(models.Model): pass class IssueTypeConfigPerCustomer(models.Model): customer=models.ForeignKey(Customer) issue_type=models.ForeignKey(IssueType) class Meta: unique_together=[('customer', 'issue_type')] How can I find all tuples of (custmer, issue_type) where there is no IssueTypeConfigPerCustomer object? I want to avoid a loop in Python. A solution which solves this in the DB would...

Matrix Transformation in R - from aggregate output to outer-like matrix

r,matrix,aggregate,transformation,outer-join
I need to transform the output of an aggregate (mean) into a matrix outer-like style. data(mtcars) aggregate(disp ~ cyl + gear, data = mtcars, FUN = mean ) cyl gear disp 4 3 120.1000 6 3 241.5000 8 3 357.6167 4 4 102.6250 6 4 163.8000 4 5 107.7000 6...

Oracle's OUTER JOIN (+) on string - Migration PostgreSQL

oracle,postgresql,join,database-migration,outer-join
I'm migrating a client's software database from Oracle to PostgreSQL, and I have some trouble understanding a query, what it does, and consequently how to migrate it. The query is: SELECT * FROM TBL1, TBL2, TBL3, TBL4 WHERE TBL3.Project_ID = TBL1.Project_ID AND TBL2.Type_ID = TBL1.Type_ID AND TBL4.PROPERTY_NAME(+)='Id' AND TBL4.Entity_ID(+)=TBL1.Entity_ID And...

Join a table two times on different fields

mysql,outer-join
topics: title, poster_id (int), last_poster(int), date players: id, name SELECT *, f.name AS forum FROM topics t LEFT JOIN forums f ON f.id = t.forum_id LEFT JOIN players p ON p.id = t.poster_id In topics I have two fields with the ID of the author of the topic (poster_id), and...

FULL JOIN with =any doesn't use indexes

sql,postgresql,indexing,outer-join,postgresql-performance
Using Postgres 9.3.5, I can't seem to get a full outer join with an =any where-clause to use the relevant indexes. A minimal example: create table t1(i int primary key, j int); create table t2(i int primary key, j int); insert into t1 (select x,x from generate_series(1,1000000) x); insert into...

Sum of outer products multiplied by a scalar in MATLAB

matlab,vectorization,product,outer-join
I would like to vectorize the sum of products hereafter in order to speed up my Matlab code. Would it be possible? for i=1:N A=A+hazard(i)*Z(i,:)'*Z(i,:); end where hazard is a vector (N x 1) and Z is a matrix (N x p). Thanks!...

Using a Combination of INNER and FULL OUTER Joins to Get Desired Data Set

sql-server-2008,join,inner-join,outer-join
I’ve got 5 Tables: Patients Table 'PAT' Patient Orders 'ORD' Patient Comments 'Comm' Patient Antigens 'Ant' Patient MRNs 'MRN' Information in the following four tables: Pat has all of our patients information Ord only has orders from the last four years, joined to the Pat table on Patient ID Comm...

Join collection in QueryOver with projections

c#,nhibernate,outer-join,nhibernate-queryover,nhibernate-projections
I want to retrieve the list of short User versions with their Characters collection (short version too). I use QueryOver with projections. public class User { public virtual int Id { get; set; } public virtual string Nickname { get; set; } public virtual ISet<Character> Characters { get; set; }...