An SQL join clause combines records from two or more tables or views.

learn more… | top users | synonyms

1
vote
1answer
21 views

How to join fixed row table against mapping table and reveal unmapped records

I have two tables and I am having trouble figuring out how to join them to meet my projection needs. Here is a scenario that illustrates the problem 1. The first table has a fixed set of values/rows; ...
2
votes
2answers
27 views

Get all (non repeated) distribuidores with their paises given a producto_solicitud

I have this schema: And as title says I need to get all records from fabricante_distribuidor by given producto_solicitud.id value. I'm doing in this way: SELECT ...
1
vote
1answer
41 views

How to join a table with a table valued function?

I have a user defined function: create function ut_FooFunc(@fooID bigint, @anotherParam tinyint) returns @tbl Table (Field1 int, Field2 varchar(100)) as begin -- blah blah end Now I want to join ...
5
votes
1answer
67 views

Sort by number of related rows in referencing table

Let there be two tables: Users id [pk] | name --------+--------- 1 | Alice 2 | Bob 3 | Charlie 4 | Dan Emails id | user_id | email ----+---------+------- 1 | 1 ...
-2
votes
0answers
11 views

MariaDb join 2 table with diffrence storage engine [closed]

As you know, MariaDb has a lot of special storage engine like: Cassandra, TokuDb, ... Can I join 2 table with different storage engine in one SQL query? eg: 1 cassandra join 1 tokudb,...
0
votes
1answer
19 views

SQL query to aggregate data from three tables

I have the following three tables: The "users" table: user_id first_name last_name username password The "users_addresses" table: address_id user_id street country_id email The "countries" ...
1
vote
1answer
20 views

MySQL join for two queries

I have the following two queries... mysql> select id, title from events; +----+-----------------------------------------------------------------------------------------------+ | id | title ...
3
votes
1answer
257 views

Sql server table insert performance optimization

Setting In a datawarehouse, I am joining a fact table to 20 dimensions. The fact table has 32 million rows and 30 columns. This is a temporary staging table so I don't have to deal with other users ...
2
votes
2answers
68 views

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES('{"parent":null,"children":[2,3]}'); INSERT INTO test(data) ...
0
votes
0answers
24 views

MySQL Multi Count and Filter

Table: Event ============== ID OldUserID NewUserID Date Table: User ============== ID Forename Lastname .... How can i filter by date column like so... this code is pseudo... SELECT ...
0
votes
1answer
24 views

optimize my query mysql

I have the following query: SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink FROM director_movie as d LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId WHERE d.castName ...
0
votes
1answer
25 views

Simple left outer join leaving me with inner join

So I have a table with each record being a change to an object. e.g. in the app you open your project with a project id in this project you edit an object name hit save that generates new record ...
0
votes
1answer
50 views

Massive joins Vs Updating temp table

I'm working on a query that essentially aggregates a slew of tables together. The first method I tried was to inner join all of the tables into one nice big query that pretty much looked like this: ...
0
votes
2answers
48 views

Summarizing Pay Checks Based on Employee's Job Code

What I'm trying to do is summarize an employee's earnings for each job code they worked in for the year. Employees are assigned a job code based on an effective date. I'm trying to line up the job ...
2
votes
2answers
38 views

Joining two Mysql VIEWs takes a very long time

I have a textbox (for which I used jQuery auto-completion) in my PHP form where user can insert an actor/actress name and then by clicking "Search" button, a new window is opened showing list of ...
2
votes
2answers
50 views

How can I simplify this UNION?

Here's the case - I have the following query: SELECT DISTINCT post_id FROM ( ( SELECT DISTINCT rp.post_id, pm.meta_value AS premiere_date FROM wp_related_people AS rp JOIN ...
0
votes
2answers
55 views

Companies and addresses MySQL [on hold]

I've got a database with 2 tables. companies addresses These are my tables. Adresses adress_id adress_adress adress_adressnumber adress_zipcode adress_city adress_postbus adress_country ...
1
vote
1answer
44 views

Join select queries on an id in SQL Server

