How to perform Left Outer Join in Django ORM ?

I have the following models: class CandidateDetail(models.Model): full_name = models.CharField(max_length=128, null=True) email_id = models.CharField(max_length=64, null=True) created_at = models.DateTimeField(auto_now_add=True) modi...
more »

2017-10-27 08:10 (1) Answers

SQL Performance: Filter first or Join first

I am having three tables viz., Employees, Department and Grievance. The Employees table has over a million records. I need to find the details of the employee, his/her department and grievances raised by him/her. I could think of the below two queri...
more »

2017-10-13 12:10 (4) Answers

Joining tables from two Oracle databases in SAS

I am joining two tables together that are located in two separate oracle databases. I am currently doing this in sas by creating two libname connections to each database and then simply using something like the below. libname dbase_a oracle user= e...
more »

2017-10-11 11:10 (2) Answers

JOIN THREE TABLES IN CODEIGNITER

I have three tables 1.article (id,article_title) 2.article_status(id,article_id,stage_id) 3.article_stages(id,stage_name) Each article have multiple insertion in table 2. I want to fetch the last value which indicate the current stage using join ...
more »

2017-10-03 12:10 (4) Answers

Trouble with my Query

I have made a simple database application however I am having trouble adding in one final Column. The amount column needs to be populated with the Yearly Premium from the PRODUCTS table and then divided by either 1, 12 or 26 depending on if it is pay...
more »

2017-09-25 04:09 (1) Answers

T-SQL: Filtering columns by date

I am trying to generate columns based on the date of the activity. I used FULL JOIN before but it only generated duplicated rows even when using CASE. Some guidance on how to approach this would be great. Table 1 is my current data and Result is the...
more »

2017-09-17 17:09 (2) Answers

Postgres planner overcounts when joining 18 tables

Postgres Version: PostgreSQL 9.6.4 on x86_64-alpine-linux-musl, compiled by gcc (Alpine 6.3.0) 6.3.0, 64-bit We are having an issue with an INNER JOIN on 1 table with 18 others, and I would to get some pointers on where to look to fix it (we have ha...
more »

2017-09-11 14:09 (0) Answers

R: rolling self-joins on subsets in data.table

Here a MWE of my problem. Data: library(data.table) #dates in %Y-%m-%d df <- data.table(date=as.Date(c("2001-01-02", "2001-01-02", "2001-01-02", "2001-01-02", "2001-01-02", "2001-01-02", "2001-01-02", "2001-01-02", "2001-01-02", "2001-0...
more »

2017-09-10 20:09 (0) Answers

The "*" is not working in SQL Server 2016

I have a SQL query written in SQL Server version 2000. The query is not running in SQL Server 2016. The query is like below. Select * from ProjPace2 P, ProjPace2 P2 where P.DivCode *= P2.DivCode and P.ProjGrp *= P2.ProjGrp and P.ProjYr *= P2.Pro...
more »

2017-09-10 09:09 (1) Answers

TSQL joins using more than one 'ON' keyword

I came across a stored procedure that contained some syntax I found to be confusing to read. I am hoping someone can help to explain the multiple usage of the 'ON' keyword in JOINS (see the generalized example below). I am having trouble finding a cl...
more »

2017-09-05 21:09 (4) Answers

Rails search for posts by label (tags)

I'm trying to implement search function on a recipe based on the tags it was given. I'm running into issues trying to understand how joining them works and where the code is placed exactly. This is my Recipe model: class Recipe < ApplicationRec...
more »

2017-08-31 17:08 (1) Answers

SQL Server - Trying to de-normalize my table

Apologies for the title but I am trying to do stuff way above my level even for me to explain it. Say I have a table with the variables person, foodstuff and amount: Person food Amount Mike Butter 3 Mike Milk 4 Mike Chicken ...
more »

2017-08-28 15:08 (2) Answers

MYSQL - How to optimize 3 LEFT Joined tables?

Problem: My query works perfectly fine when the rows of each tables are just few (say less than a thousand). But when I tried to populate the db with dummy data, the page loads endlessly. I am using 3 tables to join: students - it holds basic inf...
more »

2017-08-21 10:08 (1) Answers

SQL Left Join - Multiple Rows in Right Table

I am trying to make PHP / MySQL Search Form. When a user wants to search he need to fill 3 input fields: FromDate, ToDate and SucategoryID. There are 2 tables: Items and ItemsBlockDates In Table Items, there is info about the item and it is identifi...
more »

2017-08-13 18:08 (1) Answers

Weird join on on behavior in tsql

I recently found old code that uses JOIN JOIN ON ON instead of the more familiar JOIN ON JOIN ON syntax. DECLARE @a TABLE ( val INT ) DECLARE @b TABLE ( val INT ) DECLARE @c TABLE ( val INT ) INSERT INTO @a VALUES (1),(2),(4) INSERT IN...
more »

2017-08-02 14:08 (2) Answers

Table Left Join

I'm currently trying to get an output from two tables that I want to join and it seems like I have a block in my mind on how to resolve this. Table 1 has products with unique IDs. ID | (other info) ----------------- AA | BB | CC | Table 2 has th...
more »

2017-07-18 09:07 (2) Answers

MySQL return null for non-existent record

I have 2 MySQL tables, which I am required to join and access as a derived table. Basic example with some dummy rows: CREATE DATABASE Test; USE Test; CREATE TABLE TableOne( Id INT auto_increment NOT NULL, SomeField FLOAT, Timestamp DAT...
more »

2017-07-10 18:07 (1) Answers

Join tables in two databases using SQLAlchemy

I am working with two MySQL Databases. I want to join a table from DB 1 with a table from DB2 in SQLAlchemy. I am using automap_base while creating data access layer in sqlalchemy as follows... class DBHandleBase(object): def __init__(self, co...
more »

2017-06-15 11:06 (1) Answers