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

learn more… | top users | synonyms

0
votes
3answers
20 views

Table concatenation

I want to have a dynamic query that I can concatenate my table based on current month. I want to have a variable to my FROM and JOIN statements. DECLARE @month_number AS VARCHAR(2); DECLARE @...
0
votes
1answer
16 views

Help needed in creating a SQL statement containing four joins

Please help. Given a studentID and a TagNumber i want to get the UserID related to that TagNumber. The difficult condition is that TagNumbers can be duplicate within a Users table. So we can pinpoint ...
0
votes
2answers
30 views

How to join to the same table multiple times?

I have two tables, "hierarchy_table" and "name_table". The hierarchy table contains an object which has multiple parents and children. Each parent and child is referenced by id. | object_id | ...
0
votes
1answer
124 views

Count for many-to-many relationship

I have the following tables: surveys properties owners_properties owners ---+------------ ---------- ------------+--------- ------ id | property_id id ...
1
vote
1answer
28 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 ...
0
votes
2answers
244 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 ...
0
votes
1answer
29 views

Mysql Inner join performance

I have performance problem! I searched for Inner Join, tried optimize table but still the same. I have Users, Entries, Tags and tag_user tables. This code works perfectly : select * from `entries` ...
1
vote
1answer
62 views

Joining Tables in the FROM vs. the WHERE clause [duplicate]

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
160 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
93 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
25 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
4answers
68 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
37 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
30 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
123 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
120 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
337 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
67 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
47 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
23 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
348 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
60 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
17 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
769 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
42 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
500 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
53 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
53 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
26 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
34 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 ...