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

learn more… | top users | synonyms

0
votes
1answer
29 views

SQLITE table joining with group

MESSAGE TABLE USERS msg_id | msg_to | msg_from | msg_body | msg_date user_id | user_name 1 1 2 hi 22/1/14 ...
1
vote
2answers
86 views

JOIN on variable number of columns

I have the two tables courses AND something_about_courses: courses course_code course_session course_type something_about_courses course_code course_session some_description To ensure that I ...
2
votes
1answer
481 views

Join tables in two databases make query slow? is better to partition db?

I have a table1 in db1 and table2 in db2 on SQL Server 2008 R2. If I make a select query joining both tables, it's really slow to get the results. A simple query like SELECT * FROM db1.dbo.table1 ...
0
votes
1answer
75 views

Pivoting some row data into columns (SQL Server 2008 R2)

Sorry if this has been answered before but I'm exhausted and on a time line. Maybe I'm just an idiot. I'm designing a data export from One application to import into another. Full-Disclosure: I did ...
0
votes
1answer
75 views

queries for hierarchical data

This table is a questions and answers CMS and i want to know how data is fetched from these tables. +------+----------+-------+ | id | parentid | type | +------+----------+-------+ | 1 | 0 ...
0
votes
1answer
47 views

JOIN on 3 tables being slow

everyone. Can't figure out what indices are missing or how to rephrase my query. I created about a milllion of test entries in each table to see performance. Query takes about 1.5 seconds which is ...
1
vote
0answers
160 views

Outer Apply vs Left Join Performance

I am Using SQL SERVER 2008 R2 I just came across APPLY in SQL and loved how it solves query problems for so many cases, Many of the tables I was using 2 left join to get the result, I was able to ...
2
votes
2answers
6k views

How to JOIN two table to get missing rows in the second table

