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

learn more… | top users | synonyms

1
vote
1answer
45 views

Joining Tables in the FROM vs. the WHERE clause

First time caller here. I have a question regarding best practices when joining tables. For example, both of the following queries return the same results: SELECT i.id, p.first_name, p.last_name ...
1
vote
1answer
158 views

MySQL very long-running queries

I have a quite large db (800+Mb dump file) that I imported to my local server. It's a Wordpress db from witch I need to extract certain posts. There are around 160,000 posts inside. Currently I'm ...
-1
votes
0answers
10 views

INNER JOIN WHERE user id

I have 2 tables Table name: "as_users" Col 1: user_id Col 2: uid Col 3: email Col 4: username And Table name: "user_images" Col 1: uid Col 2: username Col 3: image The following code shows ...
2
votes
2answers
89 views

Is 3 minutes to join a 50 million table too much?

I apologize if this is the wrong place to ask, but I really need some opinion on whether or not my query is good or not. Basically, I need to get a count of old and invalid entries per group, which ...
1
vote
1answer
132 views

placing conditions in a join query join on vs where

Is there and advantage to one of the following options over the other or are they the same ? select * from T1 Join T2 on T2.id=T1.t2_id and T2.field=value; vs. select * from T1 Join T2 ...
0
votes
1answer
24 views

Trying to compare several similiar lists on one table to produce a concatenated result

So imagine that four friends are planning on moving into a house together. They put together a list of stuff that they all own. Obviously, there is some overlap but there is also unique things among ...
1
vote
0answers
16 views

Join Tables of Varying Criteria in Order of Priority

I am trying to join two tables based on varying criteria in order of priority. The situation I have inherited a clinical database which has inherently bad data quality from years of poor input and ...
1
vote
4answers
65 views

Build JSON object from one-to-many relationship data in a single query?

I have a PostgreSQL 9.5.3 DB with tables like this: container id: uuid (pk) ... other data thing id: uuid (pk) ... other data container_thing container_id: uuid (fk) ...
0
votes
1answer
35 views

Oracle sql combine several rows of the same table into one row

I have a table where for each given ID, three rows are generated as shown. The first row is the header row: +-------+------+-------+------+-------+----+----+----+ | ID | GRP | ITDESC| DEN | NUM ...
0
votes
0answers
29 views

Subquery or Inner Join?

I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions. SELECT t.id, t.date, t.amount, t.description FROM ...
-1
votes
0answers
19 views

some pattern that I can inner join from only one of many kind of tables

I have a very long data form which needs to be saved. additionally, my form data can be separated into 4 kind of form. if I get common columns between 4 types I'll have a table with 48 columns ,...
0
votes
0answers
42 views

Find Intermediate Bus Stop and Source and Destination Bus

I have a table which consists of 4 Columns: PKID, Bus_ID, Stop_Position and Stop_ID. Data: BUS_ROUTE_TABLE PKID BUS_ID STOP_POSITION STOP_ID ------------------------------- 1 1 1 ...
1
vote
2answers
122 views

Select highest id of each type in mysql

I am creating a database that keeps track of backups on our various servers. The query that I am using actually seems to work for the most part. The only issue is that it isn't getting the highest ids....
3
votes
1answer
119 views

How to get max value of a column from one table with its matching record in second table in PostgreSQL?

