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

learn more… | top users | synonyms

-4
votes
1answer
25 views

Query to sort through history table

I have the above tables and want a query that will give the results as above. Basically I have a table of history where I want to display single row by adding two column that show prior Group from ...
0
votes
1answer
28 views

How to return null values from a joined table if the matching row is missing?

Here is what I need to achieve: person_id last_name first_name region_id region name 1 barnum phineas 1 maricopa 2 loman ...
1
vote
1answer
78 views

Same query plan, different data set, very different query duration SQL Server 2012

This may be a terrible question, because I'm not sure how information I can include to help. We have data segregated by customer. One customer apparently has higher volume of data. The same query ran ...
1
vote
0answers
31 views

Groupwise max then join

I have a votes table that has many subitems. I have a subitems action table that records what action was performed by which user on which group, on which subitem, on which vote Users can either cast/...
0
votes
1answer
39 views

Help on joining 3 tables Oracle XE 11g

I am trying to return the first name, surname, subject and mark from the following tables: SQL> desc students; Name Null? Type ------------------------ -------- -------------...
1
vote
3answers
67 views

How should INNER JOIN query look?

I am trying to fetch some records using INNER JOIN. The problem is that I am not sure what's the best (recommended) way of doing it. What "pros" and "cons" have the queries shown below? All of them ...
1
vote
1answer
596 views

Does MySQL load the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY?

I have a table that isn't very large row wise (in the 100ks range), but that contains a lot of raw data that is very large in size. Despite having a relatively little number of rows, it's around 1.5GB....
0
votes
0answers
11 views

Find missing associations in many to many relationship [duplicate]

I have client and category tables with a many to many relationship via client_category_association. table: client id name 1 ek 2 do table: category id name 1 un 2 dos 3 tres table: ...
1
vote
1answer
31 views

Join second table based on third table with conditions

I"m using Doctrine for a website that has 3 tables to connect together. In simplest terms, they look like this Contractor - id - name ContractorJob - id - contractor_id - job_id - status Job - id - ...
1
vote
1answer
48 views

Galera MariaDB not joining with IST

