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

learn more… | top users | synonyms

1
vote
1answer
56 views

Optimize join - group by Oracle SQL filter

I have a few tables: CHEST: id fk_id_rap fk_id_ses_rap 1 1 1 2 2 1 3 3 1 SES_RAP: id an 1 2015 2 2016 RAP: id name 1 rap1 2 rap2 3 ...
1
vote
3answers
288 views

Query for lowest price for product in same row

I have two tables: first for products and second for prices. I need to calculate and display the minimum price from all 5 prices for one product. I already have query to select five prices and ...
5
votes
2answers
3k views

The order of INNER JOIN's

I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000. I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
2
votes
1answer
61 views

Using Join to get data from multiple tables in SQL Server [closed]

A(a_id, a1, a2) B(b_id, b1, b2) C(c-id, a_id, b_id) Now, I want to fetch all the data of table A, and from table B, I only need a record of b1. How can I use join query in SQL Server to fetch the ...
8
votes
2answers
280 views

Running a complex query for every date in a range

I've got a table of orders Column | Type | Modifiers ------------+-----------------------------+---------------------------------...
3
votes
4answers
70 views

Select using a different value than id on join [closed]

I have two tables, normalized: table1 id quality product_id 1 100 1 2 100 2 3 10 4 4 50 2 and table2 product_id product material 1 ...
1
vote
2answers
55 views

group result of multiple table to get unique result after count operation

survey_categories +---------------+-------------------+ | survey_cat_id | survey_cat_status | +---------------+-------------------+ | 1 | 1 | | 2 | ...
4
votes
1answer
184 views

Fast alternative for “NOT IN”

I have a table A that got a field called id that is the primary key of this table. I also have a table called B which also have a field called id as primary key. Now I want to get all rows from ...
1
vote
1answer
54 views

Is it necessary to create foreign key in this query? [closed]

I have the table called investors having as primary key the column id. I have the tables Users, Login_Logs and Accounts. All of these tables contains the foreign key investor_id. Between investor and ...
0
votes
2answers
53 views

How to JOIN each row ON NOT IN ( SELECT … )?

Say I have a table of users, songs, and songs played. users id: int songs id: int songs_played user_id: int song_id: int If I want to, for each user, find a song that has not yet been played. How ...
1
vote
1answer
751 views

LEFT JOIN DEFAULT NULL VALUE

My query can be simplified as this: SELECT a.id, a.name, b.* FROM a LEFT JOIN b ON a.id=b.id Which b is from group concat select (very dynamic column). Sometimes there are 3 columns in b, sometimes ...
3
votes
1answer
268 views

COUNT number of column values with INNER JOIN [closed]

I'm new to PostgreSQL and I am making a Report. This is my SQL Fiddle: http://sqlfiddle.com/#!15/6ce465/5 So basically I have two tables, responses and questions. The structure is responses ->...
2
votes
1answer
21 views

Is Oracle 10g smart enough to work on a WHERE clause identically as the equivalent INNER JOIN?

Way back when Michael Jackson was still alive, I made this question: http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause And now I'm performance-auditing a system in which the ...
0
votes
2answers
75 views

PostgreSQL, merge join results

I have 2 tables with common columns and would like to know if it's possible to request this 2 tables by merging the results. Table "job" : parent_id, target_id, start_date, end_date, jobposition_id ...
1
vote
1answer
303 views

sql join query through Adventure works database [closed]

I want to show a list of Product Colors. For each Color then show how many SalesDetails there are and the Total SalesAmount (UnitPrice * OrderQty). Only show Colors with a Total SalesAmount more than $...
3
votes
0answers
188 views

type ALL in explain [closed]

I have tables like these: items +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------...
0
votes
1answer
99 views

Oracle: Recursively self referential join with nth level record

I have self referential table like this: id |level | parent_id ---------------------- 1 |1 |null 2 |1 |null 3 |2 |1 4 |2 |1 5 |2 |2 6 |3 |5 7 |3 |3 ...
9
votes
1answer
511 views