I have two tables as follows: create table two(grade_id int, edu varchar(20),sortby int); alter table two add constraint pk_one primary key(grade_id); create table one(id int, name varchar(20),...
0
votes
1answer
336 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 ...
1
vote
1answer
14 views

Getting the syntax right for MySQL select with inner join

Using MySQL version 5.1.73 and phpmyadmin 4.0.10.16. I am unable to get this past the syntax checker: select * from courses as co , course_type as ct left join course_leaders as cl on cl....
0
votes
0answers
17 views

Do I need to index JOINED columns even if they only appear in SELECT?

This is a reformatting of the question asked here: MySQL JOIN is fast, but selecting column is slow Do I need to add an index to EVERY column in a JOINED table if that column will appear in the ...
0
votes
0answers
25 views

MySQL JOIN is fast, but selecting column is slow

My logs table has half a million rows. Each row has an indexed log_level_id column pointing to table log_levels (with only 5 rows). On log_levels, id is an auto-increment primary key and label has ...
0
votes
1answer
21 views

Mapping 5 columns to 1 (ID, dimension) table without 5 joins?

I'm using survey monkey's API to dump survey data into a SQL data warehouse. So I'm dumping response data into a table 'resonses' thusly: survey_id, Q1, Q2, Q3, Q4, Q5 survey3944, 848948344, ...
0
votes
2answers
66 views

MySQL: LEFT JOIN not working as expected

I have 2 tables: Employees, attendanceIn When I query using LEFT JOIN SELECT employees.eno,employees.name,employees.dept,attendanceIn.attIn FROM `employees` LEFT JOIN attendanceIn ON employees....
0
votes
2answers
40 views

Left Join 3 tables into ONE in Mysql

I have 3 tables: Employees, attendanceIn table, attendanceOut And My query is SELECT employees.eno,employees.ename,employees.dept,attendanceIn.attIn AS attIn, attendanceOut.attOutAS attout ...
1
vote
2answers
46 views

How to find out if rows are refenced from other tables?

I have a table named contacts (primary key id) which is referenced by a number of other tables via foreign keys. When a list of contacts is displayed in the application, I get their primary keys in an ...
1
vote
0answers
32 views

How similar is using a secondary index in InnoDB to using a second table joining 'into' the PK?

Since InnoDB secondary indexes are separate B-trees with the key as the sort order and the PK as the 'value' stored, I assumed that reading into the main table by the secondary index was a lot like ...
0
votes
0answers
22 views

Traditional SELECT with Multiple Joins or SELECT JSONB then multiple SELECT

I am writing a simple recipe book app based on node.js with sequilize as ORM and Postgresql 9.4 as DB. Recipe has multiple ingredients that can be measured with different units. I store recipe steps ...
1
vote
1answer
19 views

SQL Update column with another table column

I'm using Postgres 9.4, and already seen others asking the same (simple) question, but theirs answers do not solved my problem and I can't see why. Suppose that I have these table A with common data ...
4
votes
1answer
347 views

Why does my execution plan (sometimes) include a left join?

I found a similar question and am aware that in order for a query of the form SELECT COUNT(1) FROM foo f LEFT OUTER JOIN bar b ON f.Value = b.Value AND f.Value = b.Value2 to perform without ...
1
vote
2answers
51 views

Full Join Query

I am doing a full join query from two tables. SELECT i.idinasistencia, i.fecha, i.valor, i.justificado, h.id, p.nombres, p.apellido_paterno, p.apellido_materno, p.rut_persona FROM historialalumno ...
3
votes
1answer
58 views

How to LEFT JOIN a date table onto each customer (GROUP BY) in a customer purchase table? Can a JOIN be done for dates after initial purchase?

I am stuck on implementing a left join on dates for each individual customer. I have a table which stores customer purchases: SELECT purchase_date, customer FROM purchases I would like to create a ...
0
votes
2answers
114 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` ( `...
0
votes
0answers
16 views

sum price of childs in other table mysql

i have two table one store data child and parent hierarchy and other paths and descendant +----------+------------+-----------+ | userid | parent | price | +----------+------------+--------...
0
votes
0answers
38 views

how to get the data from table

Before this query is run an activity name (e.g. ‘Art’) should be assigned to a variable. The query should then output a list of the children registered for the specified activity. The output should be ...
1
vote
2answers
83 views

How to use a SELECT query inside an ON CLAUSE in a LEFT OUTER JOIN in MySQL

I need to retrieve data from several tables. For now I am getting maximum of them from SELECT disc.id, users.user_login as 'last_active_user', disc.discussion_sub, list....
2
votes
1answer
764 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
1answer
18 views

get minimum from result with group by mysql

i have table it store hierarchy data in mysql this table store stable relation but if each user les than 1000 buy removed and user User a lower level replace thia is my code and work fine, after qroup ...
0
votes
1answer
43 views

Really slow join, limit and order_by query

I've got these two tables: books (about 2 million records in total) Containing a unique ID (isbn) aswell as an integer value representing popularity (index, but not unique). category_pointers (also ...
6
votes
8answers
9k views

How to find the nth highest salary in SQL?

I have found a query to find the nth highest salary from Employee table , but i don't understand the logic of (N-1)? EmpID Salary 1 90000 2 80000 3 54000 4 ...
1
vote
0answers
41 views

get max item in sub query join mysql

i have a table it contain buy of each user , other table contain a star ranking According to buys all user, my scenario very sample i create it and show in sqlfiddle . example if in table buy have ...
0
votes
1answer
494 views

SQL80001 - incorrect syntax near ')'

In the below code (that I've inherited), I'm getting the message "SQL80001 - incorrect syntax near ')'", the error being on the third line from the bottom with the + sign. I've looked at THIS SO ...
1
vote
1answer
52 views

How to deal with a chain of several linked 1:n:n*m:n*m*k “join” explosion?

I have a domain model, where I have a "manager" which has 1:n relation with another resource (A). A has 1:n with B, and B has 1:n with C (it makes sense as a relation). However I'll need a lot of ...
2
votes
1answer
62 views

How to UPDATE by COUNT from JOINed tables

I have a schema as CREATE TABLE companies ( company_id int(11) NOT NULL AUTO_INCREMENT, company varchar(255), projects int(11), PRIMARY KEY(company_id) ) ENGINE=InnoDB CREATE TABLE employees ( ...
1
vote
1answer
52 views

1054 - Unknown column in subquery

i have 2 table one store data of relation user other buy user path table +-------------+---------------+-------------+ | ancestor_id | descendant_id | lenght | +-------------+---------------+--...
0
votes
0answers
25 views

Joining tables in Eloquent ORM based on condition to disallow duplicates

I have two tables I am looking to join data. This is a friendship table based on a recommendation I've seen on here. It's an AB BA relationship between friends. I want to join this with a profile ...
1
vote
1answer
32 views

How to join multiple results into one row with dynamic columns? [duplicate]

Foreword - I don't know how to formally describe the situation in a single question so the below is as verbose as I can make it... Taking wordpress as an example, there's the "wp_users" table which ...
7
votes
1answer
984 views

Using Two LEFT JOIN or use AND with single LEFT JOIN?

Using two LEFT JOINs i.e. SELECT <some columns> FROM Table1 AS t1 LEFT JOIN Table2 AS t2 ON <condition1> LEFT JOIN Table2 AS t3 ON <condition2> is this same as using AND in ...
0
votes
1answer
28 views

Returning data based on if another column in another table is NULL

I'm trying to generate a list of data which is on completed status but has not been billed. I am able to produce a list of all completed orders however i am unable to remove the ones which have been ...
2
votes
3answers
198 views

Why does a query with a LEFT JOIN run for a long time?

I have this query, it works fine and takes over 2.5021 secs: SELECT c.id AS 'id', count(c.id) as orders, o.created_at as last_order FROM customer c, order_2016 o WHERE o.customer_relacional_id = c....
0
votes
1answer
61 views

How do I save result of a join to another table?

In PostgreSQL I can save result if SELECT to another table with SELECT INTO: SELECT id INTO new_record_ids FROM new_table EXCEPT SELECT id FROM old_table ORDER BY id; However this doesn't ...
0
votes
2answers
233 views

Selecting Only One Record with Most Recent Date Using Table Join

I have a list of 463 CARRIER_CD. I am attempting to retrieve the most current BILL_DT for each of the 463 CARRIER_CD. I have uploaded the 463 unique CARRIER_CD to a database named edw_sbx_bt the ...
1
vote
1answer
34 views

Multiple subqueries in columns with joins to return multiple rows

I have a table which stores id(primary key) of users and not their names (Names are stored in another master table) and also the the department id of user and not the name of the departments and there ...
1
vote
1answer
72 views

SQL Server query problem when selecting data from child table based on column in parent table

I am using MS SQL Server 2008 using Management Studio. I am running a query(below) to extract dates and value based on the 'ticker' column from the parent table, the attribute column (child) , and ...