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

learn more… | top users | synonyms

1
vote
1answer
26 views

Optimised query to select document and possible annotations

I have a database of small documents which users can view and annotate. Their annotations are not public. I'm looking to optimise a single query to read the document and annotations for the current ...
4
votes
1answer
6k views

Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening. Following up on my previous ...
0
votes
1answer
41 views

How to create new column with value from other table

I have a table called 'menus': menu_id | sort | page_id | parent_page_id ---------|------|---------|---------------- 1 | 0 | 1 | NULL 1 | 1 | 2 | 1 1 | 2 | ...
1
vote
2answers
1k views

Does using LIMIT improve the performance and is it noticeable?

I want to understand the following. Assume that I have a complicated query with let's say a join of 5 tables a group by summations and order by. Letting aside any optimizations to the query itself ...
1
vote
1answer
192 views

Slow join behaviour with 'or' in predicate

I'm faced with a situation which I can't understand and overcome. In short we have left-join query like: select from a left join b on a.key1=b.key1 or a.key1=b.key2 This works VERY slowly, while ...
1
vote
1answer
114 views

How to prevent Cartesian joins when value can be in either field?

I have a table of articles where the same article may be presented to different audiences with slightly altered text depending on the user's access level. When this occurs, the article will have the ...
0
votes
0answers
530 views

MySql INNER JOIN doesn't use composite index

I have two tables, table A which contains date and name of table B entry and table B which contains its name and type. Table a has a composite index on table_b_name and date. I'm selecting all table A ...
0
votes
1answer
119 views

Need to create combinations of Parents/Children/Dogs, but can't get past 2nd level of CTE

I've hit a brick wall and please ask help. I'm building a table up for (in this example) a ticketing system where e.g. Ticket 1 will allow Parent: Either parent Peter or Sally, Child: to come without ...
1
vote
3answers
2k views

Conditional Join - Specific Column Select

