On Monday, November 14, 2016 at 8:37:43 AM UTC-8, [email protected]
wrote:
>
> I have a unique issue and I'm not certain if its SQL causing the issue or
> my query.
>
> I have two tables that hold product information. One is the overall
> product data, the other is stock levels based on product metadata.
>
> For example, products may have a size, color, and style.
>
> Based on the size, color, and style, we can set up what items are in stock.
>
> My issue is that products where the size, color, or style are empty do not
> appear when I join on these tables.
>
> Sequel::Model.db[:inventory].left_join(:stock,[[:stock__item_id => :
> inventory__item_id], [:stock__style => :inventory__style], [:stock__color
> => :inventory__color], [:stock__size => :inventory__size]])
>
> This results in the SQL
>
> SELECT * FROM `inventory` LEFT JOIN `stock` ON ((`stock`.`item_id` =
> `inventory`.`item_id`) AND (`stock`.`style` = `inventory`.`style`) AND
> (`stock`.`size` = `inventory`.`size`) AND (`stock`.`color` =
> `inventory`.`color`))
>
> This is great!
>
> The problem is that we have some items where Style is NULL or Color is
> NULL. Is there a way to include rows where null = null ?
>
In SQL, NULL = NULL is NULL and not TRUE. You would have to do something
like:
Sequel::Model.db[:inventory].left_join(:stock, :stock__item_id => :
inventory__item_id) do |j, lj, js|
Sequel.&(*[:style, :color, :size].map{|c| Sequel.|({Sequel[j][c]=>Sequel[
lj][c]}, {Sequel[j][c] => nil, Sequel[lj][c] => nil})})
end
which uses the following SQL:
SELECT * FROM inventory LEFT JOIN stock ON ((stock.item_id =
inventory.item_id) AND ((stock.style = inventory.style) OR ((stock.style IS
NULL) AND (inventory.style IS NULL))) AND ((stock.color = inventory.color)
OR ((stock.color IS NULL) AND (inventory.color IS NULL))) AND ((stock.size
= inventory.size) OR ((stock.size IS NULL) AND (inventory.size IS NULL))))
This is a fairly bad idea from a relational algebra perspective, but if
that's what will work in your application, you'll have to use something
like it.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
