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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.