I have tables like these: Table Employee ( Emp_Id, Full_Name, ...) Table Order ( Submitter_Id, Recipient_Id, ...) Then say that I have conditional join like this: select isnull(Emp.Full_Name, ...
-1
votes
1answer
66 views

COUNT WITH JOIN [closed]

I've the following tables: fss_post(post_id,text) fss_comment(idComment, post_id, text) Is possible to write a query that give me as result a row for each post_id with total comment count for ...
0
votes
1answer
157 views

Get unmatched rows for foreign key

Consider two tables: CREATE TABLE Users( id INT PRIMARY KEY, name VARCHAR(128) ) Engine=InnoDB CREATE TABLE Tags( id INT PRIMARY KEY, user REFERENCES Users(id), tag VARCHAR(128) ) ...
0
votes
1answer
86 views

Performance: order of tables in joined statement

I have the following SQL statement, running on a SQLite database on a Windows mobile device. SELECT table1.uniqueidentifier1, table1.int1, table1.varchar1, table1.decimal1, table1.decimal2 ...
0
votes
1answer
48 views

Append columns with values from has many reletionship

I have two tables orders and items. Every Order has many Items. orders - id - created_at - paid_at items - id - title - amount - quantity - order_id Let say that there are 3 kinds of items. Chair, ...
0
votes
1answer
755 views

Query with PIVOT fails with second LEFT OUTER JOIN to same table

I have a big query that contains a PIVOT and several LEFT OUTER JOINS. I need to add a second LEFT OUTER JOIN to a table that is already joined in the query. When I do this the query errors and I'm ...
1
vote
1answer
140 views

Joining on a field with different values

I am trying to join data from two completely different sources. One source contains an employee's schedule information, and the other tracks what they actually worked (like what time they actually ...
5
votes
1answer
4k views

Query to compare two subsets of data from the same table?

My problem: I'm trying to compare to subsets of data within one table, and I've got two methods that partially work and one certainty that there's got to be a more correct way to do it. The idea ...
2
votes
2answers
3k views

MySQL not using indexes when joining against another table

I have two tables, the first table contains all articles / blog posts within a CMS. Some of these articles may also appear in a magazine, in which case they have a foreign key relationship with ...
0
votes
1answer
293 views

MySQL : LEFT JOIN not fetching required data

I'm using left join in MySQL as mentioned below: SELECT Q.QUESTION_ID, school_id FROM Q_MASTER AS Q LEFT JOIN ( SELECT keyword, school_id FROM ...
0
votes
1answer
252 views

Efficient way to write query for multiple table which depends on column value of a table in mysql?

In the given schema, there is a table A having column A1,A2,A3,A4 There are three more tables i.e. B,C,D If the value in A4 is b, i have to lookup table B. If the value in A4 is c, i have to lookup ...
0
votes
2answers
1k 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
201 views

Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

I have a table of around 350m rows on a linked server in which I added an additional INT column to serve as a count(external_identification) of records as the result of a join on PACKAGE and DOC2. ...
2
votes
1answer
2k views

Concatenate one-to-many field in single result?

Say I have the following query: SELECT * FROM AppDetails, AppTags WHERE AppDetails.AppID = '1' AND AppDetails.AppID = AppTags.AppID Which gives the following results: AppID AppName ...
0
votes
0answers
274 views

How to increase MySQL performance with joins

I have a MySQL database with InnoDB engine. Particularly one of my queries became very slow. The query is shown below: SELECT t1.`ARTREF1` FROM `lijnen` AS t3 INNER JOIN (`kortingen` AS t2, ...
1
vote
1answer
59 views

What ways are there to limit the processor / ram requirements of a specific query?

Every once in a while, I need to run some very expensive query on a production server. Think spaghetti joins. This is likely to affect the server operation, and I don't want that. What ways are there ...
0
votes
1answer
1k views

Getting multiplication of column values on a joined table based on condition

The title was best I could find to explain my question, I don't think it helps though. Anyways, I have two tables: CREATE TABLE YEARLY_RATES ( RATE_YEAR NUMBER(4,0), RATE NUMBER(6,4) ); ...
1
vote
1answer
131 views

Query_cache doesn't work with join

I have a simple join query, but for some reason query_cache won't cache it! SELECT id, news, approve, FIXED, DATE, allow_main FROM post LEFT JOIN post_plus ON post.id = post_plus.news_id WHERE ...
0
votes
3answers
871 views

inner joins with where clause for no values?

I have 2 tables : Table1 Parent(varchar) Child (varchar) Table2 C1(varchar) PC (varchar) Sample data: Requirement - I need Table2.C1 values for which column Table2.PC = Table1.Child , ...
1
vote
2answers
303 views

Left join not working like expected

I need to do a table join to find missing translations in a translation system. Let's say we have the following simplified table (id is the identifier of the string, the actual translation is not ...
1
vote
1answer
158 views

Combining multiple left join for one single line

We have a query as below. SELECT tblSData.header, tblEAlert.eMessage FROM tblSData LEFT JOIN tblEAlert ON tblSData.sDataID=tblEAlert.sDataID Where tblSData.aID=".$aID." Order By tblSData.dateTimer ...
1
vote
0answers
94 views

How to index for a group by with columns from different tables?

Is it possible to create an index from columns of 2 different tables? If not, how do we optimize a query that does a group by on columns of 2 different tables? For example: the following ...
1
vote
4answers
636 views

Pick one single row each time from either table?

I will run a query and get a list of aID and based on that I will try to match below tables.For each aID I just want one record either from tblGAlert or tblEAlert based on gDateTime and eDateTime ...
0
votes
1answer
577 views

How can I join two tables while filtering the results with a“ WHERE IN” clause? [closed]

What I am trying to do is set up a selection filter for a cascading list on my site. When I say selection filter, I mean I am trying to limit, and sort the results based on what the user selects. I ...
-2
votes
1answer
99 views

MySQL MATCH vs JOIN

I have a lot of data to filter and therefore I wonder is it better to create somekind of indexes and save them to one table and use MATCH or LIKE to find matches or should I use many different tables ...
1
vote
2answers
341 views

Distinct rows from a join

I have two MySQL tables: ╔═══════════════════╗ ║ wpss_question_set ║ ╠═══════════════════╣ ║ id ║ ║ quesset ║ ╚═══════════════════╝ ╔════════════════╗ ║ wpss_questions ║ ...
2
votes
1answer
113 views

Minimizing Page Fetches

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
1
vote
2answers
778 views

Updating a table efficiently using JOIN

I have a table that has the details of households and another that has the details of all the persons associated with the households. For the household table I have a primary key defined using two ...
1
vote
4answers
6k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
1
vote
1answer
49 views

Getting related data from an uncertain column

I am working on a friendship logic, the table friendship has this structure: `friendship_id` INT NOT NULL , `user1_id` INT NOT NULL , `user2_id` INT NOT NULL , `status` INT NULL , `created` ...
4
votes
4answers
549 views

Table join to match without duplicates

I have a situation where I need to match arrivals and departures of assets in a location. The problem is that asset movements are not always entered into the database chronologically, nor is there ...
-3
votes
1answer
129 views

SQL Server: Month Range Join [closed]

Given that there are two dates like 2012-07-08 and 2013-02-06. I need to count record of each user for every month. If there is no data for a user in particular month then it should return zero. The ...
0
votes
2answers
70 views

Grouping results based on link table

I have three tables: users +----+-------+ | id | name | +----+-------+ | 1 | dave | | 2 | cher | | 3 | meg | | 4 | sarah | +----+-------+ groups +----+-------+ | id | name | ...
4
votes
1answer
549 views

Forcing Join Order

I have a table with just over a million rows. These rows can have a parent record in the same table which is found by joining to itself on 6 different columns (i.e. there is no single ParentID ...
4
votes
2answers
452 views

Multiple JOIN of 6 tables via 5 database instances

I'm currently reengineering my corporates user management and created a table which lists all users of all the 5 database instances. Next step is, that I need to write a query which shows me all the ...
-1
votes
2answers
3k views

How to use GROUP BY after the Having clause Mysql

Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions All the accounts which is not ...
0
votes
1answer
321 views

Simplify subqueries with join

I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data. ...
0
votes
1answer
2k views

How to create the multiple sum query with joins

Please consider following database: Table `invoices`: -- invoice_id Table `amounts`: -- amount_id invoice_id type_id amount I am trying to get the sum for each type from table like this: SELECT ...
1
vote
1answer
45 views

Joining data from legacy database

I'm trying to figure out how to write this query. Table 1 - Booking BOOKING_ID | BOOKING_DATE | etc... 1 | 2013/01/01 | 2 | 2013/01/01 | Table 2 - BOOKING_PROCEDURE_TYPE ...
1
vote
2answers
193 views

Prune unused joins

I have expected that selecting from a view would not join the tables from which I don't query any values, but it doesn't seem to be the case. I have a bunch of tables with the following structure: ...
0
votes
3answers
67 views

find sold subscriptions from table 1 and store name in table 2, joined by store id. (oracle)

I am trying to get the number of sold subscriptions from one table and the store name from another. The tables have a common column containing a store id, however the columns do not have the same ...
1
vote
3answers
347 views

Will performance be better if I specify a filter multiple times?

Do I gain more performance if I repeat the filter in the WHERE clause on both sides of an OUTER JOIN? To clarify using code: select * from #main_select left outer join ( select main_id ...