Skip to main content

Questions tagged [join]

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

Filter by
Sorted by
Tagged with
-1 votes
3 answers
127 views

LEFT JOIN Clause

I think it would be better for me to just ask why it is that we use the LEFT JOIN clause for the following task? List all manufacturer name and phone number that have poor sales… less than 3 sales of ...
1 vote
2 answers
3k views

How to select students that failed at least at all courses that student with id = 1 failed?

I have the following tables: STUDENT (student_id, first_name, last_name, birth_date, year , domain) PROFESSOR (professor_id, first_name, last_name, birth_date, hire_date, title, salary) COURSE (...
11 votes
2 answers
78k views

Apply INNER JOIN only if there is a row available otherwise use NULL

id value_id parent_id dropdown_id name 7 14945 14944 57 4000 8 14944 0 56 bbb 9 14943 14940 59 comprable 10 14942 14939 59 comprable 11 14940 14931 58 3 12 14939 14930 58 2 13 14931 14929 57 ...
0 votes
1 answer
2k views

How to remove NULL values in multidimensional array?

SELECT dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid ,array_agg(array[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]) as ...
10 votes
2 answers
5k views

How to manipulate join order of PostgreSQL optimizer?

I am working on table join order optimization. While query processing, I want to get table join order generated by optimizer, and update it by using my own algorithm in PostgreSQL? How can I update ...
0 votes
1 answer
2k views

Why the JOIN doesn't use index?

I have a query like this: EXPLAIN SELECT dictionary.id, dictionary.word, lwd.grammatical_role, lwd.phonetics FROM loghatnameh_dehkhoda dictionary LEFT JOIN loghatnameh_words_details lwd ON lwd.word = ...
0 votes
1 answer
95 views

MySQL query takes too long to execute

I'm working on an IOT based application for which I'm storing time series data in MySQL. I have two tables in different databases as follows: describe fo_data.device_data; +-----------+----------------...
0 votes
1 answer
49 views

Postgres join as inner json

Just trying to do a simple inner join as json. model posts (author is fk to profiles.id) id author title ... profiles id displayName ... Trying to do a simple join so that: select * ...
2 votes
2 answers
236 views

Straightforward inner join is slow despite using indices and ref/eq_ref types

I have a couple moderately-sized MySQL 8.0.30 InnoDB tables: models - 1,000,000 rows activations - 10,000,000 rows A fairly simple INNER JOIN takes about 500ms, which seems like a lot (is it?). This ...
0 votes
0 answers
32 views

how to make joins on different tables based on the data retrieved, with TimeScale/Postgres

Here is a simplified version of the problem I'm trying to solve: Table A with id and some data: id, data A01, xxxx A02, xxxx ... Table B with id and some other kind of data id, data B01, xxxx B02, ...
1 vote
1 answer
347 views

Find duplicate values by joining tables SQL server

Finding the duplicate values in the 'Item_Sales_Detail' table as NULL rows in the 'Sales' and 'Item' tables by joining three tables. 'Sales' table (ID is primary key) ID Invoice Date TotalAmount 10 ...
0 votes
0 answers
198 views

Redshift / Postgresql query multiple tables faster than Natural Full Join

I have been given a requirement to query 8(+) different Redshift tables and return some of the fields where email = provided email address. Current implementation: (select product_name, email, ...
-1 votes
1 answer
2k views

Retrieve all data from an inner join with duplicate column names in MySQL

I have a database with tables for "users" and "profiles". Every profile links back to a user. I would like to do an inner join where I am able to retrieve a set of profiles that ...
0 votes
0 answers
28 views

Avoid Duplicates Record

SELECT DISTINCT log.employee, log.attendance_date , case when ch.log_type = 'IN' then TIME_FORMAT(ch.time,"%H:%i:%s") end as attend_time , case WHEN ch.log_type = 'IN' THEN CONCAT(...
0 votes
1 answer
1k views

Bad inner join performance on one-to-many relationship

I have a question regarding the postgres planner that I can't wrap my head around. I am trying to get the planner to look up a project ID by its unique key before running the other side of the query ...
0 votes
1 answer
123 views

MYSQL: Return a predefined value if result of join is null

Before all, sorry my very bad english level. Here's my problem. If i have two tables: 'Products' |id | product | |---|---------| |1 | "Fork" | |2 | "Spoon" | |3 | "Knife&...
0 votes
0 answers
57 views

Joining Two Tables via JSONB column (date)

Given the following data: create table datas (id int, data jsonb); insert into categories (id, budget) values (1, '{"2022-07-01": {"budget_amount": value1, "some": &...
1 vote
1 answer
64 views

How to efficiently query with a join and a where/order across two tables?

I have two PostgreSQL tables: contents id (primary key) captured_at (date) status (enum) plenty of other columns we don't care about library_contents library_id content_id With three indexes on ...
-1 votes
1 answer
62 views

Add Some custom Text at the place where value is null in the join?

I want to join two table in such away that If there is no value against first table entry in the second table then it should fetch that row but insert some custom text in that row. Is there any ...
2 votes
1 answer
25 views

Every value in Column 1 Needs every other value in Column 1 as a new row

I am stuck on some code. As shown in the image, I need each state to be given a new row for every other road project in the country. Ohio, for example, is given 2 new rows in the new table, 1 for each ...
1 vote
1 answer
130 views

Combining Multiple queries from the same table inrto 1 query

I am looking to find a way to achieve the below... I have tried using Joins and SubQueries but no joy as yet... I have a table that keeps track of wins/loses/draws as so Date WLD 01/01/23 Win 05/01/...
0 votes
1 answer
74 views

PostgreSQL INNER JOIN With Most Current Records

I am trying to create this VIEW: CREATE VIEW highlight_on_rss_feed AS SELECT highlight_on_article.title AS title, highlight_on_meta_description.meta_description AS description, concat('...
3 votes
3 answers
3k views

Join twice on same table with different conditions and no PK / FK relations

I have the following tables: agencies: | code | commissions: | registered_at | action | origin | destination | commission | I need to SUM(commissions.commission) for the following conditions ...
0 votes
1 answer
22 views

merging 2 Queries into one

I have the following queries: Query 1: SELECT agencies.code, SUM(IF(commissions.action = 'collection', commissions.commission, 0)) as collections, SUM(IF(commissions.action = 'delivery',...
1 vote
1 answer
34 views

MySQL Query - Sum data according to Category Parent name - each their own column

Here is the data I have: table agencies: id code 18 agency_01 6 agency_02 table compensations id agency_id compensation_category_id amount registered_at 3 6 4 45000.00 2023-01-01 4 6 5 25000.00 ...
0 votes
0 answers
36 views

Select columns from joined tables, group or distinct by a single column, and order by another column

Here's essentially what I'm trying to do: SELECT deals.*, retailers.title AS retailer_title, ST_Distance(retailers.lonlat, 'SRID=4326;POINT(-118.4104684 34.1030032)' :: geography, 15 * ...
1 vote
1 answer
335 views

Why is it called "semi-join"?

If my understanding is correct, a semi-join is a way of selecting records from one dataset that have related (via a common attribute) records in another set. So where does the "semi" come ...
0 votes
1 answer
2k views

Lookup multiple ids in separate table with 2 subqueries

I have a query from a timesheets tables that I am using to select a submitter, approver, and a date. SELECT PRTIMESHEET.POSTED_TIME, PRTIMESHEET.PRRESOURCEID as "SUBMITTER", PRTIMESHEET....
0 votes
0 answers
26 views

How to retrieve 2 column from 2 tables, column 1 and 2 are count from two tables

How to retrieve 2 column from 2 tables, column 1 and 2 is (count) from two different tables and where condition from another table (table 3 ) my query is : SELECT ( SELECT COUNT(*) FROM `...
0 votes
1 answer
3k views

How would I use "LEFT JOIN" and "IF NOT EXISTS" in this case?

There're 2 main tables: drivers and driver_riders that are connected by a foreign key. Every day some of the drivers make a single or multiple rides. There's also a table driver_rewards driver_rewards ...
41 votes
2 answers
512k views

Using SELECT in the WHERE clause of another SELECT

I have made a draft remote application on top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functioning of the application. For each final business result that I produce, ...
1 vote
1 answer
143 views

Poor performance when at least one of the left joins should match a record

I've got bookings (bookings) in my database. A booking can have 0 to n flight services (flight_services) and 0 to n hotel services (hotel_services). A user on my website can filter the bookings by ...
0 votes
0 answers
35 views

Optimization of google query with multiple LEFT JOIN and OR operators!

I have a SQL query that runs twice daily and ends up spilling disk space, the query selects data from left joining three tables and use multiple OR operators to fetch results and then insert all ...
9 votes
1 answer
49k views

Oracle: Non key-preserved table should be

I'm getting "ORA-01779: cannot modify a column which maps to a non key-preserved table" when I try to update a join. I've searched around the site and found a lot of advice on what key-preserved ...
1 vote
0 answers
237 views

Postgres sorted join with millions of rows, low cardinality, slow query - time to partition tables?

I have an API that is used to query for data about programs and other forms of content. We have a few tables that I need to join on to fullfil an ad-hoc query, and I am running into trouble speeding ...
0 votes
2 answers
48 views

I have a database with a player, tournament, and registration table. I need to query all players that played in 2 types (attributes) of tournaments

I currently have two tournaments that have matches. Wimbledon and Atlanta open which I have one as a grand slam and the other as a masters 1000, and I want to get the players who played in both This ...
0 votes
1 answer
1k views

Why can't I use a table alias in a subquery?

I have a MS Access database. When I try to run this query: SELECT * FROM ( SELECT * FROM tblClients AS a1 LEFT JOIN ( SELECT TOP 1 lVersion, lRequestID AS lVerReqId, lClientId ...
1 vote
1 answer
119 views

Is there any advantage using non-equi join instead of where clause in Oracle SQL?

I've been studying Oracle SQL non-equi join and while there are so many different use cases, I noticed that joining tables using a range of values with non-equi join is basically the same as using ...
1 vote
0 answers
42 views

Increasing the 'stoptime' of generated routes

I'm learning some basic SQL skills and I was toying around with some data to see what I can do, however, I'm a bit stuck on how to proceed with my current problem. I have a table that looks like this: ...
1 vote
0 answers
29 views

Multipile queries vs a long query with joins in my situation

I am currently working on a project where, each user can possess multipile portfolios. Each portfolio contains products, each product contains assets, which can be associated to different kinds that ...
2 votes
1 answer
45 views

How to combine three tables for a queue in each Store in each town?

Sorry if my question is no appropriate for DA. I have a State-city table with unique id for every town/city name. (id: town --> city --> State) I also have a list of Store names associated to ...
0 votes
0 answers
28 views

Would INNER JOIN on equality of two keys delete rows effectively?

Ī̲ repair a MediaWiki site after a DoS attack which injected terabytes of garbage into the text table. Ī̲ found that DELETE with clauses works painfully slow and, even worse, locks the table; that is, ...
12 votes
2 answers
24k views

Postgres RIGHT JOIN with custom array

I'm using Postgres 9.1 and want to get a result with some blanks where there is no data. My query looks like the following: SELECT institution_id FROM ... WHERE institution_id IN (1, 3, 4, 5, 7, 9) ...
0 votes
1 answer
336 views

how to get data from two tables in mysql query with where Pk of one table is set as foreign key on PK column for other table?

The query I am using to retrieve data is as "select sum(totalbill) as tbdb, sum(discinamount) as tddb, sum (billafterdisc) as tbaddb," + " sum(totalqt) as tqdb," + " sum(payed) as tpdb," + " sum(...
1 vote
0 answers
478 views

database design - sales, products, customers, persons and organizations

I am trying to model a system where you can have persons or organizations as customers. persons or organizations can also have other sub-types like employees, suppliers and other types in the future. ...
0 votes
0 answers
68 views

How to show all the columns data in full outer join

I have 2 tables. The Result is Issue is EnteredBy=23 is not displaying because I am using CT1.EnteredBy. How to display CT11.EnteredBy value instead of NULL The Actual Query I am using is given ...
0 votes
2 answers
993 views

UNION SQL with LEFT JOIN

I was trying to do the UNION with two Queries and i received 42601 error. Can you help me to undersant how to fixed it? CODE : SELECT Date_part('year', pr.data_chiusura) AS anno, ...
1 vote
1 answer
306 views

Access Web App query 3 Tables with aggregatios - Need a workaround

I'm designing an Access 2016 Web App for inventoring our computers. The problem I have is with a query where I want to get the CPUCore-amount and RAM-Amount for each PC. The computer-value in RAM and ...
0 votes
1 answer
42 views

Using the correct JOIN

I am trying to create an app for the salespeople who work for my company to keep track of their sales/commission When a salesperson brings on an account, their name is noted on the account and the ...
0 votes
1 answer
68 views

Enforce query plan for queries with expensive predicates/UDFs

I have a question on how to enforce the query optimizer to use a certain execution plan. Lets assume we have two tables, t1 (25k rows) and t2 (100k rows). Also note that all the rows in t1 will find ...

1 2 3
4
5
41