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

learn more… | top users | synonyms

2
votes
1answer
162 views

MySQL: Very slow query, not using index, no joins involved

I am running what I think is a very simple query: SELECT SQL_NO_CACHE m.mbr_nbr FROM tran.member m WHERE m.cardnbr = 000099999930 The table member is a poorly written one, with 133 columns and ...
0
votes
1answer
464 views

Slow performance of MySQL Join Query

I have inherited a site (running Coldfusion8 and MySQL 5.0.88) with a product search using multiple criteria. I'm struggling with optimizing the search, especially a pricelist check, which makes the ...
2
votes
1answer
449 views

Help with a complicated MySQL Query inserting data using select from 2 tables

I am stuck with a case and unable to get the desired result from my query.. I have to insert into a table some records based on multiple records from 2 different tables.. Tables: Target: ...
1
vote
1answer
560 views

Unable to add records to query in Access 2007

I've found this excellent resource that covers why a query might be read-only. I have a query that is not read only, yet I'm unable to add new records to it. There are three tables. Let's call them ...
1
vote
2answers
692 views

Table orders regarding Nested loop join in DB2

First I would like to cite the existing post Table order in Join Query so the author is saying that the table order really doesn't make things(result, access plan)different. I have doubt on this ...
1
vote
1answer
946 views

Really slow DISTINCT ON query with multiple joins

Originally posted: http://stackoverflow.com/questions/11173717/expensive-query-on-select-distinct-with-multiple-inner-join-in-postgres The songs table has only about 4k rows, posts and stations have ...
1
vote
3answers
63 views

How can I query to do this?

I have 2 tables, in feeds I have my feeds and (feeds.ID) is the primary key. second table is items. and feeds.ID is realted to items.feed_ID, so with a simple inner join i can extract the data of each ...
3
votes
2answers
4k views

forcing Oracle to use hash join for a subquery

I have a query that looks like SELECT * FROM table0 WHERE id IN (SELECT id FROM table1 JOIN table2) Oracle is choosing to join table0 with the result of (table1 x table2) using nested loops and ...
1
vote
1answer
421 views

Merge multiple table output but indicate which tables the records came from

I have an application with a function that uses a stored procedure (SQL Server 2008 R2) to pull in and arrange data from one table. Now, I’ve been asked to make it pull from two tables, but it’s not ...
3
votes
1answer
657 views

MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

We are using aws/rds, MySQL 5.5.8. all InnoDB. For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; ...
5
votes
2answers
373 views

How to use merge hints to isolate complex queries in SQL Server

I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal. However when I know the number ...
1
vote
1answer
1k views

Combine column from multiple rows into one row in SQLITE

I have a sqlite-database with the schema Articles Id Name Tags Id Name ArticlesTags Id ArticleId TagId I´m trying to query for Articles.Name and a LIST of tags. Basically what I would want ...
0
votes
1answer
39 views

Linking table or column key

Simple structure: have a table PAGES - which stores information about specific landing pages working on developing a GROUPing concept for the pages, where we are saying that a page may or may not ...
2
votes
1answer
176 views

Rewriting a query and eliminating subqueries

