Veeam is happy to provide a free NFR license for one year. It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
1-Many join with duplicate id's in one table
Posted on 2013-05-16
Have a table (from our accounting software so I can't change the way it works)
inItems
101-00002 AP Flour
101-00003 Salt
I have another table that lists the possible Units Of Measure for each item
inUOM
101-00002 BG22
101-00002 GR
101-00002 KG
101-00003 BX25
101-00003 GR
101-00003 KG
I need to get the item details & the UOM's that are not GR or KG
101-00002 AP Flour BG22
101-00003 Salt BX25
Tried a bunch of different Join combinations but all return everything
ideas?
inItems
101-00002 AP Flour
101-00003 Salt
I have another table that lists the possible Units Of Measure for each item
inUOM
101-00002 BG22
101-00002 GR
101-00002 KG
101-00003 BX25
101-00003 GR
101-00003 KG
I need to get the item details & the UOM's that are not GR or KG
101-00002 AP Flour BG22
101-00003 Salt BX25
Tried a bunch of different Join combinations but all return everything
ideas?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
2
2- +2
8 Comments
Active today
SELECT
i.item, u.UOM
FROM dbo.inItems i
INNER JOIN dbo.inUOM u ON
u.item = i.item AND
u.uom NOT IN ('GR', 'KG')
ORDER BY
i.item, u.UOM
If you want to list an item even if it has no (other) uom, use a LEFT OUTER JOIN instead of an INNER JOIN.
i.item, u.UOM
FROM dbo.inItems i
INNER JOIN dbo.inUOM u ON
u.item = i.item AND
u.uom NOT IN ('GR', 'KG')
ORDER BY
i.item, u.UOM
If you want to list an item even if it has no (other) uom, use a LEFT OUTER JOIN instead of an INNER JOIN.
first thing you have given us the table names but not the column names,
SO I assuming the below
100-0001 will be under the column itemID
BG22,KG,GR.... will be under the column UOM
if that is not correct, change them respectively in the below query
ok try this
SO I assuming the below
100-0001 will be under the column itemID
BG22,KG,GR.... will be under the column UOM
if that is not correct, change them respectively in the below query
ok try this
select i.*,U.UOM
from inItems i
JOIN inUOM U
ON i.itemID = U.itemID
AND u.UOM not in( 'KG','GR')
Hi,
If the first column named "id", and second column named "detail" of every table. You can try this:
Select * from inUOM
Left join inItems on inUOM.id = inItems.id
Where inUOM.detail <> 'GR' and inUOM.detail <> 'KG'
If the first column named "id", and second column named "detail" of every table. You can try this:
Select * from inUOM
Left join inItems on inUOM.id = inItems.id
Where inUOM.detail <> 'GR' and inUOM.detail <> 'KG'
I'm going to propose one slight variation to Scott's answer:
SELECT
i.item, u.UOM
FROM dbo.inItems i
INNER JOIN dbo.inUOM u ON
u.item = i.item AND
u.uom <> 'GR' AND
u.uom <> 'KG'
ORDER BY
i.item, u.UOM
He may want to weigh in on this, but it's my understanding that the IN will evaluate as logical ORs, which will expand the execution plans and could result in a sub-optimal execution plan being used.
Edit: just a proposed potential optimization to his answer which was, as you already said, spot on =)
SELECT
i.item, u.UOM
FROM dbo.inItems i
INNER JOIN dbo.inUOM u ON
u.item = i.item AND
u.uom <> 'GR' AND
u.uom <> 'KG'
ORDER BY
i.item, u.UOM
He may want to weigh in on this, but it's my understanding that the IN will evaluate as logical ORs, which will expand the execution plans and could result in a sub-optimal execution plan being used.
Edit: just a proposed potential optimization to his answer which was, as you already said, spot on =)
@didnthaveaname
do you really think the person asking this question needs the optimization of the query, in most of the cases over here they dont. The need the job done.
I might be wrong though, but this is my understanding for being a memeber from last one month.
do you really think the person asking this question needs the optimization of the query, in most of the cases over here they dont. The need the job done.
I might be wrong though, but this is my understanding for being a memeber from last one month.
The question's closed. The points are assigned (correctly). It was merely suggested to try and be helpful.
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 18 hours left to enroll
635 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.