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

learn more… | top users | synonyms

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 ...
0
votes
1answer
34 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
60 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, ...
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
64 views

left join on closest date [closed]

I have 2 tables orders +----+-------+--------+------------+ | id | email | status | date | +----+-------+--------+------------+ | 1 | | 0 | 1427953986 | | 2 | | 0 | ...
0
votes
3answers
173 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
50 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
32 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
133 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?
0
votes
1answer
154 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
1answer
505 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 ...
21
votes
4answers
21k views

Are individual queries faster than joins?

Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient? ...
5
votes
3answers
721 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 ...
1
vote
1answer
96 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
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 ...
2
votes
1answer
1k views

MySQL update one table from another joined many-to-many

I have two tables, events and plays joined many-to-many by a third table plays_in_events. Where the events.Name field is blank, I want to set it equal to the name of the play that was performed in ...
1
vote
1answer
309 views

Creating Database Tables for Reviews functionality that incorporates Tags/Categories

I'm wanting to create some simple website functionality through a little database idea I have in order to give myself the ability to create/retrieve customer reviews based upon tags/categories. More ...
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
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), ...
1
vote
1answer
56 views

Can I optimize a left join for a single match? [duplicate]

Consider a join A LEFT JOIN B ON A.id = B.a_id: B.a_id is nullable -- most but not all B records point to an A record. A can, at most, only have one matching B record (business logic). Not all A ...
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 ...
1
vote
1answer
247 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
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 ...
0
votes
1answer
340 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 ...
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 ...
10
votes
5answers
54k views

Matching single column against multiple values without self-joining table in MySQL

We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data: user_id ...
2
votes
1answer
289 views

Selecting most recent date when dates are stored in another table

Working in a MySQL database, I have tables set up like this: Table song_instances has song_id (int), meeting_instance_id (int) The meeting_instances table has columns meeting_instance_id (int), ...
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 ...
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 ...
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 ...
3
votes
3answers
671 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 ...
5
votes
1answer
112 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
892 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
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 ...
2
votes
1answer
103 views

What column is this alias referring to?

I've read the following code a few times trying to figure out what it is doing. "A right outer join is that which returns the unmatched rows from the right tables with matched rows from the left ...
1
vote
0answers
189 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
433 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

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
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
3answers
235 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
240 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 ...
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 | ...