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

learn more… | top users | synonyms

1
vote
1answer
529 views

How to add additional tables/fields into a query with multiple inner joins?

Given this query (many thanks to RolandoMySQLDBA from question Select records that do not have associations outside a certain list), how do I add other fields from other tables into my result? SELECT ...
3
votes
1answer
603 views

Get data from two tables with recursive relationships

I have a MySQL 5.1 DB, being used behind a PHP application to track permissions and other items that need to be issued to staff that hold various positions within an org. To track what privileges a ...
1
vote
1answer
1k views

Immediate refresh materialized views (MQT) with OUTER JOIN under DB2

DB2 accepts a materialized view based on an "OUTER JOIN" query, as long it doesn't refresh automatically (REFRESH IMMEDIATE). I cannot find a simple solution to get round this problem. Perhaps with ...
0
votes
1answer
45 views

Getting the name of the column that was used in a join?

This may be a bit of a weird question: Our project's datamodel has multiple tables with multiple columns any one of which can be used to join with one settingtable. The question here is how to get ...
1
vote
0answers
799 views

How can I join one to one to (many to one) without using group_concat

I have a table structure as follows: -- -- Table structure for table `emails` -- CREATE TABLE IF NOT EXISTS `emails` ( `ID` int(5) NOT NULL AUTO_INCREMENT, `email` varchar(255) COLLATE ...
1
vote
0answers
38 views

parenthetic grouping of join clauses

What is the difference between these joins? a left join b left join c (a left join b) left join c a left join (b left join c) Does the grouping only affect the order of the joins? Will the query ...
0
votes
2answers
7k views

MySQL: Join one Row in a table with two Rows in other table

In my MYSQL Database COMPANY. I have two tables, like below in my diagram (arrow shows relations): `users` `user_login` +--------------+ ...
1
vote
1answer
324 views

Visually create a join using PHPMyAdmin

I recently started using SQL Server 2008 express and it has a really nice joins interface, whereby you can visually create a join. Is this possible on PHPMyAdmin?
0
votes
1answer
108 views

Why is there such a huge performance difference in these two join statements?

I have a query which i first wrote as (query has been simplified a bit) : select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c inner join product p on p.indID = ...
0
votes
1answer
91 views

Speed efficient query for membership first joined, latest category from membership table (min, max)

I have the following table representing membership information: CREATE TABLE IF NOT EXISTS `membership` ( `id` int(11) NOT NULL AUTO_INCREMENT, `organisation_id` int(11) NOT NULL, ...
3
votes
1answer
6k views

Oracle: Non key-preserved table should be

I'm getting "ORA-01779: cannot modify a column which maps to a non key-preserved table" when I try to update a join. I've searched around the site and found a lot of advice on what key-preserved ...
1
vote
2answers
363 views

Expand sparse table with self outer join on distinct values

I have this: g1 | g2 | x ---------+-----------+----- New York | Monday | 210 New York | Tuesday | 258 Chicago | Monday | 30 Chicago | Wednesday | 25 LA | Thursday | 40 ...
2
votes
2answers
1k views

Postgres RIGHT JOIN with custom array

I'm using Postgres 9.1 and want to get a result with some blanks where there is no data. My query looks like the following: SELECT institution_id FROM ... WHERE institution_id IN (1, 3, 4, 5, 7, 9) ...
4
votes
1answer
238 views

How to write this self join based on three columns

Hello there I have a following table ------------------------------------------ | id | language | parentid | no_daughter | ------------------------------------------ | 1 | 1 | 0 | ...
1
vote
1answer
332 views

join the two tables which possess one to many relationship

In drupal,the content type table and my own table is used to do some operations. Im having two tables namely Table1:'content_field_mem_id' & Table2:'Ex' description of ...
1
vote
1answer
549 views

oracle huge hash_area_size

I am wondering what is the maximum setting for hash_area_size parameter? I'm trying to perform a huge join which ends up allocating 10Gb of temp space and completes in an hour. 10Gb is not that much ...
2
votes
1answer
1k views

Selecting minimum value using a subquery

I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating. We have a table for prices ...
1
vote
1answer
135 views

Join Calender Table

I have a Datatable and a Calendartable. I do a join on this tables to get sequential dates if there is no data for this day. My problem now is that there a several id's and i need a count for each ...
0
votes
1answer
259 views

Mysql Join with filters

I have two tables: MAINCOLOR and SUBCOLOR with the data as: MAINCOLOR : MAINID COLOR 1 RED 2 BLUE 3 GREEN SUBCOLOR: SUBID MAINID SUBCOLOR STATUS 1 1 PINK 0 2 1 ...
1
vote
3answers
529 views

Complex query with multiple normalized fields

We have a fairly simple table structure, but with a LOT of fields per table (talking 40+). This data is initially produced in plain-text, user-readable tables, but then it is translated into ...
1
vote
2answers
833 views

How to get non repeat results

I've two tables default_tb_persona and default_tb_envio, this are the DDL for those tables: CREATE TABLE `default_tb_envio` ( `codigo` varchar(7) NOT NULL, `fecha` date NOT NULL, `id_modalidad` ...
1
vote
1answer
691 views

How and what is the most efficient way to join two tables, retaining a particular field from both?

I have two tables, with the following columns: Table 1 ID Component Data1 Data2 Table 2 ID Component Data3 Data4 If I have rows in these tables, with items Component1, Component2, Component3, ...
2
votes
1answer
1k views

How does mysql deal with queries that touches myisam and innodb tables?

In our mysql database, we use both myisam and innodb tables, though there are more myisam tables. QUESTIONS If a query involves both myisam and innodb tables, will the query use table lock or row ...
3
votes
1answer
1k 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; ...
4
votes
2answers
786 views

Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that's in 2000 (80) compatibility mode

We have a very old database running on SQL Server 2008 R2. The database is running in 2000 (80) compatibility mode and has oodles of NON-ANSI joins in stored procedures, views and functions. We need ...
5
votes
2answers
535 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 ...
4
votes
1answer
253 views

Is there a standard way to replace codes with values from a lookup table for reporting or analytics?

Suppose I have a "main" table that was probably heavily normalized and consists largely of columns that merely contain codes that are lookups into other tables (they are probably foreign keys but feel ...
1
vote
1answer
3k views

Retrieving data from inner join using LIMIT and OFFSET

These are my two tables: table1 qid[PK] |gid[PK] |abcd | xyz | date ---------------+---------+---------+------+------------ 00001 | qwe | 54 | a | 1994-11-29 00002 ...
0
votes
1answer
823 views

Group By on column from joined table

its about the following query. EXPLAIN SELECT phrase, infinitiv FROM `dict_verbs_all` `t` INNER JOIN `dict_verbs_all_language` `verbsGerman_c` ON (`t`.`id`=`verbsGerman_c`.`verb_id`) INNER JOIN ...
0
votes
2answers
376 views

Tuning query to remove joins for reporting

This is the query I was sent. There is currently no data in the system as I was converting this query from teradata to SQL server. SELECT user_id FROM user_table LEFT OUTER JOIN ( SELECT user_id , ...
0
votes
2answers
265 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
2
votes
3answers
1k views

How do I join two tables on two different instances of DB2?

There are two instances of DB2 running on the same machine. One has a table aliased to the a table in the other one. Is there a way to do this in DB2 or does it have to be done via an external tool? ...
4
votes
1answer
514 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 ...
1
vote
2answers
716 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
2answers
121 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 ---------- ...
1
vote
1answer
568 views

SQL Server: join two tables with “valid-to” dates

I have two tables like so: T1_Key, T1_Data, T1_ValidUntil 1 A 2000-01-01 1 B 2000-06-30 2 C 2005-05-31 3 D 2004-12-31 3 E ...
2
votes
1answer
93 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
476 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
657 views

Update ranking on table

I have two tables: mysql> desc rank ; +----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
2
votes
1answer
607 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 ...
4
votes
3answers
2k views

Optimizing query using view on EAV structure

An application is writing into a database that follows an EAV structure, similar to this: CREATE TABLE item ( id INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE item_attr ( item ...
6
votes
2answers
875 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
1k 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 ...
4
votes
2answers
760 views

Right Full Outer Join Query

I came across 1 query which is Select * from R Natural Outer Join S Where R=(A,B) has tuples {(1,2),(1,2),(3,4)} and S=(B,C) has tuples {(2,5),(2,5),(4,6),(7,10)}. To implement this I created 2 ...
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 ...
1
vote
2answers
576 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
93 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
5k 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 ...