Tag: join

Obtain Name Column Based on Value

aggregate-functions database join postgresql sql

I have a table that calculates the number of associated records that fit a criteria for each parent record. See example below: note – morning, afternoon and evening are only weekdays What I am trying to achieve is to determine which columns have the lowest value and get their column name as such: Here i…

How to retrieve data from two MySQL tables with multiple associations

foreign-keys join mysql sql

I got these two tables where one table is having multiple foreign keys to the second table. Table rankings Table item One ranking record may have multiple association to item table using first_item_id, second_item_id, third_item_id or forth_item_id fields. I want to retrieve ranking records with the correspon…

In Postgresql, how do I use joins with multiple conditions including >= and <=

join left-join postgresql sql

I have table A and table B. Each row in table A represents every time a user sends a message. Each row in table B represents every time a user buys a gift. Goal: for each time a user sends a message, calculate how many gifts they’ve purchased within 7 days before the timestamp they sent the message. Som…

SQL: How to divide column by counts?

count join mysql sql

So I have this query This will print something like device_id paper_count 1 5000 2 10000 And I have this other other query that will return the number of times the toner has been changed according to some date. I used count to count the number of times it’s been changed. This will print something like d…

Find such values from a column which must be associated with all desired values from another column

join sql

I have a lookup table with two foreign key columns. The values can be as: A_ID B_ID 5 1 5 2 6 1 7 2 9 5 9 1 9 2 Now, How do I query this table to get all such ids of column A_ID which must be associated with a collection of desired ids of column B_ID i.e.

SQL Row Data to Column Data in Join

join sql sql-server ssms

Trying to wrap my tiny brain around how to write this query and I am at a loss: Current: How I would like it to look: If someone can point me to a function that could help me with this, it would be much appreciated. I already tried using: max(case when ….) and pivot() which I found in other articles,

Generate Result based on max count in secondary column after a join

full-outer-join join oracle plsql sql

I have two tables which have a common key between them, and quite a lot of other important infos ; for the sake of simplicity i will be using Combination A and Combination B. When a combination is met, whichever table has the maximum number of records should be the source where i collect the information ; in …

Find 5 greatest totals after summing (quantity x value) for each qualifying user

aggregate-functions join mysql php sql

I currently have a table which displays the inventory values of users. It works, but it displays all users and their inventory values in a random order. I’m wanting to limit the table to the top 5 descending inventory values only. This data cuts across three tables: DB Fiddle Users Inventories (contains…

SQL – Returning all rows that are not ‘vice versa’ of one another

join mysql self-join sql

I’ve got a situation as follows (note this setup has been created as a minimal reproducible example). I have a table of houses, created like this: I have a large table called house_listings, like this: So, let’s say I run the following query: and I get the following output: and then I run the foll…