Here's a tricky question

Here's a tricky question

Post by Cary Linkfiel » Sat, 04 Nov 2000 04:11:31



We have three tables - ItemMaster, SpecialsHeader, SpecialsItems.  We run
Specials on groups of items.  We might have a "Film" special that has 10
different film items in it.  At the same time we have a "Candy" special that
has 5 items.  The "Film" special has a rebate, the "Candy" special does not.
An item may be in both specials at the same time.  I need to know if an item
in the Item Master is in ANY special that has a rebate.  The tables look
like this:

*ItemMaster*
Number    Descripition
1                Product One
2                Product Two

*SpecialsHeader*
SpecialName        HasRebate
Film                    True
Candy                False

*SpecialsItems*
SpecialName        ItemNumber
Film                    1
Film                    2
Candy                1

In this example, item number "1" is in a Special that has a rebate and in
one that does not have a rebate.  Item number "2" is NOT in a special with a
rebate.

How can I format a SELECT on ItemMaster that will return ItemNumber,
Description and RebateSpecialTrueOrFalse?

Cary Linkfield
eDentalDirect

 
 
 

Here's a tricky question

Post by Ben » Sat, 04 Nov 2000 05:34:51


Quote:> How can I format a SELECT on ItemMaster that will return ItemNumber,
> Description and RebateSpecialTrueOrFalse?

try this:

SELECT ItemMaster.Number, ItemMaster.Description, SpecialsHeader.HasRebate
FROM (ItemMaster INNER JOIN SpecialsItems ON ItemMaster.Number =
SpecialsItems.ItemNumber)
    LEFT JOIN SpecialsHeader ON SpecialsItems.SpecialName =
SpecialsHeader.SpecialName


Quote:> We have three tables - ItemMaster, SpecialsHeader, SpecialsItems.  We run
> Specials on groups of items.  We might have a "Film" special that has 10
> different film items in it.  At the same time we have a "Candy" special
that
> has 5 items.  The "Film" special has a rebate, the "Candy" special does
not.
> An item may be in both specials at the same time.  I need to know if an
item
> in the Item Master is in ANY special that has a rebate.  The tables look
> like this:

> *ItemMaster*
> Number    Descripition
> 1                Product One
> 2                Product Two

> *SpecialsHeader*
> SpecialName        HasRebate
> Film                    True
> Candy                False

> *SpecialsItems*
> SpecialName        ItemNumber
> Film                    1
> Film                    2
> Candy                1

> In this example, item number "1" is in a Special that has a rebate and in
> one that does not have a rebate.  Item number "2" is NOT in a special with
a
> rebate.

> How can I format a SELECT on ItemMaster that will return ItemNumber,
> Description and RebateSpecialTrueOrFalse?

> Cary Linkfield
> eDentalDirect


 
 
 

Here's a tricky question

Post by Roy Harve » Sat, 04 Nov 2000 06:08:55


Cary,

Did you know you have a product that is both film and candy?   8-)

An alternate approach, just for variety:

SELECT *,
       CASE WHEN EXISTS(select *
                          from SpecialsHeader as H
                          join SpecialsItems as I
                            on H.SpecialName = I.SpecialName
                         where M.ItemNumber = I.ItemNumber
                           and H.HasRebate = 'True')
            THEN 'True'
            ELSE 'False'
       END as RebateSpecialTrueOrFalse
  FROM ItemMaster as M

Note that this returns a row for every product, regardless of whether
there are any specials for that product at all.

Roy


>We have three tables - ItemMaster, SpecialsHeader, SpecialsItems.  We run
>Specials on groups of items.  We might have a "Film" special that has 10
>different film items in it.  At the same time we have a "Candy" special that
>has 5 items.  The "Film" special has a rebate, the "Candy" special does not.
>An item may be in both specials at the same time.  I need to know if an item
>in the Item Master is in ANY special that has a rebate.  The tables look
>like this:

>*ItemMaster*
>Number    Descripition
>1                Product One
>2                Product Two

>*SpecialsHeader*
>SpecialName        HasRebate
>Film                    True
>Candy                False

>*SpecialsItems*
>SpecialName        ItemNumber
>Film                    1
>Film                    2
>Candy                1

>In this example, item number "1" is in a Special that has a rebate and in
>one that does not have a rebate.  Item number "2" is NOT in a special with a
>rebate.

>How can I format a SELECT on ItemMaster that will return ItemNumber,
>Description and RebateSpecialTrueOrFalse?

>Cary Linkfield
>eDentalDirect

 
 
 

