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

learn more… | top users | synonyms

1
vote
3answers
66 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
581 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 ...
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
26 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
38 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
47 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
59 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
77 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
24 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
35k 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
42 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
62 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
97 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
38 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
25 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
33 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 ...
0
votes
0answers
41 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
81 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
55 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
24 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
589 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
57 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
65 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
22 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
31 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
49 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
181 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
44 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
58 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
109 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
45 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
66 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
90 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
50 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
404 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 ...
1
vote
0answers
32 views

MySQL Update column with case on joined tables

I have 2 tables and I want to update table 1 using a case check on table 2: update cms.Clients c #c_id unique join cms.ClientDomains d #c_id not unique on c.c_id = d.c_id set c.country = case ...
1
vote
2answers
54 views

A join that uses ordered phyical records is more efficient than others?

If i execute a join that uses ordered phyical records, it will be more efficient than a join that does not have the physycal records ordered? References are welcome.
0
votes
0answers
37 views

SQL Joining across multiple tables

I'm trying to query across multiple queries at once, it is fairly easy to run queries one after the other but that is not what I should be doing in this case. I have one free variable and the rest of ...
0
votes
2answers
53 views

TSQL Delete All dates and stores data

This might sound like a stupid question but I can't seem to find a better way. It's a posting system where stores send us info on a daily basis (can be for multiple days). That info is placed in ...
1
vote
4answers
138 views

Selecting data from another table using a foreign key [duplicate]

I have two tables A & B. Table A id | name | num Table B id | date | roll id in table B is a foreign key which relates to the id in table A. I want to get the contents of table B where id ...