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

learn more… | top users | synonyms

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 ...
0
votes
1answer
32 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
62 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
90 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
61 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
136 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 ...
0
votes
0answers
33 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
56 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
48 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
98 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
50 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
167 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
80 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
42 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
1answer
24 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
719 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
50 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) ...
5
votes
3answers
4k views

How to SELECT multiple COUNT(*) columns with each GROUP BY having a different WHERE clause?

I have the following schema: CREATE TABLE Person ( PersonId int PRIMARY KEY ) CREATE TABLE Action ( ActionId int PRIMARY KEY, PersonId int NOT NULL FOREIGN KEY REFERENCES Person(PersonId), ...
0
votes
0answers
51 views

Very slow inner join

newby on mysql so help needed I created a my sql query, on their own they run fast but when I enter the inner join it runs forever. i did index theres id tables but no result in speed. here is my ...
0
votes
1answer
115 views

Get the value of one attribute from the reference key of another table?

I have two tables named as ps_order_detail and ps_product_lang in MySQL. I want to get the product name from one table whose reference key is in another table. I have two tables. First the ...
1
vote
1answer
53 views

How can I make multiple joins on the same table?

I have a table (signal) which contains references to two other tables (ee and eeav). I want to perform an SQL Query which combines the data from ee and eeav tables. The situation is as follows: The ...
0
votes
1answer
36 views

Linking Two database to execute join query

I have a remote odbc connexion and an MySQL database in my localhost i ask if there is a way to execute a join query ( linking the two database ) ? thanks.
0
votes
0answers
146 views

MySQL: Is it possible to join a virtual table with itself?

I am not sure if 'virtual table' is the correct phrase. I am instantiating a table X like so: SELECT * FROM ([some select subquery]) X and I would like to cross join that table with itself several ...
4
votes
1answer
163 views

MySQL - GROUP_CONCAT returns duplicate data, can't use DISTINCT

I have a normalized database and I'm trying to return data from multiple tables using JOINs and GROUP_CONCAT. Problem: Rows are being duplicated with GROUP_CONCAT. I can't use DISTINCT because some ...
1
vote
2answers
231 views

How to delete rows from a table when we're joining and return data?

I have two queries: The first one is as below: SELECT * FROM batch bp INNER JOIN sender_log sl ON sl.id=bp.log_id AND bp.protocol='someprotocol' The second query is used to delete rows: ...
2
votes
2answers
194 views

Limiting the Number of Records Joined

How can I perform a query that contains a (one-to-many) join between two tables, which will only join up to a pre-defined number of joined records? So, lets say I have two tables, one for Matches, ...
0
votes
2answers
37 views

Aggregating results of two database views in one database view

I have a view with name of UserInfo like this: And another view with name of UserRoleInfo like this: I want to aggregate all the records of the two views for one user name like H.F into ...
0
votes
1answer
196 views

Efficient way to write query for multiple table which depends on column value of a table in mysql?

In the given schema, there is a table A having column A1,A2,A3,A4 There are three more tables i.e. B,C,D If the value in A4 is b, i have to lookup table B. If the value in A4 is c, i have to lookup ...
0
votes
1answer
47 views

Merge left outer join column

I have a query that use left outer join. something like that : select issues.id, journal.notes from issues left outer join on (issues.id = journal.issue_id and journal.notes != '') So far it ...
0
votes
1answer
107 views

MySQL query using left join on two inline views not returning rows without values in second view

For some reason when I execute the code below I'm only getting results back that have sku in both views. I thought this should give me everything in the first view and add the content from the second ...
1
vote
0answers
123 views

MySQL join query indexes

After recently discovering the MySQL feature to log queries that aren't using indexes, I've been chasing these down in a large app, looking for places to improve performance. There is at least one ...
0
votes
1answer
46 views

What is the correct way of filtering a property by multiple features?

Having the following tables: Property Feature PropertyHasFeature (property_id, feature_id) What is the right way to search properties by specifying multiple features? I want to list all ...
0
votes
1answer
64 views

Low-level relational operators from SQL

I have been asked to outline the sequence of commands using low-level operators (join/project/select) that are used in the following SQL statement; SELECT d.name, k.city FROM DOG d, KENNEL k WHERE ...
1
vote
0answers
64 views

SELECT, PROJECT, CARTESIAN PRODUCT and JOIN

I have the following tables; DOG +----------------------------------------------+ | Name Type Age Kennel_name Owner_name | +----------------------------------------------+ ...
0
votes
1answer
47 views

limiting rows in joins

So I have this problem with one of my queries. The query is meant to get data from table patients and join the table patient_nurse, then table nurses to it. The patients table is as follows id | ...
0
votes
1answer
113 views

MySQL: LEFT OUTER JOIN within reason

What I'm attempting to do is LEFT OUTER JOIN a table, but I only want the last inserted record of the joined table to affect the results. Currently each table row within the joined table seems to be ...
0
votes
1answer
84 views

mysql directory grow to 246G after one query, which failed due to table is full

I was trying to run the following statement with the hope to create a join of two existing tables. create table CRS_PAIR select concat_ws(',', a.TESTING_ID, b.TRAINING_ID, a.TESTING_C) as k, ...
1
vote
1answer
105 views

Innodb buffer pool and multiple joins

assume we have 1 Billion records and want to store them in One or Three tables by post_type. post_id -> primary key post_type -> ( page, group, user ) user_id -> index date -> ...
1
vote
1answer
888 views

MySQL: NOT EXISTS vs LEFT OUTER JOIN…IS NULL

Is NOT EXISTS or LEFT OUTER JOIN...IS NULL more efficient for finding records in one table that aren't in another? Specifically, I'm trying to figure out which of the two queries below is better: ...
3
votes
3answers
494 views

How to SUM previous sum, e.g N = (N-1) + (N-2) + … + 1?

I have a table name "TABLE_A (id integer, no integer)" . I want to sum "no" with group by "id" and current "sum of no" = previous "sum of no" Here is my code : 1/ Create table & insert data: ...
1
vote
1answer
146 views

Self-join unique pairs

I have a table (players) containing a list of players, I would like to pair up these players in a unique way, so that every player's opponent is unique every round (ie. every time the SELECT query is ...
6
votes
3answers
949 views

Can someone explain why left joining two views in mysql is so slow?

Here is a question I asked yesterday - http://stackoverflow.com/questions/22180727/left-joining-two-views-is-slow. I got a good answer that helped me but I don't understand why the LEFT JOIN is so ...
2
votes
1answer
47 views

Update only the oldest entry in a table with foreign key criteria

I'm working on a forum website. What I am trying to do is updating the thread Title and Content (first post), which resides in different tables - "Threads" and "Posts". "Threads" "Posts" I'm ...
0
votes
1answer
44 views

Nesting inner to sum inner query's result

Suppose I have a query select a,b from xyz The result would be: a        b m1     10 m2     20 m3     30 Can I somehow ...
0
votes
2answers
163 views

25M x 25M inner join (postgresql) performance

I have a one time need to do an inner join of 25M rows on 25M rows. The box is a Alienware area 51, 4 cores 25GB memory and SATA drive (non system disk). So far it has taken 22 hours. I did btree ...
0
votes
0answers
86 views

Selecting posts, user details and votes from 3 SQL tables

I have a user table with id, username, email etc.. post table with id, uid (user id), content etc and vote table with id, uid, pid (post id), vote (ENUM : '1' and '-1'). In the vote column of Vote ...
0
votes
2answers
116 views

Select * on inner join with common columns

I have 3 tables (mysql db) on which I want to do an inner join. The problem is that these tables have common columns. I know I could use aliases to differentiate them (select A.x as Ax, B.x as Bx, C.c ...
0
votes
1answer
71 views

Can't get rid of “temporary; Using filesort”

I can't get rid-off "temporary; Using filesort" when ordering columns from the second table explain SELECT * FROM post JOIN post_plus ON post_plus.news_id = post.id ...
0
votes
0answers
273 views

USING to join multiple tables with same field

I have 4 tables, each with a common field QuestionnaireId. I am wondering if there is any difference in output or performance difference between using USING on the field in each join or by specifying ...
6
votes
2answers
249 views

Should I nest dependent outer joins in SQL Server?

I have heard mixed information on this and am hoping for a canonical or expert opinion. If I have multiple LEFT OUTER JOINs , each dependent on the last, is it better to nest them? For a contrived ...