Here's a tricky question

Post by Cary Linkfiel » Sat, 04 Nov 2000 06:31:29


Thank you.  What do the parentheses around the FROM clause do?

Cary


> > How can I format a SELECT on ItemMaster that will return ItemNumber,
> > Description and RebateSpecialTrueOrFalse?

> try this:

> SELECT ItemMaster.Number, ItemMaster.Description, SpecialsHeader.HasRebate
> FROM (ItemMaster INNER JOIN SpecialsItems ON ItemMaster.Number =
> SpecialsItems.ItemNumber)
>     LEFT JOIN SpecialsHeader ON SpecialsItems.SpecialName =
> SpecialsHeader.SpecialName



> > We have three tables - ItemMaster, SpecialsHeader, SpecialsItems.  We
run
> > Specials on groups of items.  We might have a "Film" special that has 10
> > different film items in it.  At the same time we have a "Candy" special
> that
> > has 5 items.  The "Film" special has a rebate, the "Candy" special does
> not.
> > An item may be in both specials at the same time.  I need to know if an
> item
> > in the Item Master is in ANY special that has a rebate.  The tables look
> > like this:

> > *ItemMaster*
> > Number    Descripition
> > 1                Product One
> > 2                Product Two

> > *SpecialsHeader*
> > SpecialName        HasRebate
> > Film                    True
> > Candy                False

> > *SpecialsItems*
> > SpecialName        ItemNumber
> > Film                    1
> > Film                    2
> > Candy                1

> > In this example, item number "1" is in a Special that has a rebate and
in
> > one that does not have a rebate.  Item number "2" is NOT in a special
with
> a
> > rebate.

> > How can I format a SELECT on ItemMaster that will return ItemNumber,
> > Description and RebateSpecialTrueOrFalse?

> > Cary Linkfield
> > eDentalDirect

 
 
 

Here's a tricky question

Post by Ben » Sat, 04 Nov 2000 23:50:42


the parentheses specifies that the tables ItemMaster and SpecialsItems are
joined first before they are in turn joined to SpecialsHeader.  this is
particularly necessary when an inner join is combined with a left join in a
single select statement.

B


> Thank you.  What do the parentheses around the FROM clause do?

> Cary



> > > How can I format a SELECT on ItemMaster that will return ItemNumber,
> > > Description and RebateSpecialTrueOrFalse?

> > try this:

> > SELECT ItemMaster.Number, ItemMaster.Description,

SpecialsHeader.HasRebate
> > FROM (ItemMaster INNER JOIN SpecialsItems ON ItemMaster.Number =
> > SpecialsItems.ItemNumber)
> >     LEFT JOIN SpecialsHeader ON SpecialsItems.SpecialName =
> > SpecialsHeader.SpecialName



> > > We have three tables - ItemMaster, SpecialsHeader, SpecialsItems.  We
> run
> > > Specials on groups of items.  We might have a "Film" special that has
10
> > > different film items in it.  At the same time we have a "Candy"
special
> > that
> > > has 5 items.  The "Film" special has a rebate, the "Candy" special
does
> > not.
> > > An item may be in both specials at the same time.  I need to know if
an
> > item
> > > in the Item Master is in ANY special that has a rebate.  The tables
look
> > > like this:

> > > *ItemMaster*
> > > Number    Descripition
> > > 1                Product One
> > > 2                Product Two

> > > *SpecialsHeader*
> > > SpecialName        HasRebate
> > > Film                    True
> > > Candy                False

> > > *SpecialsItems*
> > > SpecialName        ItemNumber
> > > Film                    1
> > > Film                    2
> > > Candy                1

> > > In this example, item number "1" is in a Special that has a rebate and
> in
> > > one that does not have a rebate.  Item number "2" is NOT in a special
> with
> > a
> > > rebate.

> > > How can I format a SELECT on ItemMaster that will return ItemNumber,
> > > Description and RebateSpecialTrueOrFalse?

> > > Cary Linkfield
> > > eDentalDirect

 
 
 

Here's a tricky question

Post by Joe Celk » Sun, 05 Nov 2000 01:43:00


Quote:>> We have three tables - ItemMaster, SpecialsHeader, SpecialsItems.

We run Specials on groups of items.  We might have a "Film" special
that has 10 different film items in it.  At the same time we have
a "Candy" special that has 5 items.  The "Film" special has a rebate,
the "Candy" special does not. An item may be in both specials at the
same time.  I need to know if an item in the Item Master is in ANY
special that has a rebate.  The tables look like this: <<