I have the following query : SELECT ws_id, n50, num_singletons, num_segments_less_100, kmer, num_all_reads, number_bases_in_contigs, num_bases_in_singletons FROM stats_assembly WHERE ws_id IN ( ...
3
votes
1answer
556 views

How to order query results by tag weight in IN('tag1', 'tag2', 'tag3')

Consider a simple query as SELECT * FROM posts JOIN tags USING(post_id) JOIN tag_map USING(tag_id) WHERE tags.tag IN('tag1', 'tag2', 'tag3') tag_map is a many-to-many relationship between tables ...
1
vote
1answer
2k views

What joins are available in Informix 9.2?

I'm testing some queries with an old version of Informix (9.2.1) and can't seem to execute even the most basic queries containing RIGHT OUTER JOIN or FULL OUTER JOIN. Tried several variations (RIGHT ...
1
vote
4answers
400 views

cross self join to get more rows - better solution?

I have a table employees: +----------------+------------------+ | Field | Type | +----------------+------------------+ | id | int(10) unsigned | | persons_id | ...
1
vote
3answers
167 views

How is a join performed by a database engine?

How is a join between two tables actually performed by a database engine? I am sure that listing one tuple against all tuples of the other table cannot be the way to perform the join; it's just a ...
1
vote
1answer
282 views

Finding Duplicates in LEFT JOIN

Here is my table structure: create table table_a (emp_name varchar(100), dept_name varchar(100)); insert into table_a values ('amar', 'finance'); insert into table_a values ('akbar', ...
1
vote
2answers
1k views

Improving performance of nasty nested-view joins

I have a moderate-sized database spread out over a few tables, the rough architecture is: Input Data (Data ID, Session ID and a few fields of statistical importance) Input File (Data ID and a blob) ...
3
votes
3answers
416 views

SQL JOIN Syntax in MS SQL

I have been taught in my MSSQL classes, this is how to join two tables select * from FirstTable A JOIN SecondTable B on A.ID= B.ID Now in my professional life, I came across JOIN queries like ...
1
vote
2answers
1k views

LEFT JOIN conversion to INNER JOIN, can't change FROM clause

I have 2 tables: BASE is master and is linked with CHILD via field ID: Left outer Join / 1:N relation. These 2 tables contain historised data, defined by 2 date-fields: start_date and end_date. ...
4
votes
2answers
918 views

Index on primary key not used in simple join

I have the following table and index definitions: CREATE TABLE munkalap ( munkalap_id serial PRIMARY KEY, ... ); CREATE TABLE munkalap_lepes ( munkalap_lepes_id serial PRIMARY KEY, ...
6
votes
1answer
214 views

Table order in Join Query

if i say: Table1 left join Table2 is it the same as saying Table2 right join Table1 ? In other words, should i expect to get the same results from 2 identical queries where the only thing that is ...
0
votes
1answer
2k views

How can I select the latest record having one state where no later records exist with any other state?

I want to select the last record (by datetime) having a given state where no later records exist with any other state for each given "widget". widget state timestamp ------ ----- ...
2
votes
1answer
618 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 ...
21
votes
1answer
2k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
0
votes
2answers
70 views

I couldn't compile this little query (needs 2 tables)(mysql) [closed]

i have 2 tables as like seen : user table : id, userid , country friends table : id, userid , friendid so i want to get friendid rows from friends table where friends.userid == user.userid and ...
1
vote
1answer
64 views

Joins in MySQL database

I am a newbie to the MySQL database.I want to know different types of joins in MySQL and any link or any other kind of documentation with examples which will clear the doubts will highly appreciable.
2
votes
1answer
118 views

Group_by and having vs Joins when table is self joined 5-6 times

Now the situation due to which this question came to me. Schema is Table User_Read_Book user_id | book_id Now I want to get users who have read certain books. Say give me users who ...
4
votes
1answer
4k views

mySQL query optimisation — multiple joins or select … where not in (select distinct…)?

Background I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
2
votes
2answers
5k views

How to combine UNION and INNER JOIN?

I'm fetching the grandparent of all nodes in a tree as follows: SELECT P.id, P.parent, GP.parent gp_id FROM product_groups P INNER JOIN product_groups GP ON P.parent = GP.id There is ...
6
votes
3answers
2k views

Why is this Full Outer Join not working?

I've used Full Outer Joins before to get my desired results, but maybe I don't fully understand the concept because I am not able to accomplish what should be a simple join. I have 2 tables (which ...
3
votes
1answer
2k views

Having trouble with Merge Join with SSIS

I am currently using Merge Join to perform an inner join on two sorted sets of data in SSIS. The problem I am having is that no records are being returned from the inner join. To troubleshoot I have ...
2
votes
5answers
9k views

Full Outer Join in MS Access

I have two staff lists: List A: StaffID Supervisor ==================== 0001234 NULL 0001235 0001234 0001237 0001234 0001239 0001237 and List B: StaffID Supervisor ...
1
vote
1answer
71 views

How can this data be joined?

Definition: table_A table_B id,description id,description,id_table_A Column id_table_A is a foreign key to the primary key of table_A and is not mandatory. Sample data: ...
2
votes
2answers
347 views

Joining based on date (or date range)

I'm not sure if I've completely fudged the design of my small hobby database (I'm not a DBA by any means), but I have a table like this (primary key is (staffid, effectivefrom)): staffid | target | ...
1
vote
1answer
388 views

Usefulness of Foreign Key WITHOUT reference option

Sorry, if this is naive question, but I searched a lot and did not get a vivid answers. Consider a simple InnoDB FOREIGN KEY without ON DELETE or ON UPDATE I think this will avoid INSERT of a value ...
2
votes
2answers
1k views

Limit WHERE to MAX() & COUNT()

This is my current mySQL query: SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread FROM TAB_EVENT e LEFT JOIN TAB_MESSAGE m ON e.id=m.event_id WHERE ( m.`read` IS NULL OR ...
2
votes
2answers
160 views

Validation of availability for a new order

I have a management application of sales, stock and payment on a warehouse whole saler from a web interface. In particular, when a order is effectuated it must create a line corresponding to each ...
5
votes
2answers
469 views

Unexpected extremely long query time (~5 minutes using nested WHEN-INs)

For the first time, I am running up against a horrifically long execution time of a MySQL query (~5 minutes). The data in the database is highly (and not arbitrarily) normalized. It very efficient ...
2
votes
3answers
322 views

Express union which is dependent on other results

I would like to make a union of two tables that are actually in a hierarchical constellation. How would I write that in relational algebra? Say table A is the parent of B in a 1:n relationship. ...
5
votes
2answers
1k 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 ...
0
votes
1answer
2k views

Oracle SQL for left outer join to rownum = 1 of another query?

Background I have a query joining many tables together, the chief of which is a workorder table. I also have a table called WOStatusHistory, which stores every status for every workorder. A diagram ...
2
votes
2answers
645 views

In DB2, how can I disable NLJOIN?

I want to indicate to the optimizer, via SET or optimization profiles, that NLJOIN shouldn't be used at all when optimizing a query. I've been looking for this for a while without any success. I ...
2
votes
2answers
874 views

Update column by comparing date and string values together

I have problem in updating table. I need to update table by comparing date and name with another table. I have two tables: table 1 : dim_sesid name(varchar) role ...
7
votes
3answers
797 views

Oracle's left join and where clauses errors

CREATE TABLE "ATABLE1" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); CREATE TABLE "ATABLE2" ( "COLUMN1" VARCHAR2(20 BYTE), "COLUMN2" VARCHAR2(20 BYTE) ); ...
2
votes
1answer
375 views

Query takes hours

Up until now all queries in my PostgreSQL application were fast. During the last days, sometimes a query takes several hours. Index are healthy, because after dump, restore, vacuum -z, it is still the ...
2
votes
2answers
353 views

get column from too many tables in mysql

.I have too many tables X1,X2,...Xn (they might exceed 75 tables) in Mysql DB to represent departments of a very large company. every table has structure something like this desc X1; ...
1
vote
1answer
143 views

How should row-specific metadata be created handled for an outer join view?

I have two tables, a large (millions) table of Tickets for orders, and a small (hundreds of thousands) table for Invoices metadata on a subset of those tickets. The metadata is information about when ...