Questions tagged [join]

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
21 views

Improving performance with a common low-cardinality field

I have a database with multiple tables. Every table has a field named sourcename, which indicates basically which customer the data came from. There are only about ten possible values for this field ...
user avatar
  • 101
0 votes
0 answers
35 views

How to flatten/pivot "inner join" into a single row in a certain order?

Let's say I have 2 tables in MySql/MariaDb: competisions and participants. There can be multiple participants in one competisions. I want to a) do an inner join b) sort the participants by score c) ...
user avatar
  • 27
0 votes
2 answers
18 views

PostgreSQL JOIN SELECT null if record on one of both table not exist

I am having 2 tables with data as below: tableA ======== 1 2 3 4 5 tableB ======= 1 3 5 6 8 9 With this query: select coalesce(tA.id, null) as id_A, coalesce(tB.id, null) as id_B from ...
user avatar
0 votes
0 answers
14 views

speeding up JOIN by LIKE or REGEXP

I have two tables of CREATE TABLE titles ( title_id int(11) unsigned NOT NULL AUTO_INCREMENT, title varchar(255), INDEX(title), PRIMARY KEY(title_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE ...
user avatar
  • 4,261
1 vote
0 answers
16 views

Best way to create a View which includes counts for multiple one-many relationships?

Consider three entities - Forum, Thread & Topic, with the following relationships Forum has many Threads Forum has many Topics I want to create a View forum_details that displays all columns of ...
user avatar
-1 votes
1 answer
39 views

SQL Multiple CASE conditions with SUM and count Functions along with JOIN Condition

Kindly help me with the right syntax of mine, really appreciate it, if you could just edit my existing syntax into correct one without alternate options. thanks in advance Table schema CREATE TABLE ...
user avatar
-1 votes
0 answers
23 views

How to join all members of GROUP BY groups?

Suppose I have a table (myTable) of datetime indexed objects: dttm | price ---------------------------------- '2022-7-11 T00:00' | 10 '2022-7-11 T01:00' | 11 ... ...
user avatar
  • 99
-2 votes
0 answers
13 views

postgresql : how to join multiple relational tables and select row based on data in other tables [closed]

This is the description of my problem on this page trying to get data like this with sqlalchemy didnt work for me : tod_session_req = await session.execute( select( ...
user avatar
0 votes
1 answer
23 views

MSSQL - SELECT on data from JOIN reference

SQL Server being used to run this I'm trying to pull information that could be variable.. Could you please check it and find the error? SELECT id = t.id name = c.name fav_flavor = ...
user avatar
  • 113
0 votes
1 answer
42 views

How to display last record per parameter?

I have a query in SQL server I use to get basic stats from two tables of sites and records associated with sites. I'm having trouble figuring out how to ALSO create a column that displays the last ...
user avatar
0 votes
0 answers
22 views

T-SQL, Fast self join on the next available date where dates skip weekends and multiple rows can have the same date

TL;DR, I'll post back later with more info including table, view, and index definitions; before/after results; and query plans to help to answer this question. I'm trying to use a self-join to a view ...
user avatar
1 vote
1 answer
40 views

How do I SELECT elements using WHERE with another table's record values in WHERE statement?

Problem summary Today part of my app crashed, but nothing else stopped with it. I need to find the first and last tracked message ids for each channel during the downtime. I log unix timestamps ...
user avatar
  • 13
0 votes
1 answer
24 views

Find records with join matching all join IDs

Suppose to have the following database setup: Users ( id int primary key, name varchar(255) ) OrgUnits ( id int primary key, name varchar(255) ) Users_OrgUnits ( user_id int, orgunit_id ...
user avatar
1 vote
0 answers
52 views

Postgres: one-to-one join with duplicates and inequality matching

** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 ** I have two tables taken from two data sources,...
user avatar
1 vote
1 answer
36 views

join condition order

Given a simple query like this SELECT * FROM table_a a inner join table_b b on b.id_from_a = a.id Does the order of the on clause matters? In the example above I always put the recently joined ...
user avatar
0 votes
1 answer
30 views

How can I optimise this MySQL query?

I'm trying to make a filter which matches every record in the table to find ones which are within the range @min and @max. They're unxi timestamps if anyone was wondering. It does work right now, but, ...
user avatar
  • 13
1 vote
1 answer
32 views

How can I correct this mysql join query and put 1 to many in the same row?

This query is close to what I need but google_responsive_headlines.responsive_headlines has many rows to google_text_ads. I would like to have those results in the same row but not sure how to ...
user avatar
-6 votes
2 answers
46 views

How can I join 2 tables together, and then count by 2 columns? [closed]

Say I have these 2 tables: What is the best way for me to join these 2 tables, and then count the number of userids per country, per category?
user avatar
0 votes
1 answer
76 views

MySQL - Optimizing a large table's join

Im using the following query. This eat my complete CPU in Aurora Cluster. Also too much of wait. I tried by tunning index, but still no luck. Is there any recommendation to make it perform better? ...
user avatar
  • 1,708
0 votes
1 answer
30 views

how to display matching values from different tables which used in joins without duplicates

Here, I wrote a query using joins, the relation is fine, but I wanted display multiple columns from different tables which I used in joins, when I am trying to include or select other table columns it ...
user avatar
2 votes
1 answer
36 views

Getting all shared values of two or more given values in one table (SQL)

I have a chat database where each user belongs to a chat; this relation is represented in the table "is_member(uid, cid)". Now, when creating a new chat, I want to check if there is not ...
user avatar
  • 23
0 votes
1 answer
36 views

Is it possible to have a join table where the right side is one of several possible tables?

We have a table meant to store a property of something else: Table: property Column: id, PK Column: owner_id, FK Column: name Column: value Column: kind we generate a number of properties based a ...
user avatar
  • 123
1 vote
1 answer
69 views

Why is this simple query making up 50% execution time?

I'm using postgresql 9.5. I installed the pg_stat_statements extension to analyze queries and got the following surprising result: scheduler=# SELECT substring(query, 1, 30) AS query, ...
user avatar
  • 133
-1 votes
1 answer
25 views

how to join these individual queries into a single query

I have two individual queries, which I have to join them into single query. 1st query: I have a parameter called name in a request. When I give that name, it should determine its id. Example: If I ...
user avatar
0 votes
0 answers
27 views

How to use column names stored in another table?

I have a few tables in which I store some table names, column names, and some data. I need to get some column names from the content of these tables, and then use those names to create a bigger query. ...
user avatar
  • 135
1 vote
0 answers
24 views

Getting totals from different tables to create overall total

I currently have 2 tables TableA and TableB` both within the same MYSQL Database. I am trying to find a way to take totals from both tables and add them together to give a definitive total. The tables ...
user avatar
  • 111
1 vote
1 answer
50 views

Avoiding duplicates when joining multiple tables

Schema create table computer (computer_id bigserial primary key, computer_name varchar); create table computer_hdd (computer_hdd_id bigserial, computer_id bigint references computer(...
user avatar
  • 253
2 votes
1 answer
48 views

Is it a bad idea to have a "kind" column on a join table?

Is it a bad idea to have a "kind" column on a join table or should I just create separate tables for each kind? For example: Table A: Person Table B: Song JoinTable A: Person_Song (Kind: ...
user avatar
  • 123
0 votes
1 answer
61 views

No column name was specified for column 1 of 'd'

I am trying to get some data from 3 tables in SQL Server, but getting an error. I have created a fiddle with similar structure and sample data Based on the data in tblData, I am trying to get the ...
user avatar
  • 135
-1 votes
1 answer
38 views

Optimization : JOIN or CTE [closed]

Suppose that I have these tables,and example data (the real table I am working on has more columns) table `users` id name zip 1 John 201 table `region` zip city state country 22 a b c table `...
user avatar
  • 23
7 votes
4 answers
841 views

MariaDb - How to ensure a (LEFT) JOIN only if a single right side record exists

I have two tables I need to join together. However, I only want to join records when there are unqiue matches rather than picking one of several for the join. Versioning: Using MariaDB version 10.3.34 ...
user avatar
  • 173
0 votes
2 answers
70 views

Why does Postgres not use an index when joining a materialized view?

I have a table defined as: CREATE TABLE addresses ( "address" varchar(42), "other_info1" text, "other_info2" bigint ); and I have some manual corrections to ...
user avatar
0 votes
0 answers
44 views

Is there a cross join in these CTEs?

I have a query that is constructed from several CTEs (in order to organize the query). I constructed it in my SQL workbench and was happy with the test results on a smaller dataset, so I converted it ...
user avatar
  • 171
0 votes
1 answer
41 views

SQL join unexpected result

I have the following table: item running resourceid 017510 C1 43 338877 C4 44 451233 C1 45 771225 C4 41 011212 C4 47 313366 C3 34 771226 C4 48 990000 C4 46 for each "running" I ...
user avatar
  • 11
1 vote
1 answer
77 views

SELECT only IS NOT NULL columns with LEFT JOIN

I have a query where i need to select product options from multi tables. SELECT p.option_id, p.product_id, p.sku, p.qty, p.price, c.color_label, s.size_label, m.material_label, v.variation_label ...
user avatar
  • 43
0 votes
0 answers
18 views

MYSQL -> SELECT, JOIN, GROUP BY to UPDATE

I get the data I need with the following select statement: SELECT tk.actiondate, tk.completedate, tk.closedate, MAX(com.date) AS realcomplete, MAX(clo.date) AS realclose FROM ticket tk LEFT JOIN ...
user avatar
0 votes
1 answer
33 views

Drop the constant recurring JOINs in SQL queries on a SQL-Server, is this possible?

I would like to show the columns of "linked" tables in SQL-Server database. In my particular case, there are two tables "Loads" and "Locations", linked by Loads....
user avatar
  • 463
-1 votes
1 answer
16 views

Getting 502 on join eloquent laravel

I need to get distinct value using inner join. Here is query SELECT distinct(ad_camp_sid.cid) from ad_camp_sid inner join ad_dsp_sites ON ad_dsp_sites.site_id = ad_camp_sid.sid ...
user avatar
0 votes
0 answers
31 views

Joining table to MariaDB OQGRAPH table returns no rows

I am having an issue with the OQGRAPH storage engine in MariaDB. When I select from it, specifying a hard-coded destid and latch = 'dijkstras', it returns the expected data; however, when I try to use ...
user avatar
0 votes
1 answer
43 views

How to optimized self-join view of 50 millions rows table

I've the following simplified table with ~50M rows. table sample ( id uuid not null primary key, measured_date timestamp with time zone not null, segment_id uuid not null, activity_id ...
user avatar
3 votes
2 answers
1k views

How to Join two tables with SUM and GROUP BY in SQL server

I have 2 tables product id designation 1 coca 2 Pepsi 3 Fanta 4 Seven 5 Eight 2)sub_product product_id name quantity 1 sm 10 1 lg 10 1 xl 20 2 1L 10 2 2L 20 2 5L 20 3 Ty 10 3 Sa 20 4 ...
user avatar
3 votes
2 answers
457 views

Can I write a FULL OUTER JOIN without OR IS NULL?

Here's some data to play with: CREATE TABLE a ( a_id int NOT NULL, a_prop text NOT NULL ); CREATE TABLE b ( b_id int NOT NULL, b_prop text NOT NULL ); INSERT INTO a VALUES (1, '...
user avatar
  • 425
1 vote
1 answer
113 views

Simple query with a single join very slow

I have this very slow, simple query that joins a large table (~180M rows) with a smaller table (~60k rows) with a foreign key, filtering an indexed column on the smaller table, ordering by the primary ...
user avatar
0 votes
0 answers
24 views

CSV Resultset for IN clause

I am working on writing a SQL query to get data from 2 tables with inner join and need some help. Here is the scenario. I have 2 tables say A and B. table A Name Value 1 Red 2 Green 3 Blue 4 ...
user avatar
  • 1
0 votes
1 answer
295 views

child to parent in same table - there must be a better way to do this

i want to relate child to parent from same table. Table is built like this. id, parentid, name i want to have the complete path of the [name] columns without specify levels like i have in the example. ...
user avatar
0 votes
1 answer
270 views

Select a row using a foreign key in another table

Hi I'm using python and sqlalchemy and postgresql for creating a relational database I have 2 tables Users and Connections Users have information about each user with their id as primary-key and ...
user avatar
0 votes
1 answer
50 views

mssql - query with results from same table and association table

I need help with a query. I have 3 tables: "Parts" table. one important thing to note here is that, the parts table contains both main part and child part. Child parts are components of ...
user avatar
  • 105
0 votes
2 answers
434 views

MySQL: SUM() returns multiple rows when used with joins and grouping

I expected by query, which uses SUM() to return one row, containing the total of all rows. However, it returns multiple rows. SUM() is not adding the rows. SELECT SUM(DISTINCT `sales`.`sale_shipping`) ...
user avatar
0 votes
1 answer
66 views

How do I get the sum of a field and avoid duplication from an inner join?

I am pulling Geo from the Accounts table and I am ranking the Geos by Opportunity $$ Amt based on Accounts that have the name like "Brinks%". Accounts are unique, and there can be many ...
user avatar
  • 3
4 votes
1 answer
531 views

Can CASE be used to select which table to JOIN?

I'm attempting to use a CASE expression to select which table each row should be joined with. Say I've got a table of item_instances in my game, of all the items in the game world, and some of the ...
user avatar
  • 175

1
2 3 4 5
38