Naming conflict between function parameter and result of JOIN with USING clause

Given this setup in current Postgres 9.4 (from this related question): CREATE TABLE foo (ts, foo) AS VALUES (1, 'A') -- int, text , (7, 'B'); CREATE TABLE bar (ts, bar) AS VALUES (3, 'C') ...
-1
votes
1answer
72 views

A complex query to Joining 2 tables and getting result [closed]

I have a database in MySQL. I have 2 tables, Structures as follows : t1 t2 I want to join both the tables and result should be like this : Employee_id | count(DNIS) | total_calls | count(DNIS) :...
1
vote
0answers
48 views

Efficient structure for querying membership in many-to-many relationship where tableA has ONLY the indicated members from tableB?

I'm working on an application with a PostgreSQL back end. There is a many-to-many relationship between items in one table and tags in another table. The specs require that the application be able to ...
1
vote
0answers
38 views

Dealing with joins in persistent key-value stores

One promising emerging category of NoSQL databases is key-value stores. These include Redis (disk-backed), Riak, Aerospike and others. The problem I struggle with the most is the lack of joins. ...
0
votes
2answers
258 views

Doing a left join and having every match include an extra null row

So I want to do a left join and have every row on the left table include a row of nulls on the right, even when there is a match. Is there a name for this type of join? And is there a better way to ...
1
vote
1answer
36 views

SQL Join Field and Content

I have this MySQL Structure: CREATE TABLE Fields ( ID INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY, ForUser VARCHAR(255) NOT NULL, ForCategory VARCHAR(100) NOT NULL, FieldName VARCHAR(100) NOT NULL ); ...
2
votes
1answer
58 views

Oracle's (+) notation within function

If we use (+) notation we can have outer join. For example: select * from tab_a, tab_b where tab_a.num = tab_b.num(+) Is outer join. If we use function around columns, for example: select * from ...
1
vote
3answers
439 views

How can I join two tables of an Oracle database containing all rows (not the intersection)

I have a Oracle database with a table t1 with: cod | text1 ----------- 1 | aaa 2 | bbb 3 | ccc table t2 with: cod | text2 ----------- 2 | abc 4 | mlc 5 | asc how can I optain ...
8
votes
1answer
334 views

Combine two event tables into a single timeline

Given two tables: CREATE TABLE foo (ts timestamp, foo text); CREATE TABLE bar (ts timestamp, bar text); I wish to write a query that returns values for ts, foo, and bar that represents a unified ...
1
vote
1answer
71 views

MYSQL View with LEFT JOIN return all possible combinations

I am working on a view that joins multiple table. The data will be entered on separate table based on RepairID. The view will gather the data that is linking to the RepairID. My problem is after I ...
1
vote
0answers
39 views

Type of relational/object-oriented database with improved joins for object graphs?

I would like to know if there is a specific name or term for the kind of database described below: I notice one of the biggest problems when working with relational databases and object trees/graphs ...
2
votes
3answers
578 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, ...
1
vote
1answer
10k views

Select records in one table but not in another, when the second table is not matching on primary key

I have two tables from which I need only the results that appear in one table (list) which do not appear in the second table (cardinal). The list table's primary key is sku, and the table has a ...
6
votes
2answers
452 views

MySQL: delete…where..in( ) vs delete..from..join, and locked tables on delete with subselect

Disclaimer: please excuse my lack of knowledge about database internals. Here it goes: We run an application (not written by us) which has a big performance problem in a periodic cleanup job in the ...
0
votes
1answer
100 views

Mysql: Create a view with multiple self joins without duplicates in result

Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you. So i have a table entities_attributes_values where an ...
3
votes
1answer
170 views

MySQL Join When Record in Joined Table Does Not Exist

How I can join two tables when record in joined table doesn't exist. I'm tried something like this: SELECT Confessions.ID, Confessions.WriteTitle, Confessions.WriteArea, Confessions....
1
vote
5answers
155 views

TSQL insert table using view — performance issue

