Questions tagged [join]

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

Filter by
Sorted by
Tagged with
2 votes
3 answers
934 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. ...
user avatar
5 votes
2 answers
4k 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 ...
user avatar
  • 161
3 votes
1 answer
20k 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 ...
user avatar
2 votes
2 answers
2k 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 ...
user avatar
  • 533
2 votes
2 answers
4k 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 ...
user avatar
  • 43
10 votes
3 answers
5k 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) ); ...
user avatar
1 vote
2 answers
3k views

Join three tables

I have those three tables SELECT occuId as 'Occupation Id',occuDscr as 'Occupation Description' FROM occupation_field; +---------------+------------------------+ | Occupation Id | Occupation ...
user avatar
2 votes
1 answer
558 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 ...
user avatar
  • 1,341
3 votes
2 answers
894 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; +---------+------...
user avatar
  • 473
1 vote
1 answer
187 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 ...
user avatar
  • 2,033
2 votes
3 answers
44k views

Joining two tables on multiple criteria

I have two tables products and positions. products: id BIGINT PRIMARY KEY, product VARCHAR(100), criterium1 VARCHAR(100), criterium2 VARCHAR(100) positions: id BIGINT PRIMARY KEY, position ...
user avatar
  • 21
4 votes
3 answers
684 views

How can I select all online friends?

I'm currently working on a social network software written in PHP & MySQL, and it's all pretty fine, but one thing I'm stuck about is online friends. My tables looks following Users +-----+------...
user avatar
  • 319
13 votes
3 answers
9k views

Should I use SQL JOIN or IN Clause?

I have a question about best approach. I am not sure which approach is best when data is considered variable in size. Consider the following 3 TABLES: EMPLOYEE EMPLOYEE_ID, EMP_NAME PROJECT ...
user avatar
  • 231
1 vote
1 answer
1k 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?
user avatar
5 votes
1 answer
2k 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 ...
user avatar
3 votes
1 answer
30k views

Using multiple PIVOTs JOINs and GROUP BYs for advanced report generation

I've developed a timesheet recording system, that allows users to record: A Period of time (9:00 to 12:00) What they did during that time (DutyActivity: 1 hour report writing, 2 hours training) When ...
user avatar
2 votes
1 answer
468 views

Postgres planer JOIN removal

I have two tables, one contains a lot of rarely update data, and one contains a little of frequently update data. Every record in second table has corresponding record in the first, like following: ...
user avatar
  • 133
1 vote
3 answers
5k views

Grouping results to get unique rows after multiple joins

this is my first question on dba.stackexchange, hopefully it will be worth it. I am usually a bit more active on Stackoverflow as I am a developer. disclaimer : I don't have full control over the db ...
user avatar
  • 121
1 vote
1 answer
1k views

Simple select statement not returning correct data

I am trying to return data, where I want the item name (items.name), vendor name (vendors.name), url of the price (prices.url), and the price (prices.price) from 3 different tables (items,vendors and ...
user avatar
23 votes
2 answers
40k views

SQL Server Join/where processing order

After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
user avatar
45 votes
7 answers
87k views

Is it possible to mysqldump a subset of a database required to reproduce a query?

Background I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research). Question ...
user avatar
9 votes
1 answer
2k views

Why can't we perform joins in a distributed database like Bigtable?

From this answer to "What database does Google use?": Bigtable is not a relational database. It does not support joins nor does it support rich SQL-like queries. Is this obvious? I do not ...
user avatar
  • 3,261
3 votes
2 answers
4k views

mysql inner join producing more results than it should

I am trying to get the roles name, verbs name, and privileges enabled value where privileges is a join table joining verbs and roles through a verb_id and roles_id foreign key. But I am having ...
user avatar
  • 1,499
20 votes
3 answers
8k views

Column name naming conventions and best practices

I would like some expert opinion on best practices when it comes to column naming. The background is that according to Wikipedia, the following syntax, SELECT ... FROM Employees JOIN Timesheets ...
user avatar
  • 303
4 votes
2 answers
763 views

How do I calculate working business minutes?

I'm working on business inteligence reports and one of my task is to create reports for time tracking. I have to report on business minutes (10 hours per day) within business week (monday to friday)....
user avatar
  • 141
5 votes
3 answers
4k 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 ...
user avatar
  • 1,467
23 votes
3 answers
126k views

What is more efficient, a where clause or a join with million plus row tables?

We run a website that has 250MM rows in one table and in another table that we join it to for most queries has just under 15MM rows. Sample structures: MasterTable (Id, UserId, Created, Updated...) -...
user avatar
2 votes
1 answer
2k 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 2007-04-...
user avatar
  • 145
3 votes
1 answer
908 views

SQL Return Count of Answers with 0's for Choices from another table

My question went dead after one unhelpful answer on SO here I need this to be optimized because it will be called often. It is going into a stored procedure. I am given @slide (tblILDSlide.id) and @...
user avatar
2 votes
3 answers
145 views

Can I automate the "on" statement from MySQL queries?

Background In the process of QA/QC, I am writing lots of queries with multiple joins. I am using MySQL. I would like to know how (if) I can simplify join statements, e.g. by setting default fields ...
user avatar
5 votes
3 answers
11k views

Query to normalize table/combine row text

I have a table (call it oldTable) with columns like so: ID (int),Rank (int),TextLineNumber (int),SomeText (varchar) The primarykey is multi-part: ID+Rank+TextLineNumber. I'm trying to transform/...
user avatar
  • 53
15 votes
5 answers
128k views

Matching single column against multiple values without self-joining table in MySQL

We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data: user_id ...
user avatar
8 votes
1 answer
252 views

Are relations slower than a big, inefficient table?

I've been asked in my job to violate the first normal form (repeating groups across columns, using empty / null values) several times, "for the sake of computer processing power". In a nutshell, a "...
user avatar
  • 405
2 votes
3 answers
4k 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? ...
user avatar
  • 1,101
16 votes
2 answers
1k views

Peculiar Oracle outer join syntax case

I have seen the following in a query that was supposed to be ported from Oracle outer join syntax to SQL standard outer join syntax: SELECT ... FROM A, B, C, D, E WHERE A.A_ID = B.A_ID AND B.B_ID = C....
user avatar
11 votes
3 answers
234 views

Are there any database engines which will intuit the join condition based on the existing foreign keys?

It seems strange to me that, when I've defined a foreign key, the engine cannot use this information to automatically figure out the correct JOIN expressions, but instead requires me to re-type the ...
user avatar
  • 1,344
35 votes
8 answers
50k views

What is the difference between an INNER JOIN and an OUTER JOIN ?

I am new to SQL and wanted to know what is the difference between those two JOIN types? SELECT * FROM user u INNER JOIN telephone t ON t.user_id = u.id SELECT * FROM user u LEFT OUTER JOIN ...
user avatar
  • 509
8 votes
3 answers
347 views

What does the output of a JOIN statement look like?

I've been wanting to use joins for a while, but I'm having trouble visualizing the output so I know how to put it to use. Let's say I have 2 tables: CREATE TABLE Cities ( id INT UNSIGNED PRIMARY ...
user avatar
  • 937
12 votes
3 answers
414 views

Which is faster join

Which is faster SELECT * FROM X INNER JOIN Y ON x.Record_ID = y.ForignKey_NotIndexed_NotUnique or SELECT * FROM X INNER JOIN Y ON y.ForignKey_NotIndexed_NotUnique = x.Record_ID
user avatar
  • 229

1
34 35 36 37
38