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

learn more… | top users | synonyms

0
votes
2answers
67 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 ...
0
votes
1answer
39 views

Mysql improve Self join performance large table?

I have a task to help identify conflicting IP ranges , I want to highlight when one companies IP range falls within another's ... below is the sample table, I have converted the IP ranges from .dot ...
0
votes
1answer
144 views

Data Analysis with MongoDB or MySQL

I have a need for processing some data in order to come up with some statistic. This is the scenario: Each user can answer several questions from the question list. They pick their answers from a ...
2
votes
1answer
28 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
66 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
42 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
9 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 ...
-1
votes
3answers
39 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 ...
1
vote
0answers
8 views

Can I automate data pulling from VistA databases using MUMPS?

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 ...
0
votes
1answer
27 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
28 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
77 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
1answer
56 views

SQL SELECT - adding field to SELECT with JOIN is changing the results

I have a SQL sentence with JOIN, running on mysql 5.5.16: SELECT DISTINCT (ot.orders_id), ot.value FROM orders_status_history osh INNER JOIN orders_total ot ON ( osh.orders_id = ot.orders_id ) WHERE ...
1
vote
1answer
105 views

How to improve the performance of SELECT queries with multiple JOINs on a 50GB database hosted on AWS m3.medium MySQL RDS?

Example query: SELECT p.ListAgentEmail, p.ListAgentFirstName, p.ListAgentLastName, p.ListingKey FROM Property_RES AS p INNER JOIN Property_RES_COUNTIES_OR_REGIONS ON ...
0
votes
2answers
32 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
60 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
55 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
41 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
13 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
89 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
50 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
35 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
50 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
42 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
116 views

Joining on a field with different values

I am trying to join data from two completely different sources. One source contains an employee's schedule information, and the other tracks what they actually worked (like what time they actually ...
1
vote
1answer
2k 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
71 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
27 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 ...
0
votes
1answer
25 views

Optimizing a query so MySQL will use the indexes for the joins

Some context: migrating some data from a 3rd party cloud MongoDB platform service into MySQL. Wrote a query but it's very slow. Here's the query: SELECT Table1.*, (long list of fields for each ...
1
vote
1answer
101 views

Minimizing Page Fetches

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
0
votes
1answer
144 views

Does MySQL load the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY?

I have a table that isn't very large row wise (in the 100ks range), but that contains a lot of raw data that is very large in size. Despite having a relatively little number of rows, it's around ...
1
vote
2answers
98 views

MYSQL joining 3 tables with left join

I have these tables: pupils: (id_pupil, name, surname, email, user, pass, level, class) incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester) ...
1
vote
1answer
34 views

Joining Three Tables by using IDs as Foreign Keys

I'm starting out with learning MySQL and so far I realized I need to JOIN three tables. The three tables are Users Groups Members There is a list of users, a list of groups that users can join, ...
0
votes
1answer
42 views

Best Practice For Data Views with Linked Server in SQL Server

In SQL Server, I have to design a View that I can map on a DBML file in order to dynamically retrieve information from the database with multiple conditions to filter. The problem is that I have to ...
1
vote
1answer
41 views

Get data per Customer and per Date

How will I get the all the date even though there is no order between two dates? Sample Table OrderID | Date | CusID 33942 | 6-21-2014 | 6005 34059 | 6-20-2014 | 4003 53333 | ...
0
votes
1answer
49 views

Join 3 tables returns too many results [closed]

I have 3 tables: users, posts, videos. The last 2 tables have user_id that associate with users.id (first table). The problem is that when I try to run inner join query and return all posts and ...
1
vote
4answers
3k 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
0answers
31 views

Relational calculus. Natural join

How does natural join look in relational calculus? I think it looks like {A1, A2, A3, A4 | r1(A1, A2, A3) AND r2(A2, A3, A4)} Correct me please if I am wrong. Thank you very much!
1
vote
0answers
48 views

Join and aggregate result and insert blanks for missing appendants

Consider these two simplified tables: CREATE TABLE "sources" ( "id" serial NOT NULL, "name" text NOT NULL, CONSTRAINT "sources_pkey" PRIMARY KEY ("id") ); CREATE TABLE "values" ( ...
3
votes
1answer
37 views

Doing a join to search geolocation points in zones

I have a series of geolocation points in a table, and a series of "zones" in another; I'd like to do a join to identify which zone each point belongs in (if at all.) Points table looks like: ...
1
vote
2answers
84 views

Inner Join Performance

This is my first question on this forum, so be patient... My question is about the most efficient way to build a query on the following table: create table OpenTrades( AccountNumber number, ...
0
votes
1answer
147 views

Optimizing multiple MySQL sub-queries and joins for multiple runs

The query (below) that's running is taking ~0.6 sec which normally would be fine but it gets run almost 1300 times for different custids. How can I make it run faster? Setup: Mac OS X 10.7.5 MySQL ...
0
votes
1answer
41 views

Get people with similar interests

I have a table named SKILLS with two columns: id and skills. id is the id of the user, skill is a particular skill. I need to find other users who have similar interests, like who has the most ...
1
vote
2answers
68 views

join for between multiple tables without common fields

table E : id, title, datetime table L : id, place, xpos, ypos table M : id, title, datetime, contact, agenda table ER : parentid, childid, parenttype, child type data in ER : e.id, l.id, e, l -> E ...
1
vote
1answer
68 views

Performance issues when extracting scatter plot data

First off: Sorry for the title, couldn't think of anything better. Feel free to change it though. Ok, assume I have the following table: ...
0
votes
1answer
37 views

Problem JOINing three tables

I want to show all the categories and the items inside that each category which should store in items_this_category. Note, I have created an associative table item_categories because one item can be ...
0
votes
0answers
51 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 ...
0
votes
1answer
20 views

whether to merge partitioned table with only partially overlapping columns

I opted for MonetDB for my static data, but this might be less of a MonetDB-specific question about features than about RDBMS (or just SQL) in general. I have data with a few "cross-sectional" ...
3
votes
1answer
361 views

Postgresql subquery speed much slower than individual queries

In Postgres 9.1. I am attempting to do a single query to get the timezone of a given postal code. I have data loaded in my database, and I am using PostGIS to store the coordinates of each postal ...
1
vote
1answer
41 views

Joining three tables

I have a question Find the titles of all movies not reviewed by Chris Jackson Where the relations are 3 movie(mID, title, year, director), reviewer(rID, name), rating(rID, mID, stars, ratingDate) ...