I have a big view joining multiple tables that in a regular basis materialists itself into a table (this step occurs for viz's tool read a table instead of a view during data source refresh): select ...
0
votes
1answer
48 views

Lookup multiple ids in seperate table with 2 subquerys

I have a query from a timesheets tables that I am using to select a submitter, approver, and a date. SELECT PRTIMESHEET.POSTED_TIME, PRTIMESHEET.PRRESOURCEID as "SUBMITTER", PRTIMESHEET....
2
votes
1answer
197 views

Joining on a field with different values

I am trying to join data from two completely different sources. One source contains an employee's schedule information, and the other tracks what they actually worked (like what time they actually ...
0
votes
1answer
33 views

Problem using multiple JOINs

I have a list of customer IDs in table newsletter_subscriber. I have an EAV table that contains a lot of information about these customers, table customer_entity_varchar. For every entry in ...
0
votes
1answer
53 views

Postgres INNER JOIN same table failing on CartoDB

I have a table with data which looks like this +----+------+-------+ | cp | year | count | +----+------+-------+ | 1 | 2000 | 10000 | | 1 | 2001 | 9000 | | 1 | 2002 | 9500 | | 2 | 2000 | 8000 |...
0
votes
1answer
222 views

SELECT w/ INNER JOIN Query on Optional Columns

I have five tables: File2, Branches_of_Study, Schools, Education_Establishments, and Certificates. File2 is the transaction table with all the other tables as reference tables. File2 is all about the ...
1
vote
1answer
59 views

SQL Server Query For Getting data in Specific Format

We have two tables. One is PriceGroupTable and other is PricesTable. There is a one-to-many relationship between them. Please refer to the attached pic for more details. I am looking for a SQL ...
1
vote
0answers
50 views

Delete using join or using sub-query?

I need to delete rows from a table based on what is present in a temporary table. For me, both of these statements work : DELETE from main_table where `id` in (select `deletable_id` from ...
1
vote
1answer
67 views

Combining data from 3 tables depending values stored in 1 table

I'm trying to make a SQL query to return values potentially from 3 tables. This will combine all booking information for a booking which can span over multiple days with multiple guests and a single ...
2
votes
2answers
79 views

Aggregating results of two database views in one database view

I have a view with name of UserInfo like this: And another view with name of UserRoleInfo like this: I want to aggregate all the records of the two views for one user name like H.F into ...
2
votes
1answer
173 views

Filtering by minimum value per group problem

I have a select statement, which when simplified produces the following: +------------+----------+-----+ | Group | Name | ID | | vegetables | potatoes | 1 | | vegetables | carrots | 6 |...
0
votes
1answer
125 views

Cross Join and Left Join within a query

I have query which has a cross join and also a left join which produces an error Unknown column 'a.hour' in 'on clause' simplified version of query is like this SELECT b.*,a.hour,c.datetime FROM ...
8
votes
3answers
11k views

Is it better to separate a big query into multiple smaller queries?

There are situations which require having really big query joining several tables together with sub select statements in them to produce the desired results. My question is, should we consider ...
1
vote
0answers
448 views

Oracle 10.2.0.4 query hundreds times slower than the same query on Oracle 10.2.0.3

I have an issue with query on Oracle 11.2.0.4.0 (it takes forever to complete and CPU is at 100% all the time) so I was trying to change optimizer_features_enable to previous versions. It worked with ...
1
vote
0answers
185 views

Multi-Table Join to Single Destination [closed]

I am having a serious performance issue with join together four tables and placing the results (in a certain permutation) into one destination table. I am using MSSQL 2008 and good ole T-SQL. To ...
0
votes
0answers
54 views

Update a field in table when a combination of three fields with a second table is matched

I have a database form an old archaeological project which I imported into a mysql DB. I have a table "graves" with 400+ entries: "graves" id necr burials single multi 1 1 1 ...
0
votes
2answers
110 views

Select MAX DATE from joined Table

I have two tables, PROJECT and BILLINGS, as follows: Project_ID Project_Client ---------- ------------------ P1 ABC Company P2 DEF Company P3 GHI Company Proj_ID ...