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

learn more… | top users | synonyms

3
votes
1answer
119 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 ...
0
votes
0answers
20 views

Joining one table with multiple tables for aggregate functions possible?

I am trying to get this seemingly simple looking query to work : SELECT c.id, c.name, count(chc.id) as contacts_count, count(cha.id) as addresses_count FROM (customers c LEFT JOIN ...
1
vote
2answers
97 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
1answer
36 views

How do I get only those values that has related elements from other table?

I have three tables for a social network: companies, users and countries. Users belong to Companies and Companies have a Country. All tables have their own id column. Table users has a company_id FK ...
3
votes
2answers
3k views

Is there any way to force MySQL use Hash Join instead of Nested Loop Join?

According to the document MySQL Explain Output format, MySQL resolves all joins using a nested-loop join method. Is there any way to force MySQL to use a hash join?
1
vote
0answers
55 views

Whats the difference between the following queries? Getting the top 3 salaries for different department

I am practicing SQL and came across this issue. I am trying to find the top 3 salaries for each department. The table structure is basic with one employee table with columns ...
1
vote
1answer
72 views

Receiving duplicate results in one condition of JOINED query

I always end up here when Googling for solutions, so I thought I'd cut out the middleman since I'm unable to fix this via searching/forums. It's my first time posting, so please forgive any faux pas ...
0
votes
2answers
48 views

sql problem with multiple linked tables

I have an Oracle db and the following tables: raportor: id id_pct_lucru 58382 327342 ses_rap id an status 1 2012 I 21 2013 I 41 2014 D ch_trat id fk_id_raportor ...
3
votes
1answer
20 views

Is it possible to JOIN to a Primary database from a Replication instance?

I have a database on a Replication server that doesn't exist on the Primary server. There are stored procedures on the Replication server's DB that join to tables in other databases on the ...
5
votes
2answers
83 views

Help finding join without predicate

Much like a related question by swasheck, I have a query that historically has suffered from performance problems. I was looking through the query plan on SSMS and noticed a Nested Loops (Inner Join) ...
0
votes
1answer
27 views

what better way to multiple ON conditions

I've got a table called Customers. And a table Users. (MySQL 5.3) Customers: customerID|customerName|accountManagerID|attendantID 123 |Google |456 |789 124 |Apple ...
0
votes
2answers
3k views

how to join table from different database with different Users of database?

I have Two database called. Database1: host: 127.0.0.1 user: root1 pass: root1 dbname: db1 Table 1: Category ID INT(11) Primary Key Name VARCHAR(255) Database2: host: 127.0.0.2 ...
0
votes
1answer
87 views

Improving SQL Query joins performance

We have been using SQL Server 2014 for our project as a backend source. We have been performing various joins on the tables. We came across a question in one to many and many to one relationship in ...
2
votes
2answers
1k views

Is it correct, order of where clause doesn't matter when it is used with join?

I have question on execution of queries in which join is used with where clauses. I read this question on join with where conditions. I tried options like disabling rules and checked actual query ...
0
votes
1answer
52 views

Count for many-to-many relationship

I have the following tables: surveys properties owners_properties owners ---+------------ ---------- ------------+--------- ------ id | property_id id ...
0
votes
0answers
6 views

Reason of poor performance of ANSI joins in Oracle 9i

Please before flagging as duplicate, read the last paragraphs. In an Oracle 9i database this query runs in 0.18 seconds: select count(*) from dba_synonyms s,dba_objects t where ...
0
votes
1answer
54 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
22 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
28 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
33 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 | ...
0
votes
1answer
54 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
31 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
54 views

left join on closest date [closed]

I have 2 tables orders +----+-------+--------+------------+ | id | email | status | date | +----+-------+--------+------------+ | 1 | | 0 | 1427953986 | | 2 | | 0 | ...
1
vote
3answers
125 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
42 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
30 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
76 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
68 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
127 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
243 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 ...
16
votes
4answers
17k 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
657 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
81 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
837 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
232 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 ...
-1
votes
3answers
82 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
45 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
50 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 ...
2
votes
1answer
406 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 ...
1
vote
1answer
95 views

placing conditions in a join query join on vs where

Is there and advantage to one of the following options over the other or are they the same ? select * from T1 Join T2 on T2.id=T1.t2_id and T2.field=value; vs. select * from T1 Join T2 ...
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
125 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
665 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
222 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
113 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
52k 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
199 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
31 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
25 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 ...