Questions tagged [join]

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

Filter by
Sorted by
Tagged with
0
votes
2answers
16 views

I am not able to get the result when joining two tables with where clause with OR condition

I have two tables projects and project_user_cross. I want to get the projects if the user id exists in either of the two tables Here is the projects table Id | project_manager_id | name | ...
0
votes
0answers
19 views

Join a dynamic amount of tables with the same naming rule in MySQL [on hold]

I am creating an application where users go and register a server, and each server registered gets a table containing information about it, like their users. All tables from those servers are named ...
0
votes
2answers
26 views

Group results from two different tables on same hours

I have two simple tables: indoor id | timestamp | temp | humi outdoor id | timestamp | temp And two selects which give me time and average temperature grouped by same hour for the last 24 hours: ...
0
votes
0answers
15 views

select rows and calculate number of rows based on parent column value

I have a total of three tables with a many-to-many relationship. 1.) users id | email ----------- 1 | usera@example.com 2 | userb@example.com 3 | userc@example.com 2.) accounts id | userId | ...
0
votes
0answers
32 views

Optimizing range join (x between a and b) in MariaDB

My query has a join using a BETWEEN clause and it causes a full table scan to occur if I understand the EXPLAIN output correctly. Query EXPLAIN SELECT g.id, g.name, c.id AS child_id, c.date ...
0
votes
1answer
30 views

How to access Multiple DB2 Relational Databases In A Single Query

I need a solution for below: There are Two DB2 instances with two authorization ID on the same server and can be access using same user User1. Example: To access Inst1DB, I use the following ...
1
vote
2answers
31 views

Select the students that are enrolled in both the courses [closed]

This is the table definition. I want to select the students that are enrolled in courses (1 and 3). From this dataset the expected output is 'B'.
2
votes
1answer
51 views

Improving MySQL query performance for a large database

have a simple query that do a join between two table. The main table has a date field that my query filter by a range of dates. But for a month period my main table has 37412 datas and for this values ...
0
votes
0answers
25 views

Show last line with left outer join

