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

learn more… | top users | synonyms

0
votes
2answers
39 views

How do I write this cross Join in MySQL?

It should create a set of columns (ControlRange, SensorBias, SensorError, VDCurve, INSHalfCurve, INSSensCurve, ExogDEXCurve, InitGlucose) and all permutations of their values. I want the order given ...
0
votes
1answer
73 views

Sum of values for each row

SELECT ratings.programme_id, programme.programme_name, ratings.ratings FROM programme INNER JOIN ratings ON ratings.programme_id = programme.programme_id I have two tables a and b a has id and name ...
2
votes
3answers
224 views

Performance impact of NOT using a foreign key (many dedicated 1:many key-key tables vs non-fk generic key-key table)

We're using a lot of association tables to manage 1:many relationships among a variety of different objects in our system. To illustrate the question, two examples would be: users, events, ...
4
votes
2answers
68 views

Join subquery by year and month

I want to collect data from differed tables grouped by year and month. I'm using subquery for each sub data. Using mysql. SELECT YEAR(td.db_date) AS stat_year, job.total_jobs FROM ...
5
votes
0answers
273 views

Performance inner join vs left join involving parallelism

I have a query which takes about 8 hours to finish. Simplified, the query looks something like this: SELECT [Some attributes, inconsequential] FROM RBM.Financial_Events RFE INNER JOIN ...
1
vote
1answer
44 views

Syntax error in UNION ALL [closed]

I have syntax error in UNION ALL line 8.. what is the mistake? I'm using mariadb, (Innodb) SELECT 'Süre Bitiş Tarihi','Tc Kimlik No', 'Kullanıcı Soyadı, Adı','E-posta', 'Telefon','Diğer ...
2
votes
0answers
131 views

Materialized View - vs. New Calculated table for performance [closed]

I have a problem - I'm trying to de-normalize data tables in a data warehouse in preparation for use in the Business Intelligence/ Presentation layer. By denormalize I mean create a series of joins. ...
0
votes
2answers
166 views

Efficient way to join two tables with one to many relationship

This is interview question asked to me in interview and i was not able to answer. Please help.. What is the most efficient way to join two table with one to many relationship between them
0
votes
1answer
35 views

Update rows in a table with an index column starting from 0 with consecutive numbers

I have a few tables in a Oracle db with some "rule": The INDEX_COLUMN must start from 0 and must be consecutive numbers and the column NRCRT must start from 1 and also consecutive numbers. I've made a ...
1
vote
1answer
55 views

Joining 3 Tables

I have 3 tables as per below: **ATTRIBUTE TABLE** PeopleAttrCODE PersonCODE AttributeCODE Value 1 1 End Date 20151231 2 1 Start Date ...
3
votes
1answer
72 views

How to match column names and values from joining and related tables?

I have the following DB structure: cars: id make features: id name cars_feature: id car_id feature_id value I want to be able to select all cars with all features and return results with ...
0
votes
0answers
24 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 ...
0
votes
2answers
73 views

Improving performance on a join query

I have the following MySQL query that I am attempting to run against a set of tables prepopulated with data: $sql = sprintf(' SELECT video.id, video.name, video.created_at, video.duration, ...
0
votes
1answer
78 views

Improve execution time of query in POSTGRES with multiple joins

I'm having the trouble with the following query due to the growing size of the database ( websites is around a million rows, misspelled goes up to a couple millions ) It can reach up to 165 seconds ...
1
vote
0answers
63 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 ...
0
votes
1answer
38 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
1answer
22 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 ...
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 ...
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 ...
5
votes
2answers
87 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
110 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 ...
0
votes
1answer
93 views

Count for many-to-many relationship

I have the following tables: surveys properties owners_properties owners ---+------------ ---------- ------------+--------- ------ id | property_id id ...
0
votes
0answers
10 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
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
59 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
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
61 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
401 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
65 views

left join on closest date [closed]

I have 2 tables orders +----+-------+--------+------------+ | id | email | status | date | +----+-------+--------+------------+ | 1 | | 0 | 1427953986 | | 2 | | 0 | ...
0
votes
3answers
176 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
52 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
135 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
518 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
723 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
155 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
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
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
344 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
248 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 ...