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

learn more… | top users | synonyms

0
votes
1answer
162 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
37 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
61 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
55 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
111 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
59 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
53 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
345 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 ...
0
votes
0answers
81 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
44 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
25 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" ...
1
vote
1answer
82 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: ...
1
vote
1answer
52 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
5k 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
53 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 ...
1
vote
1answer
55 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
39 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
161 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 ...
1
vote
1answer
166 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 ...
4
votes
1answer
196 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
292 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
206 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 ...
1
vote
2answers
88 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
53 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
161 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 ...
0
votes
1answer
69 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
66 views

SELECT, PROJECT, CARTESIAN PRODUCT and JOIN

I have the following tables; DOG +----------------------------------------------+ | Name Type Age Kennel_name Owner_name | +----------------------------------------------+ ...
0
votes
1answer
49 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
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
135 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
93 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
112 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 -> ...
3
votes
1answer
908 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
1k 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
551 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
165 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 ...
2
votes
1answer
48 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
63 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 ...
6
votes
3answers
1k 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 ...
0
votes
2answers
207 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
100 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
157 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
81 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
336 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 ...
0
votes
1answer
70 views

Ask about Inner Join & Union

i want to ask about my script this is doesn't work. i need to help ! SELECT A.*,B.mat_nama,C.mats_nama FROM pengeluaran AS A INNER JOIN(material AS B) ON(A.mat_id = B.mat_id) INNER JOIN(mat_satuan ...
0
votes
1answer
133 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 ...
6
votes
2answers
269 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 ...
0
votes
0answers
46 views

MySQL - query join returning zero recs from table with data

I've provided a file that has the table structure and some sample data that I'm working with. I don't understand why I'm getting nulls from a joined table, where there is data. Here's my query ...
0
votes
0answers
64 views

Equivalent of old style join with new style

Can someone please help me write the following join expression in new style: Select tb1.col1,tb2.col2 from table1 tb1, table2 tb2 where tb1.col1>=tb2.col2 I know that the equivalent of ...