I have three tables that SELECT from and I'd need to join the results. It feels like this should be a rather easy task but I struggle with the syntax. Working on Microsoft SQL Server Enterprise ...
1
vote
1answer
37 views

Select two aggregate functions on the same column using different criteria - performance

Dear stack exchange users, first question here. The database in question is a service database where a user hires a service. Now I would like to show the person that was hired an overview of how many ...
1
vote
1answer
40 views

How to manipulate join order of PostgreSQL optimizer?

I am working on table join order optimization. While query processing, I want to get table join order generated by optimizer, and update it by using my own algorithm in PostgreSQL? How can I update ...
0
votes
1answer
63 views

Query optimization for multiple joins postgres

ER Diagram Brief description The database to be used is postgres. The database might contain upto 2 million AccountHolder records. Since the AccountHolder table is accessed and updated frequently ...
0
votes
1answer
32 views

Updating MySQL innodb table based on multiple joins

I have a table with the following (slightly simplified) structure CREATE TABLE `oak_relation` ( `o_id` int(10) unsigned NOT NULL, `k_id` bigint(20) unsigned NOT NULL, `initial` float unsigned NOT ...
1
vote
2answers
27 views

MySQL improve query syntax

I have this simple MySQL query: SELECT * FROM table1 INNER JOIN table2 ON table2.col = table1.id WHERE table1.id = '123' id is the primary key in table1. table2 has composite primary key of two ...
0
votes
0answers
25 views

Join does not not use index in PostgreSQL?

I have a statement that selects from two tables news and status. It seems that the index on the column status of the table news is not used. This table has about 70.000 rows and the query costs too ...
1
vote
2answers
56 views

Equality in JOIN or WHERE clause? Efficiency and style

Of the two queries below, which is the most efficient for postgresql? And which has better style in terms of readability, etc. The difference is the placement of the statement doctor.type != ...
0
votes
0answers
31 views

“DISTINCT” JOIN in mySQL [duplicate]

+----+-------------+-----------+---------+ | id | login_ip | hostname | user_id | +----+-------------+-----------+---------+ | 1 | 192.168.0.4 | 'host 1' | 1 | | 2 | 192.168.0.4 | 'host ...
1
vote
1answer
46 views

Large Data Long Query A Lot of Joins horibble performance

I have 3 Tables Person, Family, City Person Table (6 Million Rows) id (primary key auto_increment unsigned) family_id (index) city_id (index) full_name first_name (index) last_name (index) ...
2
votes
1answer
36 views

Best way of finding rows referencing a given id on PostgreSQL

I'm trying to find if there would be anything violating the foreign key constraint when deleting a row, to check if I can delete it or not (can't use ON DELETE CASCADE) For that, I have a piece of ...
0
votes
1answer
29 views

Stacked Left Joins or Left Join inside a Subquery?

OK, this is a fun one. Designing a database for a gaming system. I have 3 tables that are relevant for the query I'm trying to work on right now. character_skill_log with the relevant fields of id, ...
0
votes
1answer
43 views

MySQL update one table from another joined many-to-many

I have two tables, events and plays joined many-to-many by a third table plays_in_events. Where the events.Name field is blank, I want to set it equal to the name of the play that was performed in ...
0
votes
0answers
32 views

Receiving duplicate results in one condition of JOINED query

I always end up here when Googling for solutions, so I thought I'd cut out the middleman since I'm unable to fix this via searching/forums. It's my first time posting, so please forgive any faux pas ...
3
votes
4answers
815 views

Self Join? Or am I wrong?

We have a table that we use to store customers and items. We need to be able to find customers that have bought one item and have not bought another item: customer item Sally 1 Sally ...
1
vote
1answer
31 views

Help on MySQL joins

I'm building a system to monitor currency prices. Part of my database structure looks like this: My system is trying to pull the api ticker for each exchange with this query: SELECT distinct ...
0
votes
1answer
111 views

Merging tables, but ignoring duplicate columns