I have a problem with my syntax, I need to show the last record using inner joins, I used this example (https://stackoverflow.com/questions/2111384/sql-join-selecting-the-last-records-in-a-one-to-many-...
0
votes
1answer
23 views

Joining tables with all rows for each unique column value

I have 2 tables as follows: | A | +----------+ | ty_date | | ly_date | +----------+ and | B | +-------------+ | locationid | | ty_date | ...
0
votes
0answers
39 views

Is there a more efficient way to join a big table that this?

Consider the following. On an e-commerce site I have a sales_order table and a subscription table. The sales_order table has well over 1 million rows and about 25+ columns. The columns I'm ...
3
votes
2answers
119 views

LEFT JOIN confusion

I am having issues with this simple scenario. I'm probably missing some basic concept... I have 2 tables, the first of "things" and the second of orders. I've created a sqlfiddle: http://sqlfiddle....
0
votes
1answer
25 views

REPLACE(orig,match,replacement) multiple matches in one query

I'm trying to categorize Magic The Gathering cards. Here is a simplified version of what I'm doing/trying. Is it reasonable/doable to get my desired output from mysql? If so, how? Or am I going to ...
-1
votes
1answer
62 views

How to create a VIEW when Table1 is having relation to Table2 on several of it's fields?

My situation (greatly simplified) is as follows: Table1 ID Name Ver1 Ver2 Ver3 Ver4 Table2 ID Name ... This table holds the versions The columns Ver1 - Ver4 are foreign keys and are IDs of ...
0
votes
0answers
19 views

Order of operations in LATERAL join?

Consider the following query to find the (spatial) nearest neighbor; the PostGIS specific components here should not be of importance for my question, or so I believe: SELECT a.id, c.geom FROM ...
1
vote
1answer
36 views

How to increase the value of a column in a table by a constant by consulting another table?

I have prepared the database of the college given as create table depts( deptcode char(3) primary key, deptname char(70) not null); create table students( rollno number(2) primary key, name char(50),...
0
votes
1answer
19 views

What exactly happens when optimizer_prune_level = 1?

This documentation link states that optimizer_prune_level Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. Can ...
2
votes
3answers
56 views

How to use left join with a subquery

This or this questions do not answer my doubt. I have the following set of tables in a database called order_mgmt_sys (Please note that database is static and I can't alter the tables) mysql> ...
0
votes
1answer
17 views

How to rank an aggregation in each newly formed group?

The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL. Let's say I have a simplified ERD that looks as follows: This ...
0
votes
1answer
33 views

Schema and Query design for table with multiple languages [closed]

I've been researching on how to create a schema for a database that contains multiple languages. So I decided to separate the translations on a different table with a column as the language and ...
0
votes
1answer
25 views

How to insert values to one table from the table stored in another database in postgresql?

Here is the structure of table: `* Table 1 of database A contains mobile number and userId and both field cannot be blank I want to fetch data from table 1 of database A and insert that with some ...
0
votes
2answers
36 views

Joining 2 tables based on Dynamic criteria

Hoping you can help me. I have 2 Tables (Table PS & Table A). What i am trying is the following ; Depending on the ACCOUNT_NUMBER & Id_Type of table A, search the Table PS on the following ...
0
votes
1answer
31 views

How to Join two tables with the join being regexp_replace [closed]

Need your help, I have two tables “temp” and “temp 1” The temp table I am deriving a column called numbers. I am wondering how do you join table temp1 onto temp and joint on SELECT regexp_replace(...
0
votes
0answers
29 views

MySQL Count rows of multiple tables

CREATE OR REPLACE VIEW organizedevent AS SELECT o.organizerID AS "Organizer ID", o.organizerName AS "Name", o.contact AS "Contact", COUNT(e.eventID) AS "...
2
votes
1answer
42 views

Does the ANSI SQL Standard allow filters or parameters in JOIN conditions?

Does the ANSI Standard find the following SQL sinppet syntactically correct? I am interested specifically in the last line: SELECT name, dept_name FROM employee JOIN department ON employee....
0
votes
0answers
20 views

MySQL query that works but extremely slow

I have a developer who has created a query joining 3 tables in a method that I have never seen before. This query is working although extremely slow. I am trying to understand how this query is ...
1
vote
2answers
45 views

MySQL 5.6 users index instead of range type select on a big table and simple query

We have recently migrated from MySQL 5.5 to 5.6 (5.6.44-86.0-log Percona Server to be precise) and we have a huge problem. Seemingly simple queries run for minutes instead of milliseconds on a table ...
0
votes
2answers
54 views

How to display the dates without DUPLICATE and ORDER by MAX DATE

I just want to ask that how can I display the null values along with the MAX date. SELECT MEMB.MEMB_N, MEMB.LAST_M, PrintDate FROM MEMB INNER JOIN tblPrint ON MEMB.MEMB_N = tblPrint....
0
votes
0answers
30 views

Two left joins then two on statements [duplicate]

I stumbled upon a left join function that is throwing me off... I was trying to eliminate a subquery or using another temp table. Both of the following queries produce the exact same values. SELECT * ...
0
votes
0answers
24 views

How to get the data using where_in (1,3 ) table in mysql?

We have table Table 1 is User Table 1: id | name | sub_code ----------------------------- 1 | name1 | 1 2 | name2 | 1,3 3 | name3 | 2,4 Table 2 is ...
-1
votes
1answer
29 views

Help with joining two tables (full join)

I have two tables. Table 1 : 'DQ' is the golden source of information Table 2 : 'PS' is whats currently in the DB. What I would like to do is compare AMOUNT from the 'DQ' table vs VALIDATED in the '...
0
votes
1answer
26 views

Using WHERE / AND in Joins

Can someone elaborate on how the following two queries would provide different results? That is, how the query is processed differently using WHERE or AND. select orders.*, accounts.* from ...
-4
votes
2answers
51 views

Joining two tables together based on common identifier [closed]

I have two tables. Table 1 : 'DQ' is the golden source of information, Table 2 : 'PS' is whats currently in the DB. What I would like to do is compare the DQ table to whats inthe PS Table and ...
0
votes
1answer
23 views

How to aggregate results from SELECT JOIN

I have two tables: **user Table ** id username --- ----------- 100 greg 101 john 102 patrick 103 bill **user_subscription_plan Table ** id user_id amount --- --------- ------- 1 ...
0
votes
0answers
35 views

how to reduce time of a query which have 4 joins

I am new to CakePHP and I am working on CakePHP 3.8 version. There is a join query which takes almost 10 -12 seconds to fetch data. I don't know how to optimize this query. this query is written in ...
0
votes
2answers
67 views

How to sort rows after union

I have two tables that I want to combine. I need to be able to show all DelDates that had changes from the original (record 1). Record 1 as the first Record and Record 0 (table 2) always being the ...
0
votes
0answers
19 views

Adding rows that share a foreign key ID from the same table *in a new row*

My goal is as follows (simplified): find the newest record from table events use a FK events_meta_id to find all records from that same table (events) that have the same value in that field, if they ...
0
votes
2answers
33 views

SQL Server: Join all tables from a table listing their names [duplicate]

I have a table that contains a list of metrics and their IDs in the format: L6Metrics | ID | Name | -------------------------- | 734 | L6A_Velocity | | 736 | L6B_Velocity | | 738 ...
0
votes
1answer
18 views

three tables relation order is correct but values are false

i have those tables table1 | id | name | | 1 | axe | | 2 | bow | | 3 | car | | 4 | dart | table2 table3 | t1_id | ...
0
votes
1answer
36 views

Problem with How to join with subquery

I'm trying to create a report in Postgresql that shows some columns from some tables with join. But one of them is subquery that to show a customer with the location. My Problem is, I always get ...
7
votes
2answers
300 views

Nested-Loop-Join: How many comparisons and how many pages-accesses?

Say you have two relations R and S , where R has 1000 tuples and 100 page-accesses, and S has 50 tuples and 25 page-accesses. Assuming R is the outer relation, then how many tuple-comparisons and ...
0
votes
1answer
47 views

How to left join on groups of data

I would like to know how to left join columns for each group of data that is return in a set of data. The template needs to fill in the blanks for each group where there are missing records. Groups 1 ...
0
votes
1answer
37 views

Database design for Scavenger Hunt Game

I'm designing a section of my database which links User, Team and Hunt. I would like users to be members of a team but the team can play multiple hunts (so can be used multiple times). The first idea ...
0
votes
1answer
40 views

Why last table don't using index for joining?

By the help of others members i get an Query idea for my previous problem. But after writing this, the EXPLAIN showing that my last table do't using index for join but the joining ROW is PRIMARY KEY, ...
3
votes
1answer
36 views

Fill missing dates within groups

I have the following table with values for different stations from 2014-01-01 to 2014-01-04. The data has some date gaps that I want to fill leaving the value as NULL, and assigning the missing date ...
0
votes
1answer
37 views

How to use left join with Where clause

I was thinking that I have a very good understanding about my sql and joins but i miserably failed to make this query. I have to make employee leave report, therefore I want all employee list no ...
0
votes
2answers
13 views

Select value based on a test

I have two tables user_session_logs and blacklisted_ips. The first one for logging access logs and the second one is for blacklisted ips, and both tables have a column named ip_address. I need to ...
0
votes
1answer
34 views

What is the proper way to relate many to many?

I am building out a shipping app and database. Each shipment has three parties to it, shipper, receiver, and notification party and they all have the same attributes. I want to keep the attributes in ...
0
votes
1answer
25 views

Why this join provide multiple value?

This is my Query: SELECT GROUP_CONCAT(c.categoryName) AS categories FROM post p INNER JOIN join_post_cat jpc USING (postId) INNER JOIN categories c USING (categoryId) WHERE p.postId ...
-1
votes
2answers
25 views

Query to collect the recent unique TimeSampled rows using inner joins for multiple servers in MS SQL [duplicate]

Dears, I have written below inner join query to get the data from my database tables. But output of this data is coming up with almost 3k lines. Output is below. I need data in only unique recent "...