I’m doing a challenge problem on DataCamp. It uses two tables, “economies” which contains economic information by country code, and “countries” which contains general country information by country code. The challenge is: Get country code, inflation rate, and unemployment rate in 2015 from the “economies” table, where the gov_form is not ‘Constitutional Monarchy’ or ‘%Republic%’ in the “countries” table. With
Tag: join
What is difference between where and join in Hive SQL when joining two tables?
For example, What is difference between where and join in Hive SQL when joining two tables? Answer Join like this is a bad practice because in general, WHERE is being applied after join and transforming it to JOIN and pushing predicates is upon optimizer, to convert it to proper join and avoid CROSS join (join without ON condition). Always use
SQL Query for multiple Pivot on same column
As per my requirement, I need to apply pivot twice on same column to get different values as column header. Please see below how the data is present in table and expected results. Table Question Response TranslatedResponse activityid createdon Reason Testing testaus 1 01-01-2022 EMail abc@gmail.com abc@gmail.com 1 01-01-2022 Action test testata 1 01-01-2022 Desired Output : Email Reason Action
Filter a Dataframe using a subset of it and two specific fields in spark/scala [closed]
Closed. This question needs debugging details. It is not currently accepting answers. Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question. Closed 10 months ago. Improve this question I have an Scala/Spark question. I’m using Spark 2.1.1. I have a Dataframe
What is wrong with the WHERE clause in this SQL query?
I fixed the syntax errors and it still won’t run. I keep getting the following error: unknown column ‘tblProduct.id’ in ‘where clause’ Note that there is a column ‘id’ in the table ‘tblProduct’ Answer MySql does not support full joins, the error does not describe the problem. One workaround for full joins in MySql would be to union two queries
SQL query for all grouped records in joined table
Let’s consider a toy example. There is a table employees and table tasks, where each task is assigned to one employee. One employee can have multiple tasks. I want to query employees filtering them by some columns in their tasks. I group the employees to display each one only once. The query would be something like this: This works fine,
R: “Fuzzy Match” and “Between” Statements
I am working with the R Programming Language. I have the following tables (note: all variables appear as “Factors”): I am trying to “join” (e.g. inner join) this tables on the following conditions: 1) if table_1$id “fuzzy equal” table_2$id AND 2) if table_1$date BETWEEN(table_2$date_2,table_2$date_3) I tried to write the following code in R to do this: Question: But I am
How to transform a postgresql select with join to a json object?
I want to transform the result from a select with joins into a json object. I mean this query: should output this: (there are more fields than these. I’m just making the example simple) I found a solution this way: But I think there should be a much better way to do this. Imagine that chat_messages had a lot more
LEFT JOIN ON NULL Key Values Combined with GROUP BY
I’m using Teradata SQL and I wrote the following query (pay attention at the LEFT JOIN) Some entries for t1.key2 und t1.key3 (of the left sided table) are NULL. When that’s the case, the rows are not showing in the result, why? Is that Teradata specific, I would expect a LEFT JOIN to show rows with NULL values. If I
How to get address by user’s id oracle
In my project, front end needs, following JSON data with this format. So I Created two table for user and their addresses, So I plan to going with this table structure, USER_TABLE USER_ADDRESS Address table I created this way because, each user have two addresses, one is permanent and other one is correspondence. So is this good table structure to