Sql-server – Returning Result Based on 2 conditions from one Table

querysql server

Problem Statement: I have a table that holds three pieces of information (Number, Id, Type). Let’s call it table A:

╔══════╦════╦══════╗
║Number║ Id ║Type  ║
╠══════╬════╬══════╣
║ 1234 ║1111║Test 1║
║ 1234 ║2222║Test 1║
║ 1234 ║3333║Test 2║
║ 1234 ║4444║Test 2║
╚══════╩════╩══════╝

I have another table that holds 4 pieces of information (Number, Test1, Test2, ID). Let’s call it table B:

╔══════╦═════╦══════╦══════════════════╗
║Number║Test1║Test 2║ ID               ║
╠══════╬═════╬══════╬══════════════════╣
║ 1234 ║1111 ║      ║a1v1b0000006lmMAAQ║
║ 1234 ║2222 ║      ║a1v1b0000006lmNAAQ║
║ 1234 ║     ║3333  ║a1vf1000000qvTYAAY║
║ 1234 ║     ║4444  ║a1vf1000000qvTiAAI║
╚══════╩═════╩══════╩══════════════════╝

What I would like to do is, based on Number and Type from Table A, to search the Table B and return the corresponding ID.

The desired outcome is as follows:

╔══════╦════╦══════╦══════════════════╗
║Number║ ID ║Type  ║ ID               ║
╠══════╬════╬══════╬══════════════════╣
║ 1234 ║1111║Test 1║a1v1b0000006lmMAAQ║
║ 1234 ║2222║Test 1║a1v1b0000006lmNAAQ║
║ 1234 ║3333║Test 2║a1vf1000000qvTYAAY║
║ 1234 ║4444║Test 2║a1vf1000000qvTiAAI║
╚══════╩════╩══════╩══════════════════╝

NB: I have tried to use a decode but reading up on it, I am getting mixed signals whether this is the correct approach.

Best Answer

This is a quick solution that uses union all.

The first query finds the matching rows between Table1 and Table2 where Table1.Type = 'Test 1' and Table1.Id = Table2.[Test 1]

The second query finds the matching rows between Table1 and Table2 where Table1.Type = 'Test 2' and Table1.Id = Table2.[Test 2]

--demo setup
Declare @Table1 table (Number int, ID int, Type varchar(10))
INSERT INTO @Table1
  (Number, Id, Type)
VALUES
  (1234, 1111, 'Test 1'),
  (1234, 2222, 'Test 1'),
  (1234, 3333, 'Test 2'),
  (1234, 4444, 'Test 2');


Declare @Table2 table (Number int, [Test 1] varchar(10), [Test 2] varchar(10), ID varchar(20))
INSERT INTO @Table2
  (Number, [Test 1], [Test 2], ID)
VALUES
  (1234, '1111', '', 'a1v1b0000006lmMAAQ'),
  (1234, '2222', '', 'a1v1b0000006lmNAAQ'),
  (1234, '', '3333', 'a1vf1000000qvTYAAY'),
  (1234, '', '4444', 'a1vf1000000qvTiAAI');

--solution using union all
SELECT t1.Number
    ,t1.ID
    ,t1.Type
    ,t2.ID
FROM @Table1 t1
JOIN @Table2 t2
    ON t1.Type = 'Test 1'
        AND t2.[Test 1] = t1.ID

UNION ALL

SELECT t1.Number
    ,t1.ID
    ,t1.Type
    ,t2.ID
FROM @Table1 t1
JOIN @Table2 t2
    ON t1.Type = 'Test 2'
        AND t2.[Test 2] = t1.ID

| Number | ID   | Type   | ID                 |
|--------|------|--------|--------------------|
| 1234   | 1111 | Test 1 | a1v1b0000006lmMAAQ |
| 1234   | 2222 | Test 1 | a1v1b0000006lmNAAQ |
| 1234   | 3333 | Test 2 | a1vf1000000qvTYAAY |
| 1234   | 4444 | Test 2 | a1vf1000000qvTiAAI |

Related Solutions

SQL Server – Return Multiple Rows Based on Max Date

You simply want:

SELECT
    [Customer ID],
    MAX([Some Date]) AS[Latest Date]
FROM[Cust Date TABLE]
GROUP BY
    [Customer ID];

Ok - you've revised it. You now want to order the rows and pick the top one:

WITH numbered AS (
    SELECT
        [Customer ID],
        [Some Date],
        [Balance],
        ROW_NUMBER() OVER (
            PARTITION BY
                [Customer ID]
            ORDER BY
                [Some Date] DESC
        ) AS rownum
    FROM[Cust Date TABLE]
)
SELECT
    [Customer ID],
    [Some Date],
    [Balance]
FROM numbered
WHERE
    rownum = 1;

Sql-server – SQL Server Replication – setting up scheduler issue

What version of SQL Server are you running? Can you post your replmerg command line?
(substituting <database> for database etc.)

Re' using replmerg.exe, looking at your error message, it looks like abilities beyond synchronization are involved...

For example sp_helpdistpublisher returns properties of the publishers using a distributor... and requires db_owner or replmonitor for the distribution database.

So... does the user running replmerg.exe have the required access, to the distributor / distribution database?

Also, you might find this StackOverflow question to be of interest:

Permissions required to run REPLMERG.EXE

Is their solution --a special purpose user-- of interest? And note the comment at bottom: "Some of these instructions might even lead to the reinitialisation of the subscriber, which requires the right to a drop\recreate the corresponding database."

EDIT:
While looking up info on your REPLMERG.EXE command line, I saw this link:

How to: Synchronize a Pull Subscription (Replication Programming)

and this quote, which seems pertinent:

Pull subscriptions can be synchronized programmatically and on-demand by invoking the appropriate replication agent.

... and under To start the merge agent from the command prompt or from a batch file, this quote:

If you are using SQL Server Authentication, you must also specify the following arguments:

-DistributorLogin
-DistributorPassword
-DistributorSecurityMode = 0
-PublisherLogin
-PublisherPassword
-PublisherSecurityMode = 0
-SubscriberLogin
-SubscriberPassword
-SubscriberSecurityMode = 0

Note that your REPLMERG.EXE command line does not have PublisherLogin, PublisherPassword, or PublisherSecurityMode.

I also found Security Role Requirements for Replication

See the row titled Maintain replication agents.:

db_owner database role in the appropriate database or sysadmin server role on the appropriate server.

If the agent was created by a user in the sysadmin role, and a proxy account was not specified for the agent, the agent runs under the context of the SQL Server Agent account. In this case, a user in the db_owner role cannot modify the job associated with the agent.

Hope that helps...

Best Answer

Related Solutions

SQL Server – Return Multiple Rows Based on Max Date

Sql-server – SQL Server Replication – setting up scheduler issue

Related Question