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

learn more… | top users | synonyms

0
votes
1answer
30 views

selecting same column twice for different value

I have a table like this; event_id pair versus score 1 1 11 100 1 1 12 0 ... 1 11 1 0 1 12 1 50 Now I want to ...
1
vote
2answers
45 views

Join with comma separated values in SQL Server

I have a table [CourseMaster] LIKE CourseId CourseName ----------------------- 01 ABC 02 DEF 03 GHI 04 JKL 05 MNO 06 PQR 07 ...
1
vote
1answer
67 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
60 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
52 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
19 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 ...
-2
votes
2answers
52 views

Syntax error on JOIN [closed]

I keep getting syntax error on my join and I can't figure it out: Syntax error in JOIN operation SELECT [tblJobInfo].[Job Name], [tblJobInfo].[ShipDate], [tblJobInfo].[ShipVia], ...
0
votes
1answer
15 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
36 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
95 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
56 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
99 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
46 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 ...
1
vote
1answer
36 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
41 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
28 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
98 views

Count rows with inner joined tables

I have 3 tables: Players: mysql> SELECT * FROM players; ...
1
vote
1answer
30 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
37 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 ...
0
votes
1answer
21 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
50 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
50 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
162 views

Running a complex query for every date in a range

I've got a table of orders Column | Type | Modifiers ...
3
votes
4answers
54 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
49 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
42 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
37 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
47 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
37 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
46 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
13 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
33 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
54 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
44 views

type ALL in explain [closed]

I have tables like these: items +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
0answers
33 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
22 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
33 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
54 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
64 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
47 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
165 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
65 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
18 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
0answers
42 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 ...
8
votes
1answer
89 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') ...
7
votes
1answer
88 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
22 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
33 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
91 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
31 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", ...