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

learn more… | top users | synonyms

0
votes
2answers
213 views

Counting results for cross join

How I can get count results with cross join? Example cross join: SELECT t1.firstname, t2.lastname FROM table1 t1 CROSS JOIN table1 t2
4
votes
1answer
441 views

NEWID() In Joined Virtual Table Causes Unintended Cross Apply Behavior

My actual work query was an inner join, but this simple example with cross join seems to nearly always reproduce the problem. SELECT * FROM ( SELECT 1 UNION ALL SELECT 2 ) AA ( A ) CROSS JOIN ...
2
votes
2answers
117 views

Joining 4 tables and 2 counts

I have four tables. UserMaster ---------- UserKey - PK UserName UserID - UNIQUE UserLogTransaction ------------------ UserID - FK:UserMaster.UserID LoginTime LogoutTime CallCenter ---------- ...
0
votes
2answers
619 views

Slow query with straight_join for small result

I have a performance-problem with a query in my application and I don’t understand the behavior of mysql. The query consists of different joins, especially the join to the mentioncache-table. If the ...
-3
votes
3answers
3k views

Join tables on a partial string match

input: I have a table containing (any number of) user-supplied strings: ╔═══════╗ ║ Value ║ ╠═══════╣ ║ To ║ ║ An ║ ╚═══════╝ And a table of user details: ╔════════╦══════════╗ ║ UserID ║ ...
2
votes
1answer
91 views

JOIN 2 tables, and query data from them 3 times

Basically I have 2 tables: post_meta and posts. post_meta has a post_id, key and value: post_id key value 6343 make Chevy 6343 model Volt 6794 make Chevy 6794 model ...
3
votes
1answer
414 views

Complex SQL Query

I'm trying to formulate a single query to return a nice list of a host's interfaces and the interfaces attributes from the Cacti MySQL DB. Here are the tables I am referencing: host_snmp_cache: ...
2
votes
1answer
587 views

Update ranking on table

I have two tables: mysql> desc rank ; +----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
2
votes
1answer
560 views

inner join on PK with extra criteria slow despite indices?

Given the two tables below I am struggling to understand: why is the third query slow even though first two queries are fast what exactly is EXPLAIN saying can I do anything to significantly speed ...
3
votes
2answers
733 views

SQL ANSI JOIN precedence

I have a query that looks like this: SELECT * FROM TBLA A LEFT JOIN TBLB B ON A.Col1 = B.Col2 RIGHT JOIN TBLC C ON B.Col3 = C.Col4 JOIN TBLD D ON C.Col5 = D.Col6 In which order ...
5
votes
3answers
904 views

Many-to-Too-Many Relationship

I'm working on a book database. And among others, I have the following two tables (a bit simplified for the question): tblBook: ID, Title tblPerson: ID, Name Note that the tblPerson table contains ...
1
vote
0answers
750 views

multiple child records in a single row by join

I have one master table (teacher) structured like teacherId Name Class 1 Praveen 10 2 John 9 and having a child table (student) structured like studentId ...
1
vote
0answers
169 views

Multi-Table Join to Single Destination

I am having a serious performance issue with join together four tables and placing the results (in a certain permutation) into one destination table. I am using MSSQL 2008 and good ole T-SQL. To ...
2
votes
2answers
1k views

Select rows with repeat data within certain timeframe

As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on ...
5
votes
2answers
135 views

mysql join by 2 ways - how it works? - which gives performance difference

I had 2 different ways to query the content which showed performance difference when executed. The 1st way is EXPLAIN select SOME_COLUMNS from ( select * from A where CONDITION ) p inner join ( ...
1
vote
2answers
516 views

Count consecutive null rows from a joined table

I have the following tables: members: ---------------------------------------------- | member_id | member_name | member_join_date | ---------------------------------------------- events: ...
1
vote
2answers
91 views

Efficiency for contact-contact relationships schema

I'm migrating data from a schema where people and organisations are separate tables to one where people and organisations are all treated as contacts (they share a lot in common). Currently, the ...
3
votes
3answers
4k views

Alternative query to this (avoid DISTINCT)

Note: I work with MSSQL 2008, but I guess it's valid for many others DB engines I have this table "Users": UserID User CountryID 1 user 1 1 2 user 2 2 3 user 3 3 4 user ...
2
votes
1answer
506 views

Should I use left join to do my job in this scenario?

What I want to do is to find out the items which appeared more than once in this table: mysql> desc tables; +-----------------+---------------------+------+-----+---------+-------+ | Field ...
1
vote
1answer
187 views

Querying multiple tables

I am working on a query from our ticketing system, and I am having problems figuring out the best way to join these tables together. I have the following tables/fields: SR_service: stores ticket ...
1
vote
1answer
1k views

Optimizing multi-table left joins

I have four tables which I am trying to join together. Table event +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
3
votes
5answers
2k views

Does 'Cities' deserve a separate table?

I have the following Customers table: customer_id - int company_name - nvarchar street - nvarchar city - nvarchar comments - nvarchar The app will only be used in part of one small country ...
5
votes
1answer
430 views

Build a three table join with a recusive table in the middle?

I have three relevant tables: Parts, PartGroup, and MarkupGroup. Parts is simple. PartID artificial primary key Part part number PartGroupID Foreign key sample data: 1 ...
3
votes
1answer
5k views

how to fetch different data on compare rows of two tables

I have two tables in my database, one is user_app table in which user info are stored, table are as: +--------------+--------------+------+-----+-------------------+-----------------------------+ | ...
0
votes
3answers
2k views

INNER JOIN making COUNT(*) slow

I have a very simple query: SELECT COUNT(*) FROM messages INNER JOIN users ON messages.user_id = users.user_id With the join it takes 1146 ms and without it takes 220 ms (220 ms still seems slow to ...
3
votes
3answers
1k views

CROSS JOIN with temp table and CASE statement

I'm trying to CROSS JOIN a table with a view. Here is my code: SELECT * FROM ( SELECT * , CASE TypeId WHEN 1 THEN InGs828S WHEN ...
12
votes
2answers
3k views

SQL Server does not optimize parallel merge join on two equivalently partitioned tables

Apologies in advance for the very detailed question. I have included queries to generate a full data set for reproducing the problem, and I am running SQL Server 2012 on a 32-core machine. However, ...
1
vote
1answer
631 views

Mysql Join to get multiple row value from

I am new to my Mysql and need help regarding join I have a table called employee and this table contain some foreign key value like phone, address, sex. Table phone and address have 4 rows in it, I ...
0
votes
3answers
271 views

Is adding rows better then adding columns in case their value is not consistent?

In my project I have a table of customers, each customer has his own special price for each product, It's a small business and there are currently about 5 products. The manager says there might be ...
1
vote
2answers
636 views

LIMIT a query Postgresql

im doing this query and it gives me multiple hits because of each assetid may contain many vb.title, how can i limit the result to only show 1 hit for each assetid? 'select DISTINCT v.id, ...
2
votes
2answers
115 views

Long JOINS Returns No result all tables have one to many relationship with a particular table [closed]

I have a Table Enterpries with Primary Key 'RCNO'. All other tables have a one to many relationship with Enterprise via it's RCNO column. I make a Join of all the tables and i get nothing. Below is ...
2
votes
1answer
207 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
819 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
631 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
1k 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
908 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
2k 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
72 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
8k 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
850 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
936 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
479 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
3k 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
40 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
226 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
669 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
482 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
201 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
433 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', ...