SQL Query Join Problem

This query returns the allocations but as you see in teh result set set attached I still need the name of the allocation returned where the null value is as well as the id (AllocationMain). What can I do to acomplish this?


select e.AllocationName,e.ID, sum(sq.QTYSHIPPED) OESHIP
  from CHISM_oeline sq
  INNER JOIN CHISM_icmast d on d.productid = sq.productid
  INNER JOIN CHISM_oemain sq1 ON sq1.ticketid = sq.ticketid
  LEFT JOIN CHISM_Allocation_Items b on b.ProductID = sq.productid
  LEFT JOIN CHISM_Allocation_Brands c on c.BrandID = d.Brand_ID
  LEFT JOIN CHISM_Allocation_Main e on e.ID = b.AllocID OR e.ID = c.AllocationID
  where (sq1.Stage = 'Entered' Or sq1.Stage = 'Released')
  AND SQ.Voided = 0
  GROUP BY e.AllocationName, e.ID, b.ID, c.ID Book1.xls
jnordengAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

UnifiedISCommented:
for starters, you will probably want to remove the b.ID, c.ID from your group by as they are not returned anyhow.
0
jnordengAuthor Commented:
its okay. I figured out the problem. Those were for testing purposes only. Ok with you if I delete?
0
UnifiedISCommented:
The Nulls are where there is no record for Allocation_Main.  If you want to return a name, you have to have a record so the join or the where will have to change.  Can you elaborate on what the desired results will look like?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

UnifiedISCommented:
fine with me, delete away
0
jnordengAuthor Commented:
like I just said I already solved the problem. I would like to delete this question. Ar eyou ok with that?
0
strickddCommented:
the problem is that you are using a LEFT JOIN here:
LEFT JOIN CHISM_Allocation_Main e on e.ID = b.AllocID OR e.ID = c.AllocationID

This means that if a record exists in table "b" or "c" then all the columns in "e" will be NULL. If you want to eliminate NULL values, you either have to make this a JOIN which will remove all records from the result that are in "b" or "c" but NOT in "e", or you can make sure that there is always data in "e" when there is data in "b" or "c" using data constraints (i.e., foreign keys)
0
jnordengAuthor Commented:
ok....are you interseted in teh solution or not really?
0
jnordengAuthor Commented:
lol I dont even know how to delete a question here.....
0
jnordengAuthor Commented:
select DISTINCT e.AllocationName,e.ID, sum(f.QTYSHIPPED) OESHIP
   from CHISM_Allocation_Main e
  LEFT JOIN CHISM_Allocation_Items b on b.allocid = e.id
  LEFT JOIN CHISM_Allocation_Brands c on c.AllocationID = e.ID
  INNER JOIN CHISM_icmast d on d.productid = b.productid OR d.Brand_ID = c.BrandID
  LEFT JOIN CHISM_oeline f on f.ProductId = d.ProductId
  LEFT JOIN CHISM_oemain g on g.TicketId = f.ticketid


--here is where we lost our data......adding NULL returned the records....
  where (g.Stage = 'Entered' Or g.Stage = 'Released' Or g.Stage IS NULL)
  AND f.Voided = 0 OR f.Voided is NULL

  GROUP BY e.AllocationName, e.ID

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
dqmqCommented:
The NULL is telling you that Allocation_Main does not contain an ID that matches b.AllocID or c.AllocationID.   If you want such a name, you need to get it from another table or correct your data.

As for e.ID, the same is true.  However, in that case its apparent that you might find it in either b.allocID or c.AllocationID or both.  The problem is that they are not necessarily the same. The NULL is telling you e.ID does not match either...so, which one do you want to see that it does not match????

0
jnordengAuthor Commented:
Thanks
0
jnordengAuthor Commented:
The problem was in the conditions I didn't ask them to still allow null values for stage or voided. So naturally it kicked out those records. They would be null of course if main or oeline didnt have matching records.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
alternative: move the conditions to the JOIN clause:

  LEFT JOIN CHISM_oemain g on g.TicketId = f.ticketid  AND  (g.Stage = 'Entered' Or g.Stage = 'Released'  )

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

From novice to tech pro — start learning today.