An SQL join clause combines records from two or more tables or views.
2
votes
2answers
2k 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
264 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 ...
1
vote
1answer
393 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 ...
1
vote
1answer
36 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
110 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 (
...
0
votes
2answers
64 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 ...
3
votes
1answer
308 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
1k 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
334 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
110 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
162 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', ...
3
votes
2answers
575 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,
...
1
vote
2answers
600 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)
...
1
vote
2answers
682 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.
...
3
votes
3answers
308 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 ...
6
votes
1answer
176 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 ...
2
votes
1answer
513 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 ...
1
vote
1answer
61 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
85 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 ...
3
votes
1answer
2k 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
3k 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
1k 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
2answers
257 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 ...
1
vote
1answer
62 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:
...
3
votes
1answer
1k 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
1answer
339 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
200 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
215 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
682 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 ...
5
votes
2answers
335 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
2answers
143 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 ...
2
votes
3answers
229 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
613 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
1k 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 ...
5
votes
3answers
227 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 ...
2
votes
2answers
733 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
366 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
2answers
240 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;
...
0
votes
1answer
114 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 ...
24
votes
8answers
9k 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 ...
3
votes
3answers
226 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
...
9
votes
3answers
2k 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
...
2
votes
1answer
2k 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)
...
5
votes
5answers
10k 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 ...
2
votes
1answer
232 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:
...
1
vote
3answers
795 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 ...
1
vote
1answer
277 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 ...
6
votes
2answers
1k 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 ...
6
votes
1answer
433 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 ...
1
vote
3answers
114 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 ...
