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

learn more… | top users | synonyms

1
vote
1answer
251 views

Matching data in two tables

I'm teaching myself SQL and I ran into a question that may be related to joining tables. I'm just a bit unsure. Say, you have two tables, one listing the customer's creditcard_number, and one listing ...
1
vote
2answers
113 views

Select highest id of each type in mysql

I am creating a database that keeps track of backups on our various servers. The query that I am using actually seems to work for the most part. The only issue is that it isn't getting the highest ...
0
votes
2answers
278 views

Remove duplicates from multiple joins

I'm using WordPress $wpdb->get_results for this query (it is simplified for question purposes): SELECT customer.id, customer.name, address.address appt.appointment_date, pay.date FROM customers ...
0
votes
1answer
38 views

Merge join turns to hash match when adding a 2nd join

I would like to educate myself on what goes on under the hood in SQL Server, so I'm about to delve into Grant Fritchey's book Dissecting SQL Server Execution Plans. As it's 181 pages I just wanted to ...
0
votes
1answer
29 views

How can I get a single row on a inner join not to double my sum

I have two tables (DEP_DEZ & CAR_EXP): ID FK_ID_CHESTIONAR TOTAL_E TOTAL_F TOTAL_STOC 261 644 87 152 48 ID MASA_TOTALA FK_ID_CHESTIONAR 405 20 644 406 45 ...
1
vote
1answer
68 views

How can I Rewrite Update Query to Update Using First Record From Inner Join?

I wrote up a question about trying to do an update with an inner join last week that got a lot of answers that I am currently trying out. Here is the link to the answer I used to get to the point I'm ...
7
votes
2answers
117 views

Most efficient way to generate a diff

I have a table in SQL server that looks like this: Id |Version |Name |date |fieldA |fieldB ..|fieldZ 1 |1 |Foo |20120101|23 | ..|25334123 2 |2 |Foo ...
5
votes
1answer
114 views

Joining in query decomposition

I have a question about query decomposition and localization, as in here: When joining 3 tables (here EMP, ASG and PROJ) is there any specifiy order, when to join which table? Why ASG and EMP are ...
2
votes
1answer
922 views

How to join all combinations from two tables with a common column

I have a list of suppliers who supply a list of countries. For example: Supplier A supplies to: USA France China and another list of suppliers with their suppliers' factories For example: ...
3
votes
3answers
721 views

Problems updating with inner join with multiple on statements

I have an orders table on a database that contains geographical information about the city and state that the order is from. This includes latitude and longitude for doing haversine calculations. I ...
2
votes
1answer
54 views

postgresql join - too long when no results found

I have two tables: controller_monitor and controller_monitor_reading. controller_monitor Column | Type | Modifiers ...
3
votes
2answers
45 views

Combine the effects of separate delete and insert rows

How to I combine the two rows below: Date Id For_Id Type New_Value Old_Value ------------------------------------------------------------------ 2015-08-13 6 327 ...
1
vote
0answers
191 views

Update row value with a sub-select join

I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table. The query looks like this ...
4
votes
2answers
456 views

Count rows with inner joined tables

I have 3 tables: Players: mysql> SELECT * FROM players; ...
1
vote
1answer
55 views

Sum currencies excluding duplicatied records

I have a CRMOffer table containing customerID and other CRMOffer data. And a Sell table containing customerID and sellValue and sellPK. Sell table has a explicit PK, CRMOffer sadly doesn't have one ...
1
vote
1answer
53 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
1answer
55 views

json_array_elemnts() in FROM in postgresql 9.3 acting as where clause

I am trying to extract array out of json in the FROM statement But issue occurs when certain json doesn't have that array in it and then query just ignores that row and doesn't get counted in the ...
1
vote
3answers
242 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 ...
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
242 views

Running a complex query for every date in a range

I've got a table of orders Column | Type | Modifiers ...
3
votes
4answers
67 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 ...
4
votes
1answer
156 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
2answers
53 views

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

survey_categories +---------------+-------------------+ | survey_cat_id | survey_cat_status | +---------------+-------------------+ | 1 | 1 | | 2 | ...
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
2answers
102 views

Include logical key on each table vs. join

I'm designing a database that is used to track an inventory item through its lifecycle. I'm currently relating my tables using the previous lifecycle event's ID (for example, a shipment contains the ...
3
votes
1answer
217 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
20 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
71 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
259 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
154 views

type ALL in explain [closed]

I have tables like these: items +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
0answers
47 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. ...
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 ); ...
0
votes
2answers
235 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
votes
1answer
71 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) ...
2
votes
1answer
57 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
424 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 ...
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
36 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 ...
3
votes
1answer
165 views

How do I make this Postgres view that performs a join updatable?

I've got two tables, products and subscriptions: CREATE TABLE products ( id bigint NOT NULL, title character varying(75), description text, manufacturer_id bigint, created_at ...
9
votes
1answer
421 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') ...
8
votes
1answer
290 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 ...
0
votes
1answer
91 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 ...
0
votes
1answer
89 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 ...
3
votes
1answer
156 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, ...
0
votes
1answer
43 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", ...
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
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 ...
6
votes
2answers
424 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 ...