I have two tables I want to merge: city1(id, city, state, zip, lat, lon, county) city2(id, city, state) The tables contain overlapping city and state, but not primary keys. I can't figure out how ...
1
vote
1answer
44 views

Left outer join not returning all columns in a grouping query

I'm trying to query all users to find the price of orders in the last month. I'm doing this on postgresql. Here's a toy database to show my problem: I have a 'people' table and a 'orders' table. I'm ...
2
votes
1answer
86 views

SELECT query with DISTINCT on a table-structure for graphs is very slow

I have 2 tables where nodes contains about 60m rows and edges about 500m rows (both growing fast). These two tables represent a directed graph. CREATE TABLE edges ( node_from bigint NOT NULL, ...
3
votes
1answer
51 views

How to join by most recent preceding date?

Imagine I have a database with historic price data and a second table with dates Item | Price | Date Customer | Item | date ------------------------- --------------------------- ...
0
votes
1answer
60 views

Select Information from All Tables [closed]

EER Diagram Question List the customer number, customer name (first and last), order number, order date, employee number, employee name (first and last), product number, product name, and order ...
2
votes
1answer
44 views

(How) can I return the most specific row on a join?

I'm trying to work out a query that can parse through a table of prices (and exceptions to those prices) to yield the applicable price for a product, ideally using a join. I'm on PostgreSQL. For ...
0
votes
0answers
19 views

PostgreSQL SELECT and JOIN question [duplicate]

Trying to fix the previous post text formatting issue there was and I did not recognize: I'm using PostgreSQL under ubuntu 14.04. I have two tables as below: create table material_base_name ( ...
0
votes
0answers
28 views

SQL JOIN excluding duplicate

I'm using PostgreSQL under ubuntu 14.04. I have two tables as below: create table material_base_name ( id bigserial, base_name varchar(50), constraint pk_materials_base_name primary key (id) ); ...
0
votes
1answer
45 views

Help with optimising a sql query on two partitioned tables

I have a SQL query that I am running on two very large tables in SQL Server 2012 Enterprise The two tables are ACKs and Logs. Both are partitioned on their time by day column with the appropriate ...
0
votes
2answers
110 views

SQL join with multiple columns as FK to same list table

I have a table that has 5 references to a list table for normalization of the first table. So in the table 1 a row looks line other columns..., reference_1, reference_2, reference_3, reference_4, ...
0
votes
0answers
14 views

Handle a Search on 2 Big Tables

Hello i want to realize a exchange system with 2 tables. 1 Table for "the user is looking for" 1 Table for "the user has to share" the Tables are very simple id bigint user_id bigint object_id ...
0
votes
0answers
32 views

MYSQL Tables Join Filtering For Favorites

I am in need of some help figuring out the best way to join separate tables I am using. I am essentially trying to output a list of schools (first table in MYSQL) where one column is an empty star ...
0
votes
1answer
37 views

Joining a column from another table with InnoDB engine

I'm using MySQL with InnoDB and I want to join the location column from products table into a productStock table. I have this table 'products' mysql> select * from products; ...
0
votes
2answers
46 views

Subquery or multiple left joins, or both?

I have two tables, for simplicity table1, with id as Primary key, and a field name, and table2 with the same. Each table has quite a few records, but during a complicated form process, the user is ...
0
votes
2answers
42 views

Combine two queries from 1 table, 1 to Many

I have the following queries select o.increment_id, o.customer_id, round(o.base_grand_total * o.base_to_global_rate,2) as revGBP, DATE_FORMAT(o.created_at, '01/%m/%Y') as 'MonthofOrder', from ...
0
votes
1answer
51 views

How to optimize a very slow query with joins and group by?

I have this GTFS database : All the ***_id columns are indexed, and there are foreign keys between tables (such as a FK on trips.route_id to routes.route_id). So basically the link between a stop ...
0
votes
3answers
245 views

Alternatives for a LEFT OUTER JOIN query

Problem I have a SQL statement with an LEFT OUTER JOIN which works fine on our Microsoft SQL Server. My problem is that i have to be compatible with H2 Database and this one got a bug with OUTER ...