In a simple voting system as CREATE TABLE elections ( election_id int(11) NOT NULL AUTO_INCREMENT, title varchar(255), CREATE TABLE votes ( election_id int(11), user_id int(11), FOREIGN KEYs for ...
2
votes
2answers
216 views

“All joins can be rewritten to sub queries”, is this statement true?

I wonder if there any case that a join can not be converted to a sub-query. BTW: I am not discussing advantages and disadvantage of using sub-query or using join.
1
vote
4answers
5k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
0
votes
2answers
57 views

left join not working [closed]

CREATE TABLE `table1` ( `id` INT(10) NOT NULL ) ENGINE=InnoDB; CREATE TABLE `table2` ( `id` INT(10) NOT NULL, `value` INT(10) NOT NULL ) ENGINE=InnoDB; INSERT INTO table1 VALUES (1), (2), (3), (4); ...
1
vote
1answer
43 views

Joins in Update statement in sqlite

CREATE TABLE Table_1 ( Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , EmailId VARCHAR ) INSERT INTO Table_1 VALUES (1,'raj@gmail.com'); INSERT INTO Table_1 VALUES (3,'test@gmail.com'); ...
0
votes
1answer
22 views

PostgreSQL SQL syntax - JOIN and substrings

I have a single table of hostnames and NFS exports, some but not all of which are related. Example entries: HOST VOLUME ----- ---------- host1 vol1 host1 vol2 host1 ...
0
votes
0answers
29 views

Minimize table joins in design

Probably didn't come up with the best title, but I'll explain what I'm trying to do. I need to be able to track gym members, trainers, workouts, and gym locations. I've created a simple example below. ...
1
vote
1answer
17 views

MySQL Join, Force NULL Row

I'm having a bit of trouble figuring out how to do this -- I need to select all rows from table t1, join table t2 -- but include a null row from t2 for each record in t1 (even if rows match in t2). ...
0
votes
2answers
27 views

MySQL get values from the other table by using inner joins

In mysql I have 3 tables called as ps_product_lang ps_order_detail and ps_image here are the tables sample data === ps_product_lang === CREATE TABLE IF NOT EXISTS `ps_product_lang` ( ...
1
vote
1answer
124 views

SQL Server Multiple JOINS with Table Value Function - query never ends

I have a query with 4 joins using a table value function to get the data and when I execute it the query never ends. Issue Details Table valued function: CREATE FUNCTION [dbo].[GetIndicator] ( ...
3
votes
1answer
3k views

Subqueries run very fast individually, but when joined are very slow

ypercube solved the problem. Subqueries were totally unnecessary, and the whole thing works with plain joins. It is still strange that MySQL's optimizer could not make use of my original query, ...
0
votes
1answer
38 views

Query Rows That Have a Following Row That Meets Criteria

Will try to do my best to explain this, as I don't really know how to word this to search for an answer. I'm a constantly learning but still newish SQL user who thus far has been able to solve most ...
2
votes
1answer
85 views

If an INNER JOIN in a query returns no rows, do additional INNER JOINS add a significant performance overhead?

If I make a query as follows: SELECT * FROM TableA A INNER JOIN TableB B ON A.AID = B.AID INNER JOIN TableC C ON B.BID = C.BID Does the INNER JOIN with TableC add significantly to the performance ...
0
votes
0answers
34 views

self join of single table to find time difference of adjacent rows of a single column which are not in sequence

I want to find the time difference between two adjacent rows of a single table which are not in sequence.i made a self join of table and tried but i did not achieve what i need .can anyone please help ...
1
vote
1answer
67 views

Cartesian product of two tables not working as expected in MySQL but fine with SQlitee?

I was practising some sql joins and decided to create a schema online rather than using my local database on sql fiddle. My schema is pretty simple table 1 (5 entries): (emp->{id,name,dep}) table ...
-1
votes
3answers
85 views

Trouble quering a join in oracle sql

Theres a question on an exercise which asks me to write an SQL statement to show the SKU, SKU_Description, WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. I have to use a ...
2
votes
1answer
62 views

Understanding JOINs and why the syntax works the way it does

Heyo, I'm confused as to how this works: UPDATE original SET price=(SELECT price FROM new WHERE new.id=original.id) WHERE id IN (SELECT id FROM new); What it does is updating an original table ...
0
votes
0answers
73 views

Are heterogeneous joins between MS Access and SQL Server tables really so bad?

I'm developing a multi-user Access 2013 application with an Access backend on a fileserver. At first I'm going to finish my stable Access application which works fine for the moment. However, I plan ...
1
vote
2answers
44 views

Merge output of two Queries MYSQL

I have a mysql table with the following schema: +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
0answers
47 views

Right Outer Join (column name doubt)

I've read the following code a few times trying to figure out what it is doing. "A right outer join is that which returns the unmatched rows from the right tables with matched rows from the left ...
1
vote
0answers
19 views

Can I automate data pulling from VistA databases using MUMPS? [closed]

I apologize if this is on the wrong StackExchange website. This seemed to make the most sense because it's a database question and there's no longer a Healthcare IT site. I'm an intern at the VA ...
1
vote
1answer
127 views

MySQL: Which join is better between left outer join and inner join

Which join is better peforming if all of them provides the same result? For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). ...
12
votes
7answers
15k 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 ...
2
votes
1answer
42 views

WHERE attribute having two relation lines in the same SQL

I have a database designed with tree tables: checklist (id, name) tag (id, name) checklist_tag (checklist_id - FK, tag_id - FK) Running this SQL: SELECT cc.id as checklist_id, cc.name as ...
1
vote
4answers
123 views

How to join the latest previous record with SQL

The original question was posted on stackoverflow. I think this site could be more focused on db related topic. I have a table whose schema is like this: CREATE TABLE product_shipping( ...
2
votes
1answer
65 views

Conditionally join table

I'm new to database design and have designed a database that seems fine for the application but looks like it has a major red flag to me. I have a master table which is then joined to another table ...
0
votes
0answers
10 views

How to join the latest previous record with SQL [duplicate]

I have a table whose schema is like this: CREATE TABLE product_shipping{ product_id CHAR(10), product_name CHAR(10), deal_dt DATETIME, deal_reason_no SMALLINT, cancel_dt DATETIME, cancel_reason_no ...
0
votes
1answer
32 views

Query to join three tables

I have three tables which I have to join. I'm not a good SQL writer. Can please sombody help me to prepare this using joins: branches ----------------------------------------- branch_id | branch_name ...
1
vote
1answer
37 views

Merge a table into an existing one

I know how to JOIN two tables as create a new table out of the results, using CREATE TABLE result_table AS ( SELECT * FROM tableA JOIN tableB ). I forgot to include some columns from the JOIN in the ...
3
votes
2answers
105 views

How to inner join some tables and outer join others?

I'm certain I'm going to be embarrassed by the eventual answer, but I've been googling around and banging my head against the wall and can't figure it out. I have four tables described below. I'm ...
0
votes
2answers
56 views

Using a join to return multiple values of the same column?

I have two tables user Rank In the Rank table, I saved rank_id and supervisor_id. In the Rank table, I stored the rank name. The rank_id and supervisor_id are foreign keys from the Rank table. ...
1
vote
2answers
98 views

Get only the oldest entry in a join

So I have a table bugs |bugid| title| the table is lacking a date column. But when creating a new entry, most likely always a new entry gets created in bugnotes as well saying "bug created" with the ...
0
votes
1answer
106 views

How to INNER JOIN and OUTER JOIN within the same table?

I am using PostgreSQL and I have the following tables: Product ID | Name | Price Receipts ID | Month | Quantity | Product_ID (FK) Sales ID | Month | Quantity | Product_ID (FK) I am trying to ...
0
votes
1answer
54 views

How to simulate a table join on itself?

Let's assume this is my current data: product_number date value 100 2010-01-01 1 100 2010-02-01 1 100 2010-03-01 1 200 2010-01-01 1 ...
1
vote
1answer
15 views

Select the last logged change before a specified time from a change list

I want to get the last change for all issues issue, that was made before a specified end time. I have 3 tables. projects +---+------------+ |ID |project_key | +---+------------+ |1 |bug_tracker | ...
0
votes
2answers
96 views

Database search with multi joins

I have a MySQL database and I want to perform a little bigger search. I have about 10k records in one of the tables and It's expected to grow, but slowly. The biggest problem is that to perform the ...
0
votes
1answer
56 views

How can I control the order of a query regarding to inner join

I have 3 tables. They have table_a 5m records table_b 20m records table_c 20m records I'm performing the query below. SELECT * FROM table_a a INNER JOIN table_b b ON a.a_id = b.a_id AND ...
0
votes
1answer
68 views

How to join 3 tables together given 2 many-to-many relationships tables in between?

I have 3 main tables: Person, Publication and Conference and 2 many-to-many link tables in between: person_publication and person_conference I want to join all 3 main tables together (showing me all ...
1
vote
2answers
372 views

Mysql LEFT JOINing same table twice

mysql> SELECT * FROM customers; +----+------+ | id | name | +----+------+ | 1 | Matt | | 3 | John | +----+------+ mysql> SELECT * FROM purchases; +----------+------+-------+ | owner_id | type ...
0
votes
0answers
48 views

Why a select from the tables, joined by a CASE statement, could be 7x slower?

There are two queries from the same two tables: one joins them on the columns @t1.b and @t2.x, the other uses a case statement as below. The 2nd query is 7x slower. The record counts are only ...
1
vote
1answer
4k views

Select records in one table but not in another, when the second table is not matching on primary key

I have two tables from which I need only the results that appear in one table (list) which do not appear in the second table (cardinal). The list table's primary key is sku, and the table has a ...
0
votes
0answers
74 views

How to know the correct indexes for mysql?

I tried to find out with explain where I must set the correct index. A current select takes about 20 seconds. Can someone tell me which would be the correct index for my tables? SELECT trade.*, ...
0
votes
0answers
39 views

Oracle right outer join SELECT statement with a table

I am trying to find out the tables that are in MONITORING.USAGE_TRACKING table in first place, along with the other tables that need to be analyzed. SELECT A.JOB_NAME, A.START_DATE from ...