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

learn more… | top users | synonyms

0
votes
1answer
59 views

SQL Server - Update using Join in Merge

Scenario : I need to update values in a table by joining it with another temp table. But I am using a Merge statement to check various scenarios and update the values in the table when the condition ...
0
votes
1answer
23 views

SQL query with multiple many-to-one joins [closed]

I want to select a count of all surveys where the survey.property.address.city == "Garrison". I have the following tables (stripped for your convenience): surveys properties ...
4
votes
1answer
58 views

Adding a second Group By column slows query down

I have the following query: SELECT 'TEXT', 'TYPE', 'DESTINATION', 'HEADLINE', 'COUNTRY_ID', 'COUNTRY_CODE' UNION ALL SELECT Data_1.TEXT, Data_1.TYPE, Data_2.Destination, Data_2.HEADLINE, ...
0
votes
1answer
33 views

Join MySQL Lookup tables

I have the following MySQL tables: games genres game_genres Games can have many genres (action, fighting, horror), so they're in a lookup table with game_id and genre_id. When I pull in my list of ...
1
vote
1answer
35 views

I have two tables and need to join them

First table: id | username | firstname | surname | address ---+----------+-----------+-----------+--------- 1 | johnie | john | something | blabla 2 | alice | something | something | ...
1
vote
1answer
59 views

Optimization of complicated sums and counts in related tables

I have a categories table, which have two columns, id and name. I have a types table, which have three columns, id, category_id and points. I have a products table, which have three columns, id, ...
17
votes
3answers
398 views

Access (Jet) SQL: DateTime stamps in TableB flanking each DateTime stamp in TableA

First Words You can safely ignore the sections below (and including) JOINs: Starting Off if you just want to take a crack of the code. The background and results just serve as context. Please look at ...
1
vote
1answer
34 views

How do I compare all tables with nonzero rowcounts (in an archive db) with tables of the same name in the line of business db?

(Full Disclosure: I am a software tester who is out of my depth. Because I am unable to get a definitive answer from the developer due to the necessary independence of the test team, I have to figure ...
-1
votes
1answer
62 views

left join on closest date [closed]

I have 2 tables orders +----+-------+--------+------------+ | id | email | status | date | +----+-------+--------+------------+ | 1 | | 0 | 1427953986 | | 2 | | 0 | ...
0
votes
3answers
155 views

Need SQL outer join help with 3 tables

See below example. ID_1 and ID_A are primary keys for table_1 and table_3 respectively. Table_2, the primary key is ID_1 and ID_A. ID_A is joined to one and only one ID_1 via table_2 due to business ...
1
vote
1answer
46 views

Left join not working with sub-query

I've got table of scores where each entry corresponds to a particular (student, subject) pair. CREATE TABLE score ( id serial PRIMARY KEY, value integer NOT NULL, subject_id integer NOT NULL, ...
0
votes
2answers
31 views

Is there a more satisfactory query to get the rows of 'A' ordered by the number of rows of 'B' associated with that row of A in SQL

Suppose I have two tables A and B. Table A has a primary key, A.ID for instance, and each row in B contains a reference to some A.ID (there is a foreign key column in B). As a specific example: A: ...
1
vote
2answers
122 views

Multiple COUNT fields, GROUP BY

I'm trying to group two columns in my MySQL database and show counts for them respectively (as separate columns). Is there a way of doing this or do I have to join two sub-queries? I'm aware of WITH ...
3
votes
1answer
70 views

Joining from database A to view in database B; where is processing done?

If I join tables from database A to a view in database B (same server), are the joins within the view in database B processed on database B before joining to the tables in the current database?
1
vote
1answer
434 views

How to select multiple id from same table

Here is the simplified version of my problem. I have these tables: Master table id - name - surname - fathers name - mothers name Names id - name Surnames id - surname I want to query master ...
5
votes
3answers
709 views

Can I skip the intermediate table in a join?

I've written this query : SELECT "BD - Utilizadores".Utilizador, "BD - Utilizadores"."Palavra passe", "BD - Áreas".área FROM "BD - Utilizadores" INNER JOIN "BD - Permissões" on ...
0
votes
1answer
149 views

Convert UNION query to JOIN. Is it actually needed for high performance?

I have the following table structure in MySql. To get the list of roles for a particular user, I use the following union Query, SELECT r.ID, r.ROLE_NAME, r.TENANT_DOMAIN, r.PROVIDER_ID FROM ...
1
vote
0answers
54 views

How do I correctly group output from multiple tables?

I have more than 2 tables where entries are associated with dates. Each table can have multiple entries per date. I want to produce output that lists a date, then lists the entries of each table for ...
0
votes
3answers
85 views

Joins in Stored Procedure

Below is my code, which returns billable and non billable total hours for their projects. I want to show in single row, but i am getting two result sets like below. DECLARE @UserName NVARCHAR(50); ...
1
vote
1answer
93 views

Same SQL stall on server but work on local ( on slower machine )

I have this strange issue that because of my limited knowledge about Mysql I cannot understand, I will really appreciate if somebody can enlighten me and point me to how to debug the issue. ...
1
vote
0answers
48 views

3 tables joins performance issue

DB: Postgres 9.4 schema: table a: id, score table ab: a_id, b_id, b_type table b: id, name, type I am trying to get top 10 result in the following format: name, count(group by name), ...
3
votes
1answer
1k views

Postgres Count with different condition on the same query

EDIT Postgres 9.3 I'm working on a report which has this following schema: http://sqlfiddle.com/#!15/fd104/2 The current query is working fine which looks like this: Basically it is a 3 table ...
2
votes
0answers
17 views

Join integration query involving multiple associations

I'd like some assistance in fixing this query. Let me explain the background on what it currently does, and then what I need to integrate. First, here are my current tables. users (performers) table ...
0
votes
1answer
325 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
1k 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
214 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
109 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
240 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
28 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
67 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
108 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
840 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
552 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
172 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
363 views

Count rows with inner joined tables

I have 3 tables: Players: mysql> SELECT * FROM players; ...
1
vote
1answer
53 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
52 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
50 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
211 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
233 views

Running a complex query for every date in a range

I've got a table of orders Column | Type | Modifiers ...
3
votes
4answers
66 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
145 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 ...