asked on

sql syntax help

Hello Experts
I am trying to merge 2 tables and fetch the desired result, please see below
CREATE TABLE [dbo].[Temp_Items_To_Add](
      [Location] [nvarchar](50) NULL,
      [Item] [nvarchar](50) NULL,
      [T1] [nchar](10) NULL,
      [T2] [nchar](10) NULL,
      [T3] [nchar](10) NULL
) ON [PRIMARY]

Location      Item                  T1            T2            T3
KK : UK            BEERHOPS            52      32      19
KK : UK            BONSAITREES            2232      3153      1205
KK : USA      BONSAITREES                    48
KK : USA      CACTUSPLANT                    48

CREATE TABLE [dbo].[Temp_Lookup_To_Add](
      [Location] [nvarchar](50) NULL,
      [Item] [nvarchar](50) NULL,
      [InternalID] [nvarchar](50) NULL
) ON [PRIMARY]

Location      Item                  InternalId
KK : UK            BEERHOPS            2407
KK : USA      BONSAITREES            2408
KK : USA      CACTUSPLANT            2342

I need to match Loction and Item and fetch the Internal ID from the lookup table

If the item from Items_To_add table does not exist in the Lookup_table, then it should "N/A"

Desired Output

select l.internalid, i.location, i.item, i.t1, i.t2, i.t3
from temp_lookup_to_add l, temp_items_to_add i
where l.location=i.location and l.item=i.item

InternalId      location item t1 t2 t3
2407             KK : UK            BEERHOPS            52      32      19
2408            KK : USA        BONSAITREES                    48
2342            KK : USA        CACTUSPLANT                    48
N/A             KK : UK            BONSAITREES            2232      3153      1205

Daniel Wilson

Select Coalesce (l.internalID, 'N/A'), i.location, i.item,i.t1, i.t2, i.t3
from temp_lookup_to_add l LEFT JOIN temp_items_to_add i
ON l.location = i.location

PortletPaul

I'd much rather look at how you are generating the temp tables - strong likelihood a more efficient way exists - can we see those queries please?

meyda

ASKER

thanks DanielWilson for your input, the query has returned 2millions results instead of 293

PortletPaul; they are manually created, i get the data in CSV which I dumb into these 2 tables and fetch the desired results back in CSV .

Please advise

PortletPaul

thanks (manual I can't change)
I think you need 2 fields in the join, like this:

SELECT TOP 300 -- temporary for testing
       ISNULL(lkp.internalID, 'N/A')
     , itm.location
     , itm.item
     , itm.t1
     , itm.t2
     , itm.t3
FROM temp_lookup_to_add AS lkp
LEFT JOIN temp_items_to_add itm
       ON lkp.location = itm.location
      AND lkp.item = itm.item
;

Open in new window

the 'top 300' is for testing purposes

meyda

ASKER

thanks, this has returned 1348 rows - not sure why?

PortletPaul

we may need to see a bigger sample of your data perhaps?
there's nothing else shown above that would help in the join.

how many rows are in each table?

we could try it the other way around, like so:

SELECT TOP 1500 -- temporary for testing
       ISNULL(lkp.internalID, 'N/A')
     , itm.location
     , itm.item
     , itm.t1
     , itm.t2
     , itm.t3
FOM temp_items_to_add itm
INNER JOIN temp_lookup_to_add AS lkp
       ON itm.location = lkp.location
      AND itm.item = lkp.item
;

Open in new window

{+edit} note the inner join - if that is too restrictive (and probably will be) use LEFT JOIN instead

Daniel Wilson

PortletPaul is correct -- I omitted a field from the join.

You will get more rows from the correct join than from yours that used the WHERE clause b/c we are doing OUTER joins that allow an "N/A" to be filled in for the items missing a location.

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Daniel Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

good recovery Daniel :)