0

Sorry if the title was sort of vague was unsure how to phrase it.

I currently am choosing values for a field from table like so (clickable SqlFiddle link included):

`Select  `ID`,IF(`SpecificationID` is NULL,'0', `SpecificationID`) from Orders;`

http://www.sqlfiddle.com/#!9/b8108b/8

Which as you can see pulled the SpecificationID from the Orders table and if it found a NULL pulled 0. I get that I could/should make the default values in my table = 0 yet to do so would require multiple code changes so this was the easiest way to pull 0 for NULL values which I ultimately need for this process.

It turned out that Orders can have multiple Specifications so I created a Look-Up-Table with OrderID and SpecificationID

My new query therefor looks liks this:

Select `OrderID`,`SpecificationID` from LUT_Orders_Specifications 
order by OrderID;

http://www.sqlfiddle.com/#!9/b8108b/9

The issue is that since the LUT only stores values for records with an OrderID I don't get OrderIDs which have no values and therefore don't get 0 for them.

I am unsure how/if I can do a Join that will get the values from the LUT and also return 0 for the records that are not in the LUT.

I know how to select IDs that are not in a table e.g.

Select ID from Orders where ID not in
(Select T1.OrderID from LUT_Orders_Specifications T1
Inner Join Orders T2
on T1.OrderID=T2.ID)

http://www.sqlfiddle.com/#!9/b8108b/15

But I am unclear how I can combine the processes so I am grabbing all the specification values for a single order and a 0 value for an order that has no specifications.

user3649739
  • 1,829
  • 2
  • 18
  • 28
  • If I understand you correctly, you are looking for a [`left join`](https://stackoverflow.com/a/6188334/6248528). Take some time to look at it (and come back if you have more questions). It also contains a link to explain the behaviour for `not in` (your query is probably not what you want it to do (`on T2.OrderID=T2.ID` is probably not correct, and also, your join will usually be on the outside, not inside the `not in`). But I hope the linked answer will shed some light on that topic. – Solarflare Sep 24 '17 at 11:44
  • @Solarflare You don't understand correctly but that is my fault for calling the the second query a `Join` it is simply a select that went into an array. The last `NOT IN` works perfectly as I want it to do and I am clear on how to use the `Join` since I use them often. My question is actually how to "embed" the concept of empty records into the first `IF` statement. I'll see if can clarify this more. – user3649739 Sep 24 '17 at 11:52
  • @Solarflare Hopefully the reworked explanation and sqlfiddles help to clarify. Thank you. – user3649739 Sep 24 '17 at 12:38
  • 1
    Hmm, I am still not sure, but I'll start with what I think you are trying to do: `select orders.id, coalesce(t1.SpecificationID,0) from orders left join LUT_Orders_Specifications T1 on orders.id = t1.OrderID`. (coalesce is the standard way write your `if`, but your if will work identically there). It will list every order with their specifications (one per row), and 0 if no specification is there. If you want one row per order, you would need to clarify "get the values from the LUT" (you can use e.g. a `group by`). If that is not what you are looking for, maybe add sample data+expected result. – Solarflare Sep 24 '17 at 13:30
  • @Solarflare That seems to give me the result I want when I run it on `http://www.sqlfiddle.com/#!9/b8108b/9`, I'll try it on my actual production example and if it works there let you know so you can make it an official answer if you want. – user3649739 Sep 24 '17 at 17:08

0 Answers0