Please post DDL and not your personal pseudo-code and quasi-
illustrations.  This makes people guess about data types, keys,
constriants, etc.  I assume that you know that Standard SQL-92 does not
have a BOOLEAN datatype -- if it did, the 3VL and the meaning of NULLs
would get all screwed up, then you would have to make special rules
just for BOOLEANs, etc.

Here is my best guess as to what your schema is or should be

Number is a terrible name for a column -- too general to identify a
data element; is it s count?  an id?  a location?   I also havbe not
heard a table called a "master" - that is an old file system term from
the magnetic tape days and the network databses of the 1970's.  In SQL,
we try to ggive tables names that tell us what they are a set of with a
collective or plural noun -- a logical description, not a procedural or
physical one.

CREATE TABLE Items
(item_nbr INTEGER NOT NULL PRIMARY KEY,
 descripition VARCHAR(15) nOT NULL);

 1                Product One
 2                Product Two

CREATE TABLE Specials
(special_name VARCHAR(15) NOT NULL PRIMARY KEY,
 rebate CHAR(1) NOT NULL DEFAULT 'y'
                CHECK (rebate IN ('y','n'))

 Film                    True
 Candy                False

CREATE TABLE SpecialsItems
(special_name VARCHAR(15) NOT NULL
              REFERENCES Specials(special_name)
              ON DELETE CASCADE
              ON UPDATECASCADE,
 item_nbr INTEGER NOT NULL
          REFERENCES Items(item_nbr)
              ON DELETE CASCADE
              ON UPDATECASCADE,
 PRIMARY KEY (special_name, item_nbr));

 Film                    1
 Film                    2
 Candy                1

Quote:>> In this example, item number "1" is in a Special that has a rebate

and in one that does not have a rebate.  Item number "2" is NOT in a
special with a rebate. <<

Okay, I can see that.  but your specs do not say what to do about an
item is in one special with a rebate and a second special without a
rebate.  Do you want to know what the specials are for each item?  Or
just that onhe eixsts?

Quote:>> How can I format a SELECT on Items that will return item_nbr,

description and rebate ? <<

This will give you a list of all the items that are in at least one
Special with a rebate.  Given the FOREIGN KEY ocnstraints, the EXISTS()
predicates ought to use just the indexes in the plan.

SELECT I1.*
  FROM Items AS I1
 WHERE EXISTS
       (SELECT *
          FROM SpecialItems AS SI1
         WHERE SI1.item_nbr = I1.item_nbr
           AND EXISTS
               (SELECT *
                  FROM Specials AS S1
                 WHERE rebate = 'y'
                   AND S1.special_name = SI1.special_name)));

--CELKO--
Joe Celko, SQL Guru & DBA at Trilogy
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

1. Here's a tricky one

I'm trying to generate an hierarchical view where multiple sub-nodes exist:

The scenario is this:
* Table STORES has store info
* Table INVENTORY is indentifier dependent on stores, contains data for all
stores
* Table HOURS has 7 rows, one for each day of the week.

Using sqlxmlrequest how would you get a result set like this:

<STORE name="My Store">
  <HOURS day="Monday" open="08:00am" close="11:00pm"/>
  <HOURS day="Tuesday" open="09:00am" close="10:00pm"/>
  <HOURS day="Wednesday" open="10:00am" close="09:00pm"/>
  <INVENTORY name="Widget" qoh="3"/>
  <INVENTORY name="Gadget" qoh="4"/>
  <INVENTORY name="Thingy" qoh="5"/>
</STORE>
<STORE name="Your Store">
  <HOURS day="Monday" open="07:30am" close="11:30pm"/>
  <HOURS day="Tuesday" open="09:30am" close="10:30pm"/>
  <HOURS day="Wednesday" open="10:30am" close="09:30pm"/>
  <INVENTORY name="Gizmo" qoh="7"/>
  <INVENTORY name="Thingamabob" qoh="8"/>
  <INVENTORY name="Thingy" qoh="9"/>
</STORE>

My attempts always result in <INVENTORY> ending up as a sub-node of <HOURS>.

Any ideas!  Thanks!

2. Recover deleted rows?

3. This couldn't be that tricky

4. PrepareInfo in distributed transaction

5. New Request - I don't know why it is tricky

6. BDE Bugs 3.5 [Revised]

7. Here's a tricky

8. QBE File - Calculation error - Help Requested

9. Tricky SQL Question

10. tricky question with 2 fact tables

11. Tricky DateTime question