logo
down
shadow

In SQL, Main table should match with lookup table for the multiple columns


In SQL, Main table should match with lookup table for the multiple columns

By : user2174547
Date : October 17 2020, 08:10 PM
like below fixes the issue You get multiple matches because of the NULL = matches anything rule. So for any Name, Rule4 will definitely matches as it is NULL for all columns
If you only wanted the best matches, use a TOP 1 with ORDER BY
code :
SELECT  *
FROM    dbo.MAIN_TABLE m
        OUTER APPLY
        (
            SELECT  TOP 1 *
            FROM    dbo.LOOKUP_TABLE l
            WHERE   (l.COL1 = m.COL1 OR l.COL1 IS NULL)
            AND     (l.COL2 = m.COL2 OR l.COL2 IS NULL)
            AND     (l.COL3 = m.COL3 OR l.COL3 IS NULL)
            AND     (l.COL4 = m.COL4 OR l.COL4 IS NULL)
            ORDER BY 
                CASE WHEN l.COL1 IS NOT NULL THEN 1 ELSE 2 END
            +   CASE WHEN l.COL2 IS NOT NULL THEN 1 ELSE 2 END
            +   CASE WHEN l.COL3 IS NOT NULL THEN 1 ELSE 2 END
            +   CASE WHEN l.COL4 IS NOT NULL THEN 1 ELSE 2 END
        ) l


Share : facebook icon twitter icon
R - Match values from multiple columns in a data.frame to a lookup table

R - Match values from multiple columns in a data.frame to a lookup table


By : Okan kavılı
Date : March 29 2020, 07:55 AM
it helps some times I think it's easiest to reframe this as joining tables rather than matching. I got a solution with dplyr, purrr, and base::merge() for handling strings. This should scale up for you!
code :
library(dplyr)
library(purrr)
data <- structure(list(Mouse = c(1, 2, 3, 4, 5, 6, 7, 8), Group = c("SNI", 
                                                                    "Sham", "SNI", "Sham", "SNI", "Sham", "SNI", "Sham"), t1 = c("00011", 
                                                                                                                                 "00011", "00000", "00110", "00001", "00010", "01001", "00110"
                                                                    ), t2 = c("00000", "00001", "00001", "00000", "00001", "00101", 
                                                                              "00100", "00010")), .Names = c("Mouse", "Group", "t1", "t2"), row.names = c(NA, 
                                                                                                                                                          -8L), class = c("tbl_df", "tbl", "data.frame"))

lookup <- structure(list(Response = c("00000", "00001", "00010", "00011", 
                                      "00100", "00101", "00110", "00111", "01000", "01001", "01010", 
                                      "01011", "01100", "01101", "01110", "01111", "10000", "10001", 
                                      "10010", "10011", "10100", "10101", "10110", "10111", "11000", 
                                      "11001", "11010", "11011", "11100", "11101", "11110", "1111"), 
                         `Final Fil.` = c(9, 9, 7, 7, 7, 7, 5, 5, 7, 7, 5, 5, 5, 5, 
                                          3, 3, 7, 7, 5, 5, 5, 5, 3, 3, 5, 5, 3, 3, 3, 3, 1, 2), Adjustment = c(0.5, 
                                                                                                                -0.5, 0.5, -0.5, 0.5, -0.5, 0.5, -0.5, 0.5, -0.5, 0.5, -0.5, 
                                                                                                                0.5, -0.5, 0.5, -0.5, 0.5, -0.5, 0.5, -0.5, 0.5, -0.5, 0.5, 
                                                                                                                -0.5, 0.5, -0.5, 0.5, -0.5, 0.5, -0.5, 0.5, -0.5), PWT = c(9.5, 
                                                                                                                                                                           8.5, 7.5, 6.5, 7.5, 6.5, 5.5, 4.5, 7.5, 6.5, 5.5, 4.5, 5.5, 
                                                                                                                                                                           4.5, 3.5, 2.5, 7.5, 6.5, 5.5, 4.5, 5.5, 4.5, 3.5, 2.5, 5.5, 
                                                                                                                                                                           4.5, 3.5, 2.5, 3.5, 2.5, 1.5, 1.5)), .Names = c("Response", 
                                                                                                                                                                                                                           "Final Fil.", "Adjustment", "PWT"), row.names = c(NA, -32L), class = c("tbl_df", 
                                                                                                                                                                                                                                                                                                  "tbl", "data.frame"))

not_matched <- c("Mouse", "Group")
to_match <- colnames(data)[!colnames(data) %in% not_matched]
to_match
#> [1] "t1" "t2"

lookup_subset <- lookup[c("Response", "PWT")]
lookup_subset
#> # A tibble: 32 x 2
#>    Response   PWT
#>       <chr> <dbl>
#>  1    00000   9.5
#>  2    00001   8.5
#>  3    00010   7.5
#>  4    00011   6.5
#>  5    00100   7.5
#>  6    00101   6.5
#>  7    00110   5.5
#>  8    00111   4.5
#>  9    01000   7.5
#> 10    01001   6.5
#> # ... with 22 more rows

to_match %>%
  map_dfc(function(col) {
    data[col] %>%
      merge(lookup_subset, by.x = col, by.y = "Response", all.x = TRUE) %>%
      select(-1) %>%
      set_names(col)
  }) %>%
  bind_cols(data[not_matched], .)
