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
meydaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
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
0
PortletPaulfreelancerCommented:
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?
0
meydaAuthor Commented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulfreelancerCommented:
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
0
meydaAuthor Commented:
thanks, this has returned 1348 rows - not sure why?
0
PortletPaulfreelancerCommented:
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
0
Daniel WilsonCommented:
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.
0
PortletPaulfreelancerCommented:
ah, yes, hadn't actually noticed the original query :( my bad ):

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

this is an INNER JOIN

ans is the direct equivalent of this:

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

so, when we move to this:

select l.internalid, 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 and l.item=i.item

you will probably get more records than before

well spotted Daniel
0
Daniel WilsonCommented:
And one more correction.  We want all items with whatever locations they match, not the reverse.  So ...

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
good recovery Daniel :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.