Until now, I have a single MariaDB 10.1 node running as Galera Cluster. So, now I want to join the next one. systemctl start mysql initiates the snapshot state transfer (which takes about 10 hours ...
0
votes
2answers
53 views

need help joining two tables with a having count clause

I am a network guy so I apologize for this.... I have two tables I need to join and pull names out of. pt_basic has the patient names and codes I need, ptc_diagnosis has their diagnosis. I need to ...
1
vote
1answer
61 views

Select column values linked to exact set of values in another column

Given the below chats_users table in Postgres 9.5, with integer columns chat_id and user_id, I want to find the chat_id associated with an exact set of user_ids, for example: user_id IN (1, 3) => ...
3
votes
1answer
99 views

DELETE rows which are not referenced in other table

I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group. I want to delete all rows from link_group where no related row in ...
3
votes
1answer
25 views

How does Hash Join works with relational operators other than equality operator?

I have been reading about Hash Join and how does it work in physical level. However, there is some thing that I don't understand (because of my lack of knowledge). This is the algorithm of Hash Join ...
6
votes
2answers
37k views

How to JOIN two table to get missing rows in the second table

In a simple voting system as CREATE TABLE elections ( election_id int(11) NOT NULL AUTO_INCREMENT, title varchar(255), CREATE TABLE votes ( election_id int(11), user_id int(11), FOREIGN KEYs for ...
1
vote
0answers
52 views

Inner join on array columns from 3 tables

I have 2 tables that are related between them using integer data which is stored in arrays. Data table telemetria_data.historico_354677050164513 CREATE TABLE historico_354677050164513 ( imei ...
1
vote
2answers
74 views

How to avoid full table scan when using OR operator on an Index

Essentially, I want to get intersection between 2 tables based on matching IDs with the option of wildcard '*' to return all IDs: Select * from A a inner join B b on b.id = a.id or b.id = '*' ...
1
vote
2answers
102 views

how do I use a value from the select in a joined subquery?

I'm trying to avoid a stored procedure or bringing it into code but they may be my only alternatives. Here's my sql. Does anyone know any SQL voodoo that could make this happen without a stored ...
1
vote
1answer
21 views

MAX and MIN for each car model

I have a problem with a query. I have a db with car and their spec, every model has a different car related to that model with its price. I want to create a query that gives me the min and max price ...
1
vote
1answer
48 views

Creating a view with a Left Outer Join and Where Condition

Let's say I have a User Table and a User Record table which lists Data Sources associated with that User. I want to create a (Sql Server) View that lists all the contents of a User, and an optional ...
0
votes
1answer
30 views

Teradata Join Syntax

I'm having a hard time doing what I want to do here, as SQL (and Teradata SQL on top of that) is not my main forte. I have two queries that I want to join together. I want everything that returns ...
-1
votes
1answer
38 views

Select and Insert for all data - Need help querying [closed]

At a first glance, this is my query INSERT INTO T2 SELECT T1.*, T3.DATETIME, T3.STATEID FROM T1 WHERE T3.DATETIME BETWEEN '01-NOV-15' AND SYSDATE LEFT OUTER JOIN T3 ON (T1....
0
votes
0answers
42 views

Query the database for particular value in a column

I have 2 tables which look like this: Table_1: Contains Item Details ------------------------------------------------------ Item_ID | Location | Arrival_Date | Postage_Date | ... -------------------...
1
vote
1answer
33 views

Get a specific number of rows from parent table?

I have a parent table, foo, and a child table, bar, in a mySQL database. Each row in foo may have zero or more child records in bar. I want to get exactly 15 rows from the parent table, and all ...
3
votes
3answers
76 views

Most recent order from multi join query

Here's the CTE version of the query, but it is returning multiple rows for the same customer. Not just the row containing the most recent open order date: with LatestOPenOrderDate_CTE AS ( Select ...
1
vote
1answer
36 views

Query using joins [duplicate]

I have the table Vendors (VendorID PK, VendorName, VendorContactFName ,.....) I want to find out the VendorName and VendorContactFName for those Vendors whose Contact First Name is different from The ...
0
votes
3answers
127 views

Optimise MySQL SELECT with LEFT JOIN subquery

I have a query which combines a LEFT JOIN and subquery. The dataset is quite big and the time to execute the statement is over 70 seconds. SELECT s.siblings, l.id FROM `list` l ...
0
votes
1answer
60 views

How to group joined tables by aggregate function, specifically by average group-name type?

I'm trying to build a query selecting two columns (category types) from one table JOINED to another table-column displaying average grades as percentage number-types; here is my first attempt: SELECT ...
1
vote
1answer
26 views

right join leaving one row from right table

I am using MariaDB,in my sample database I am having two tables department and emp. I want to display all the department having less than 50 employees. In this case it should display all the ...
0
votes
1answer
625 views

Best Practice For Data Views with Linked Server in SQL Server

In SQL Server, I have to design a View that I can map on a DBML file in order to dynamically retrieve information from the database with multiple conditions to filter. The problem is that I have to ...
0
votes
1answer
61 views

How Can Get Random 2 rows FROM EACH GROUP FROM JOINED TABLE

EXAMPLE Table Dept: dept_id dept_name dept_status --------------------------------------- 1 Sales 1 2 Develpment 1 3 HR 1 ...
2
votes
2answers
73 views

How to prevent PostgreSQL from rewriting OUTER JOIN queries?

My query is: SELECT Acol1, Acol2, Bcol1, Bcol2, Ccol1, Ccol2 FROM tableA LEFT JOIN (tableB FULL JOIN tableC ON (Bcol1 = Ccol1)) ON (Acol1 = Bcol1) EXPLAIN ANALYZE gives ...
0
votes
1answer
30 views

Subquery uses ungrouped column “s.student_id” from outer query

I have the following tables: 1 teacher can have 1 or more students 1 student can have 1 or more homeworks I would like to know in a list of teachers, for each teacher if it has any student with ...
0
votes
2answers
32 views

MySQL Union not showing 2nd query result set

I am doing a union of 2 queries and trying to fetch data on two different columns, however the union is not fetching the value from 2nd result set. Can you please guide me on this. select * From ...
0
votes
1answer
56 views

Join a calendar table and a transaction table

The database (DB2) in my company contain two table : Warehouse_transaction contain item_id, transaction_qty (negative or positive value), transaction_date ; Calendar contain all days between 2012 ...
1
vote
3answers
202 views

Duplicates while doing multiple joins

I have 4 MySQL tables 1. companies - {id*, company} 2. employees - {id*, fname, lname, id_company*} 3. technologies - {id*, technology} 4. company_technologies - {id_technology*, id_company*} Field ...
2
votes
1answer
59 views

SQLite JOIN … USING - rowid works, but then it doesn't

This works. SELECT * FROM Demographics JOIN MeasuresofBirthAndDeath ON Demographics.rowid = MeasuresofBirthAndDeath.rowid; This does not. SELECT * FROM Demographics JOIN MeasuresofBirthandDeath ...
1
vote
1answer
60 views

How to mount a query to join by searching by last date

I have the following tables: TB_PRODUCTS ID COD_PROD Name =============================================== 1 100 Video monitor 2 101 Keyboard 3 102 Mouse ...
1
vote
2answers
47 views

An aggregate may not appear in the WHERE clause

I'm attempting to execute the following query to return a data set where the Updated Date is the most recent. I'm thinking my joins are causing the issue, but I'm not sure, and have not made any ...
2
votes
1answer
24 views

ON clause of JOIN specific syntax?

I've been a bit curious about the syntax of the JOIN operation, especifically the ON clause where we set the condition. Let's say we are performing a join on an existing table and a table we have just ...
5
votes
1answer
110 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 ...
1
vote
1answer
46 views

Count instances of value from one column in another column located in another table

Let's say I have the following tables : Table 1 task | date 100 | 2016-01-01 101 | 2016-01-01 103 | 2016-01-01 Table 2 task2 | date 113 | 2016-01-01 113 | 2016-01-01 103 | 2016-01-01 ...
1
vote
1answer
71 views

PostgreSQL conditional join and value distribution with materialized view

So I essentially have two tables in a database. One which is the base table of all of our media data, and another that has potential override data for spend. Not everything in the display table will ...
5
votes
2answers
125 views

Removing duplicate rows with two or more left join tables

With a query like this SELECT a.id, a.name, COALESCE( json_agg(b.*), '[]'::json ), COALESCE( json_agg(c.*), '[]'::json ), FROM a LEFT JOIN b ON a.id = b.a_id LEFT JOIN c ON a.id = ...
0
votes
0answers
24 views

Is joining two tables from the same DB faster than in separate DBs? [duplicate]

Would a query / view for joining two tables that reside in the same DB perform faster than one table from each DB in the same instance? I'm using SQL Server 2008 R2 and it's a simple inner join.
2
votes
2answers
60 views

Cross-product between table column and input values

I seem to be unable to write an SQL query, which computes the cross-product between a table column and a set of given input values. Something along the lines of: WITH {1,2} as Input Select * From ...
4
votes
1answer
93 views

Table Joined To Itself Appears Inefficient

My ultimate goal is to compare two rows (only two will exist) and for a specific type and determine if its rows values are greater than the other rows type values. The result I came up with seems to ...
1
vote
1answer
53 views

MySQL Query on bad designed table with multiple Selfjoins

i have to create a report out of a bad designed database (I cannot change) It saves different Formulars into a form_values table ---+-------+-----------+------- id | value | form_item | entry ---+--...
8
votes
4answers
522 views

Query to select max value on join

I have a table of Users: |Username|UserType|Points| |John |A |250 | |Mary |A |150 | |Anna |B |600 | and Levels |UserType|MinPoints|Level | |A |100 |...
1
vote
1answer
37 views

How to select rows based on other tables - MySql

I have tables like this: CheckTable (id) (user_id) [0] [403] UserTable (id) (username) [403] [hello] I want to select only the rows from the UserTable that do not have the user id row in the ...