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

Better way to do a multi-join in this SQL Query?

I am trying to pull data from a table (a.table) to join to another table (b.table). For me to do that, I need to join a third table (c.table) to reference between tables Plan_Code and Policy_Riders. Please see the code below USE [CDS] GO SELECT rid...
more »

2017-06-14 20:06 (1) Answers

SQL Implied Null values in joined table

In joining two tables with a one to many relationship, I want a result displaying all the values in the many table with null values in the one table. Here's an example: tbl_Platform: PriKey = PlatformID PlatformID PlatformDesc 1 Application 2...
more »

2017-06-12 22:06 (1) Answers

SQL Query INNER JOIN 4 Tables

i'm trying to show A list of purchased books that have been delivered to members. The list should show member identification number, first name and last name, address, contact number, book serial number, book title, quantity and delivery date. My T...
more »

2017-06-12 13:06 (2) Answers

Obtaining database joined table with limit

Ive been trying to join two tables but only showing a limited amount (2) of results from the joined table. Unfortunately I havent been able to obtain the correct results. These are my tables: Destinations id name ------------ 1 Bahamas 2 Ca...
more »

2017-06-01 06:06 (3) Answers

Sql: Get Attributes by joining 3 tables

This is my database schema: I simply want to Select all the appointments of a patient and get the following result set: I' have tried the following Join: SELECT [User].user_id,name, surname, [Appointment].day,[Appointment].month,[Appointment]....
more »

2017-05-26 18:05 (1) Answers

Linq JS join syntax

I am trying to join an array of dates and values to an array of dates, without filtering out the extra dates. The LinqJS reference here is pretty confusing on how I would actually go about using the join. The question here did not help much either. ...
more »

2017-04-25 21:04 (2) Answers