FAQ Database Discussion Community


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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; }...