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

learn more… | top users | synonyms

0
votes
1answer
97 views

SQL query to count number of payments made in a time period with data from two different table

Could someone please tell me how to write sql query for the following question? I am not able to get how to write the condition where it selects those user_id which is present on the said date and ...
4
votes
1answer
195 views

Select multiple columns from subquery - Connect by

I have a table which manages folder structure. Hence it has a parent-child relationship. We trigger a query to sum all documents in the complete heirarchy: SELECT folder_name name, ...
0
votes
1answer
33 views

How to get average of views before clicking on advertisement?

I've got two tables containing information about viewing and clicking on advertisement by each user: Clicks: userID Action Time 123 c 2016-01-08 01:57:00 123 c 2016-01-11 ...
3
votes
3answers
48 views

Query to find missing status events

I'm hitting a road block as the best way to query a related table. I have two tables: main and status_history. Right now, I can identify records from main with no status_history at all, but I need to ...
0
votes
2answers
13 views

MS sql query value one table but not another join table where*

I have [tbl_AllStudentsEmail] where I get my students distribution list. I also have [tbl_CourseID] where some students are registered (not all). Students record are registered here along with the ...
0
votes
1answer
25 views

Access Web App query 3 Tables with aggregatios - Need a workaround

I'm designing an Access 2016 Web App for inventoring our computers. The problem I have is with a query where I want to get the CPUCore-amount and RAM-Amount for each PC. The computer-value in RAM ...
1
vote
2answers
40 views

Does the EXIST clause only return distinct rows by its nature?

I have two queries that appear to be logically equivalent, yet they return different result sets. I am using the AdventureWorks2012 database. The first query uses an EXISTS clause with a sub query. ...
2
votes
3answers
86 views

Join between different frequency tables

Assume that I have two tables, DailyTable and QuarterlyTable, where each have 3 columns: ID, Date, and Value. As the names indicate, the frequency of data stored in DailyTable is daily, while the ...
0
votes
3answers
98 views

How can I pivot a view into a join so I get multiple query rows as columns in a single row? [duplicate]

I have a view into which I am pulling properties of vehicles, such as tire size, engine capacity, color, number of doors, etc. This data is stored in multiple tables but I have simplified the data ...
1
vote
2answers
57 views

How could I get the value as Y or N in one table if value is exists on another that meets certain condition?

I have to generate simple report from three tables named customer, product, and order. My report should have a column that shows whether the customer ordered that product or not. How could I get the Y ...
4
votes
1answer
44 views

PostgreSQL join subquery can't restrict query

I'm working on a check-splitting project that splits out amount owed for an activity. Users can be a part of multiple 'groups', which each have their own running balances. I'm trying to run a query ...
0
votes
0answers
43 views

How to give a prefix to all columns of a JOINed table in MySQL?

I have a data schema like this: car id type -> ENUM('Tesla', 'AstonMartin', 'Bmw') common_attribute car_tesla car_id specific_attribute_a specific_attribute_b ...
2
votes
1answer
49 views

How to JOIN a table with a name that equals to a determined value in MySQL?

The model of the (PHP & MySQL based) Zend Framework 2 application I’m currently working on contains a inheritance similar to the one described here. To map this to the database I’m using the ...
0
votes
1answer
67 views

comparing left join and outer apply doing the same thing

I have an update trigger on a table that that insert rows in another database/table the query look like insert into otherdb.table select manyfield from inserted left join (select id, ...
0
votes
1answer
31 views

ORA-00932 on subquery join from subquery join

The only guess I have is that perhaps the size of the subquery is considered a CLOB? I have no null values for the fields selected AFAIK. SELECT DISTINCT kbq_ent.qid AS kbq_ent_qid, kbq_ent....
2
votes
3answers
112 views

Poor performance of JOIN, WHERE, ORDER BY combination (MySQL)

Goal: select 10 most recent rows from posts which are related to a specific user. There are two tables: posts (~5,000,000 rows) and relations (~8,000 rows). posts columns: --------------------------...
1
vote
1answer
25 views

Query: How to join on different tables depending on the filled column?

I'm pretty new to the field of SQL, but already solved a lot of problems, so until now i am familiar with the different possibilites of JOIN(s) (explicit and implicit syntax), group by, distinct and ...
1
vote
1answer
30 views

Using a join operation with conditionals

I have two tables that I'm working with in my database. I am using SQL Server 2014. One is 'versions', that contains version_id, version_number, start_date, end_date and server_id. The other is '...
0
votes
1answer
54 views

MySQL Query fix and speed up

I'm newish to MySQL and a lot of my query is pieced together from this site and trial and error, so any help is appreciated. If I left out anything, or more detail is needed, I will be happy to give ...
-1
votes
1answer
29 views

Multiple references to another table, but I don't know how many

I have two tables in a database, folowwing this model: scientists : id (int) | name (varchar) | surname (varchar) | publications (int) publications : id (int) | fulltext (varchar) I would like to ...
2
votes
0answers
36 views

How to avoid row locks on both tables joined in an update?

I have little knowledge about MySQL internals, but I'm experiencing that in a query like: UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.a = x, t1.b = y, [, ...] WHERE [ ... ] rows from both t1 and ...
4
votes
2answers
382 views

Outer self join filters vs subqueries

I have a table storing information about tickets. There is one record when ticket is sold and another when ticket is used. There is a column called TransType which is set to either 'sold' or 'used' ...
2
votes
1answer
51 views

Can I avoid a join for my select procedures?

Here is a really simple example of a select procedure I'd like to create, although it's only partly done (it's pretty self-explanatory): delimiter // CREATE PROCEDURE mysql.selectTables (_first_name ...
-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
27 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
67 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
37 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
589 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
28 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
43 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
51 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
89 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
36k 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
48 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
68 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
99 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
45 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
27 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
36 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
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
109 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 ...