#> # A tibble: 8 x 4
#>   Mouse Group    t1    t2
#>   <dbl> <chr> <dbl> <dbl>
#> 1     1   SNI   9.5   9.5
#> 2     2  Sham   8.5   9.5
#> 3     3   SNI   7.5   8.5
#> 4     4  Sham   6.5   8.5
#> 5     5   SNI   6.5   8.5
#> 6     6  Sham   5.5   7.5
#> 7     7   SNI   5.5   7.5
#> 8     8  Sham   6.5   6.5
How to insert data from single column of a temp table into multiple columns of main table?

How to insert data from single column of a temp table into multiple columns of main table?


By : alimad2
Date : March 29 2020, 07:55 AM
Does that help I have a data like showed below in temp table: , Try this:
code :
INSERT INTO [dbo].[t_Act]
SELECT MAX(CASE WHEN D.RN=1 THEN D.Col END)[EventType]
    ,MAX(CASE WHEN D.RN=2 THEN D.Col END) [ClientMsgID]
    ,MAX(CASE WHEN D.RN=3 THEN D.Col END) [SessionID]
    ,MAX(CASE WHEN D.RN=4 THEN D.Col END) [Protocol]
    ,MAX(CASE WHEN D.RN=5 THEN D.Col END) [MessageType]
    ,MAX(CASE WHEN D.RN=6 THEN D.Col END) [SequenceNumber]
FROM(
    SELECT *
        ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))RN 
    FROM #TempTable
    )D
ROW_NUMBER() OVER(ORDER BY OrderColumn)RN
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))RN
MongoDB - Apply similar lookup on multiple columns in main table

MongoDB - Apply similar lookup on multiple columns in main table


By : user2720268
Date : March 29 2020, 07:55 AM
To fix the issue you can do You can use below aggregation with mongodb 3.6 and above
Firstly you don't need to use $unwind here as your field already contains array of ObjectIds. And to select the specific field from the referenced collection you can use the custom $lookup with pipeline and $project the fields inside it.
code :
db.getCollection('students').aggregate([
  { "$lookup": {
    "from": "staff",
    "let": { "teachers": "$teachers" },
    "pipeline": [
       { "$match": { "$expr": { "$in": [ "$_id", "$$teachers" ] } } }
       { "$project": { "name": 1 }}
     ],
     "as": "teachers"
  }},
  { "$lookup": {
    "from": "attenders",
    "let": { "attenders": "$attenders" },
    "pipeline": [
       { "$match": { "$expr": { "$in": [ "$_id", "$$attenders" ] } } }
     ],
     "as": "attenders"
  }}
])
How to join multiple columns in one table to another lookup table?

How to join multiple columns in one table to another lookup table?


By : John
Date : March 29 2020, 07:55 AM
This might help you You will need to join the statuses table multiple times and then alias the columns in the select clause to be formatted to be what you want. Please note that you have to alias the tables in the join clause so that you can reference the columns in the select clause of the statement.
code :
SELECT filing_name
, ds1.status_name AS 'Doc1 Status'
, ds2.status_name AS 'Doc2 Status'
, ds3.status_name AS 'Doc3 Status'
FROM documents d
LEFT JOIN document_statuses ds1 ON d.doc1_status = ds1.status_id
LEFT JOIN document_statuses ds2 ON d.doc2_status = ds2.status_id
LEFT JOIN document_statuses ds3 ON d.doc3_status = ds3.status_id
How to match multiple columns based on lookup table

How to match multiple columns based on lookup table


By : user3460781
Date : March 29 2020, 07:55 AM
wish help you to fix your issue I have the following two data frames: , We could unlist the dataframe and match directly.
code :
new_df <- results
names(new_df) <- paste0("id", seq_along(new_df))
new_df[] <- lookup$id[match(unlist(new_df), lookup$price)]
cbind(results, new_df)

#  price_1 price_2 id1 id2
#1       2       3   B   C
#2       2       1   B   A
#3       1       1   A   A
library(dplyr)
bind_cols(results, results %>%  mutate_all(~lookup$id[match(., lookup$price)]))
Related Posts Related Posts :
  • How to create table via ODBC in ansi sql compliant way?
  • Postgres. How to get all the parents that meet the childs criteria?
  • 'Exploratory' SQL queries that uses one criteria to find more criteria
  • Adding Date columns
  • Display ContactName from Guid in Gridview using SQLDataSource / Asp.Net
  • making groups based on match between column 1 and column 2 from 1 table
  • Teradata 15.10 - Remove set of characters from a string
  • PostgreSQL array_agg(INTEGER[])
  • Does a query goes through all data when you only select the last N?
  • Displaying results based on customer first order after specific date SQL Server
  • Sum records and add note what was summed up in sql
  • Need help in generating values
  • RODBC - import a table
  • SQL: How to Group By task and choose only those that are completed?
  • PostgreSQL/PostGIS Count number of visits
  • How to I get a total count?
  • Many-to-many SQL relationship
  • Procedure call inside procedure
  • How to have decreasing running total in Oracle sql
  • SQL Group function query?
  • Which is the best way to calculate Year/Month in SQL Server?
  • Best way of sanitize unparametrizable sql
  • Querying for JSON data in Oracle creates syntax error
  • Show top 1 by max column
  • SQL: Insert newly created column in the same table
  • How to extract numbers after string using regexp?
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org