Monday, March 19, 2012

Choosing one record over another

How would I select one record and not the another related record in one
Select statement?
I have regular prices that I want to display on most days and promotional
prices on particular days (christmas, summer etc) where I don't want to
display the regular price if I am displaying the promotional price.
I have a table that has 2 types of records in it.
One record is a normal ticket price on a ship. The other record is a
promotional price for that ship.
If it is a Regular Price, there will be null values in the PromoPriceID,
StartDate and End Date (as these are only used for the promotional prices).
If it is a Promotional Price there will be a Null Value in ShipName (as I
would get that from the regular record). The PromoPriceID would contain the
Regular records ShipPricingID (so that it could get the name of the ship and
so that we would know not to display that records regular price). The
StartDate and EndDate would have the date range the promotional prices ran
for.
Here is a test file setup with the display of all the records.
****************************************
************************************
******************************
drop table shipPricing
create table shipPricing
(
ShipPricingID int Identity Not Null,
ShipName varChar(50) Null,
PromoPriceID int Null,
Price Money Not Null,
StartDate smalldatetime Null,
EndDate smalldatetime Null
)
insert shipPricing(ShipName,Price) values ('Sea Witch',1500)
insert shipPricing(PromoPriceID,Price,StartDate
,EndDate) values
(Scope_Identity(),1300,'12/01/05','12/31/05')
insert shipPricing(ShipName,Price) values ('Southern Cross',2200)
insert shipPricing(PromoPriceID,Price,StartDate
,EndDate) values
(Scope_Identity(),2050,'12/15/05','12/31/05')
select ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID, Price
= Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) from shipPricing
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
1 Sea Witch NULL 1,500.00
NULL NULL
2 NULL 1
1,300.00 12/01/05 12/31/05
3 Southern Cross NULL 2,200.00
NULL NULL
4 NULL 3
2,050.00 12/15/05 12/31/05
****************************************
************************************
****************************************
**
What I want to do is have a Select statement that would give me back the
prices available for a particular date (Select ... where date = some date).
If it is a promotion I don't want to show the regular price and I would
like it to say something like "Promo".
For example:
If I want the prices for 11/15/01 I should get something like:
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
1 Sea Witch NULL 1,500.00
NULL NULL
3 Southern Cross NULL 2,200.00
NULL NULL
If I want prices for 12/05/05, I should get something like:
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
2 Sea Witch 1
1,300.00 12/01/05 12/31/05 Promo
3 Southern Cross NULL 2,200.00
NULL NULL
If I want prices for 12/18/05, I should get something like:
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
2 Sea Witch 1
1,300.00 12/01/05 12/31/05 Promo
4 Southern Cross 3 2,050.00
12/15/05 12/31/05 Promo
I can't figure out how to select one record and not the other in one Select
statement.
Thanks,
Tomyou could do it with a simple if exists statement, like so:
-- let's assume you've got some kind of variable with your Date value
in it
DECLARE @.date datetime
IF EXISTS (SELECT * FROM shipPricing
WHERE @.date > StartDate AND @.date < EndDate)
SELECT * FROM shipPricing WHERE @.date > StartDate AND @.date < EndDate
ELSE
SELECT * FROM shipPricing WHERE StartDate IS NULL AND EndDate IS NULL
if the exists is true, you won't likely be taxed for the second query,
because the rows will already be cached from the if condition test. so
i'm pretty sure performance-wise you'll only be running 1 query,
essentially. also, you can simply replace * with whatever you want to
return, and since the promo stuff is in its own query, you can throw in
a 'Promo' value in for kicks.|||also, having the condition of one entity versus another being
determined by the presence or absense of values in a field seems a
little odd to me. if it's not a huge conversion, i would consider
migrating this data into two tables: shipPricingStandard, and
shipPricingPromo. that way your queries would look more like:
IF EXISTS (SELECT * FROM shipPricingPromo
WHERE @.date > StartDate AND @.date < EndDate)
SELECT * FROM shipPricingPromo WHERE @.date > StartDate AND @.date <
EndDate
ELSE
SELECT * FROM shipPricingStandard
not sure why, but it seems like a clearer division of what seem like
two similar, but separate entities. but that's probably open to debate,
and more cosmetic than anything.
hope this helps,
jason|||"jason" <iaesun@.yahoo.com> wrote in message
news:1124995465.591667.36420@.g14g2000cwa.googlegroups.com...
> you could do it with a simple if exists statement, like so:
> -- let's assume you've got some kind of variable with your Date value
> in it
> DECLARE @.date datetime
> IF EXISTS (SELECT * FROM shipPricing
> WHERE @.date > StartDate AND @.date < EndDate)
> SELECT * FROM shipPricing WHERE @.date > StartDate AND @.date < EndDate
> ELSE
> SELECT * FROM shipPricing WHERE StartDate IS NULL AND EndDate IS NULL
>
This was the problem I had.
The problem is that you can either get the Promo prices (those with dates)
or regular prices (those without dates). The problem is that I want to
either a regular price OR a promotional price for both ships.
Here are the results if you add in dates you can see the problem. In the
first 2 it would either be on or the other for those dates, but the last
date should have the regular price for the Sea Witch and the promo price for
the other. Also I need to grab the name of the ship from the regular price
record if a promo price.
****************************************
************************************
***************************************
declare @.date smalldatetime
select @.date = '11/01/05'
IF EXISTS (SELECT * FROM shipPricing
WHERE @.date > StartDate AND @.date < EndDate)
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE @.date >
StartDate AND @.date < EndDate
ELSE
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE StartDate
IS NULL AND EndDate IS NULL
ShipPricingID ShipName PromoPriceID Price StartDate
EndDate
-- -- -- -- -- --
--
1 Sea Witch NULL 1,500.00
NULL NULL
3 Southern Cross NULL 2,200.00
NULL NULL
****************************************
************************************
****************************************
**
****************************************
************************************
*************************************
declare @.date smalldatetime
select @.date = '12/16/05'
IF EXISTS (SELECT * FROM shipPricing
WHERE @.date > StartDate AND @.date < EndDate)
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE @.date >
StartDate AND @.date < EndDate
ELSE
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE StartDate
IS NULL AND EndDate IS NULL
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
2 NULL 1
1,300.00 12/01/05 12/31/05
4 NULL 3
2,050.00 12/15/05 12/31/05
****************************************
************************************
****************************************
****
****************************************
************************************
****************************************
****
declare @.date smalldatetime
select @.date = '12/03/05'
IF EXISTS (SELECT * FROM shipPricing
WHERE @.date > StartDate AND @.date < EndDate)
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE @.date >
StartDate AND @.date < EndDate
ELSE
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE StartDate
IS NULL AND EndDate IS NULL
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
2 NULL 1
1,300.00 12/01/05 12/31/05
****************************************
************************************
****************************************
*****
Thanks,
Tom
> if the exists is true, you won't likely be taxed for the second query,
> because the rows will already be cached from the if condition test. so
> i'm pretty sure performance-wise you'll only be running 1 query,
> essentially. also, you can simply replace * with whatever you want to
> return, and since the promo stuff is in its own query, you can throw in
> a 'Promo' value in for kicks.
>|||"jason" <iaesun@.yahoo.com> wrote in message
news:1124995909.532864.267610@.g47g2000cwa.googlegroups.com...
> also, having the condition of one entity versus another being
> determined by the presence or absense of values in a field seems a
> little odd to me. if it's not a huge conversion, i would consider
> migrating this data into two tables: shipPricingStandard, and
> shipPricingPromo. that way your queries would look more like:
The reason I didn't pick 2 tables is the data is essentially the same except
for type of record as well as the dates. I could also add a type code
field, but I can do that by testing for the shipPricingID (if nothing there
it is a regular price) as well as the dates (again if nothing there it is a
regular record).
Tom
> IF EXISTS (SELECT * FROM shipPricingPromo
> WHERE @.date > StartDate AND @.date < EndDate)
> SELECT * FROM shipPricingPromo WHERE @.date > StartDate AND @.date <
> EndDate
> ELSE
> SELECT * FROM shipPricingStandard
> not sure why, but it seems like a clearer division of what seem like
> two similar, but separate entities. but that's probably open to debate,
> and more cosmetic than anything.
> hope this helps,
> jason
>|||t - try this:
declare @.dt datetime
set @.dt = cast('12/18/05' as datetime)
select
case
when @.dt between s2.startdate and s2.enddate then s2.ShipPricingID
else s.ShipPricingID
end as ShipPricingID,
ShipName = substring(s.ShipName,1,20),
case
when @.dt between s2.startdate and s2.enddate then s2.PromoPriceID
else s.PromoPriceID
end as PromoPriceID,
case
when @.dt between s2.startdate and s2.enddate then s2.Price
else s.Price
end as Price,
case
when @.dt between s2.startdate and s2.enddate then s2.StartDate
else s.StartDate
end as StartDate,
case
when @.dt between s2.startdate and s2.enddate then s2.EndDate
else s.EndDate
end as EndDate,
case
when @.dt between s2.startdate and s2.enddate then 'PROMO'
else NULL
end as Promo
from shipPricing s inner join shipPricing s2 on s.ShipPricingID =
s2.PromoPriceID
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O84VJtZqFHA.2276@.TK2MSFTNGP10.phx.gbl...
> How would I select one record and not the another related record in one
> Select statement?
> I have regular prices that I want to display on most days and promotional
> prices on particular days (christmas, summer etc) where I don't want to
> display the regular price if I am displaying the promotional price.
> I have a table that has 2 types of records in it.
> One record is a normal ticket price on a ship. The other record is a
> promotional price for that ship.
> If it is a Regular Price, there will be null values in the PromoPriceID,
> StartDate and End Date (as these are only used for the promotional
prices).
> If it is a Promotional Price there will be a Null Value in ShipName (as I
> would get that from the regular record). The PromoPriceID would contain
the
> Regular records ShipPricingID (so that it could get the name of the ship
and
> so that we would know not to display that records regular price). The
> StartDate and EndDate would have the date range the promotional prices ran
> for.
> Here is a test file setup with the display of all the records.
>
****************************************
************************************
******************************kred">
> drop table shipPricing
> create table shipPricing
> (
> ShipPricingID int Identity Not Null,
> ShipName varChar(50) Null,
> PromoPriceID int Null,
> Price Money Not Null,
> StartDate smalldatetime Null,
> EndDate smalldatetime Null
> )
> insert shipPricing(ShipName,Price) values ('Sea Witch',1500)
> insert shipPricing(PromoPriceID,Price,StartDate
,EndDate) values
> (Scope_Identity(),1300,'12/01/05','12/31/05')
> insert shipPricing(ShipName,Price) values ('Southern Cross',2200)
> insert shipPricing(PromoPriceID,Price,StartDate
,EndDate) values
> (Scope_Identity(),2050,'12/15/05','12/31/05')
> select ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price
> = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
> Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
> SubString(Convert(varChar,EndDate,1),1,1
2) from shipPricing
> ShipPricingID ShipName PromoPriceID Price
> StartDate EndDate
> -- -- -- -- --
--
> --
> 1 Sea Witch NULL 1,500.00
> NULL NULL
> 2 NULL 1
> 1,300.00 12/01/05 12/31/05
> 3 Southern Cross NULL 2,200.00
> NULL NULL
> 4 NULL 3
> 2,050.00 12/15/05 12/31/05
>
****************************************
************************************
****************************************
**
> What I want to do is have a Select statement that would give me back the
> prices available for a particular date (Select ... where date = some
date).
> If it is a promotion I don't want to show the regular price and I would
> like it to say something like "Promo".
> For example:
> If I want the prices for 11/15/01 I should get something like:
> ShipPricingID ShipName PromoPriceID Price
> StartDate EndDate
> -- -- -- -- --
--
> --
> 1 Sea Witch NULL 1,500.00
> NULL NULL
> 3 Southern Cross NULL 2,200.00
> NULL NULL
> If I want prices for 12/05/05, I should get something like:
> ShipPricingID ShipName PromoPriceID Price
> StartDate EndDate
> -- -- -- -- --
--
> --
> 2 Sea Witch 1
> 1,300.00 12/01/05 12/31/05 Promo
> 3 Southern Cross NULL 2,200.00
> NULL NULL
> If I want prices for 12/18/05, I should get something like:
> ShipPricingID ShipName PromoPriceID Price
> StartDate EndDate
> -- -- -- -- --
--
> --
> 2 Sea Witch 1
> 1,300.00 12/01/05 12/31/05 Promo
> 4 Southern Cross 3
2,050.00
> 12/15/05 12/31/05 Promo
> I can't figure out how to select one record and not the other in one
Select
> statement.
> Thanks,
> Tom
>|||This is not a table. It has no key, uses proprietary data types, etc.
This one table has more not null-able columns than the payroll for a
major auto manufacture.
Are you aware that "camelCase" adds 8-12% more time to reading code?
The eye jumps to the uppercase letter, then back to the front of the
word.
Is there really a ship with a CHAR(50) name or were you just too lazy
to pick a proper size? With the complete lack of data integrity in
this schema, you will get one.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. IDENTITY cannot ever be a key
**by definition**, which I would hope you have learned by now.
CREATE TABLE VoyagePricing -- were you pricing the ships or the
trips?
(ship_name VARCHAR(30) NOT NULL -- I would use a ship code
CHECK (ship_name IN (..)),
promo_name CHAR(10) DEFAULT 'Regular Price' NOT NULL,
trip_price DECIMAL(7,2) NOT NULL,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (ship_name, start_date)
);
Notice the use of duration ranges. Promos are nested inside the
regular fare ranges.
That is really bad business! You can show them what they are saving
with this query.
SELECT ship_name, promo_name, trip_price
FROM VoyagePricing AS P
WHERE @.my_date BETWEEN start_date AND end_date;
This will give you both the promo and regular prices. I assume that we
give the customer the lower price.
SELECT ship_name, MIN(trip_price)
FROM VoyagePricing
WHERE @.my_date BETWEEN start_date AND end_date
GROUP BY ship_name;
You keep posting the worst code of anyone in this newsgroup. Can you
get your boss to pay for a basic RDBMS course for you and the other
programmers?
A little over a year ago, I got to watch incompetent RDBMS programmers
like you kill children in Africa by messing up a medical supply system.|||Nice Rant.
First of all, and you seem to miss the point, this is not my actual table.
If you look closely (and I know this is difficult), there are NO ship
details. That is because "I MADE THIS TABLE UP JUST TO IRRITATE YOU".
The table has exactly what I felt was necessary to illustrate my problem and
allow others to quickly run it (if they want) without wasting anyones time.
I am not worrying about keys here, I am not worried about indexes, I am not
worried about the size of the Ship name (anywhy would you think a 50 was not
a proper size for a ship name - too big, too little')
Everyone here has been great and I appreciate the time that people take to
help others. I would not presume on their time by not putting only the
necessary elements to illustrate the problem. I'm not sure where
nullable/non nullable, camelCase, Pascal, Uppercase, lower case, variable
size etc. has anything to do with the question I was asking.
BTW, you weren't incorrect in your assessment as to what I was looking for.
VC got it. What happened to you? This was the reason I made the table as
spartan as possible as well as multiple examples to show what I was looking
for. You obviously missed it.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124999165.137822.47650@.g44g2000cwa.googlegroups.com...
> This is not a table. It has no key, uses proprietary data types, etc.
> This one table has more not null-able columns than the payroll for a
> major auto manufacture.
>
It is a table.
A table must have keys?
And you even call it a table (one that has more not nullable columns ...).
It has 2 BTW, one being identity column, which by definition would not be
nullable anyway.
create table shipPricing
(
ShipPricingID int Identity Not Null,
ShipName varChar(50) Null,
PromoPriceID int Null,
Price Money Not Null,
StartDate smalldatetime Null,
EndDate smalldatetime Null
)
What propriety data types?

> Are you aware that "camelCase" adds 8-12% more time to reading code?
> The eye jumps to the uppercase letter, then back to the front of the
> word.
>
Never seen that statistic. Where did you get that from?
Actually, I use camelCase for variable names (as do many). I also don't do
ship_names, I do ShipNames or shipNames. I never liked the underscore. But
that's just me.
You use ShipNames and ship_names style - but that's just you.

> Is there really a ship with a CHAR(50) name or were you just too lazy
> to pick a proper size? With the complete lack of data integrity in
> this schema, you will get one.
YUP.
schema? What schema? This is just a table (wait a minute, this isn't a
table) ! :)
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. IDENTITY cannot ever be a key
> **by definition**, which I would hope you have learned by now.
>
Cannot ever be a key'?
By what definition ?
Here is one I have read:
Definition: The primary key of a relational table uniquely identifies each
record in the table. It can either be a normal attribute that is guaranteed
to be unique (such as Social Security Number in a table with no more than
one record per person) or it can be generated by the DBMS (such as a
globally unique identifier, or GUID, in Microsoft SQL Server). Primary keys
may consist of a single attribute or multiple attributes in combination.
Does an Identity uniquely identify each record in the table?
Is it guaranteed to be unique? ( by definition)
Is it generated by the DBMS'?
Single attribute?

> CREATE TABLE VoyagePricing -- were you pricing the ships or the
> trips?
Why is that even a question and what does it have to do with the question?
Maybe I'm pricing the popcorn on the ship. What difference does it make?

> (ship_name VARCHAR(30) NOT NULL -- I would use a ship code
So would I, but I wanted to see the best way to get the name both from the
record that had the name and the Promo that didn't, but refered to the
record that had the name.

> CHECK (ship_name IN (..)),
> promo_name CHAR(10) DEFAULT 'Regular Price' NOT NULL,
Not really quite sure what you are doing here. There are 2 prices one
regular and one or more Promotional record that would display (instead of)
the regular price between the start and end dates (such as Chrismas in my
examples).

> trip_price DECIMAL(7,2) NOT NULL,
> start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> end_date DATETIME NOT NULL,
> CHECK (start_date < end_date),
> PRIMARY KEY (ship_name, start_date)
> );
> Notice the use of duration ranges. Promos are nested inside the
> regular fare ranges.
>
Not what I am trying to do. How does this handle multiple Promos'?
where is the Date Range for the Promos'

> That is really bad business! You can show them what they are saving
> with this query.
>
Bad Business? How about incorrect assessment of the question? or the
problem?

> SELECT ship_name, promo_name, trip_price
> FROM VoyagePricing AS P
> WHERE @.my_date BETWEEN start_date AND end_date;
> This will give you both the promo and regular prices. I assume that we
> give the customer the lower price.
>
Won't give them what I was trying to give them.

> SELECT ship_name, MIN(trip_price)
> FROM VoyagePricing
> WHERE @.my_date BETWEEN start_date AND end_date
> GROUP BY ship_name;
>
Also, won't give them what I was trying to give them. You have what I
needed above - how did you miss it' "If it is a promotion I don't want
to show the regular price and I would like it to say something like "Promo".
Where is that done with either of your examples'?
Ok, let me change the statement slightly.
"If the date that I am asking for is between any of the dates in the
Promotion records (row) for each Ship, display price for that row as well as
the ships name which you can get from the regular price record of that ship
and also pass back the word "Promo". If the date requested is not between
any of the dates in any of the Promotion records (row) for each ship, then
show the regular price and blank (null) instead of the word 'Promo'". In
any case, I should get 1 and only 1 record (row) back for each ship (either
a regular price or a promotional price)."

> You keep posting the worst code of anyone in this newsgroup.
Could be the case. If I was an expert, I wouldn't be asking questions.
<Can you
> get your boss to pay for a basic RDBMS course for you and the other
> programmers?
>
Already been there done that (many years ago). But I am also not an RDBMS
expert (as you may have gathered). And I am sure there are many ways to
skin a cat, as can be seen in this group. They can be done multiple ways
and still be right.

> A little over a year ago, I got to watch incompetent RDBMS programmers
> like you kill children in Africa by messing up a medical supply system.
Never been to Africa.
Tom|||"VC" <me@.here.com> wrote in message
news:XOCdnQobJZG1gpPeRVn-pw@.comcast.com...
> t - try this:
> declare @.dt datetime
> set @.dt = cast('12/18/05' as datetime)
> select
> case
> when @.dt between s2.startdate and s2.enddate then s2.ShipPricingID
> else s.ShipPricingID
> end as ShipPricingID,
> ShipName = substring(s.ShipName,1,20),
> case
> when @.dt between s2.startdate and s2.enddate then s2.PromoPriceID
> else s.PromoPriceID
> end as PromoPriceID,
> case
> when @.dt between s2.startdate and s2.enddate then s2.Price
> else s.Price
> end as Price,
> case
> when @.dt between s2.startdate and s2.enddate then s2.StartDate
> else s.StartDate
> end as StartDate,
> case
> when @.dt between s2.startdate and s2.enddate then s2.EndDate
> else s.EndDate
> end as EndDate,
> case
> when @.dt between s2.startdate and s2.enddate then 'PROMO'
> else NULL
> end as Promo
> from shipPricing s inner join shipPricing s2 on s.ShipPricingID =
> s2.PromoPriceID
Seems to do the job, except it shows Promotional records when it should show
Regular records and vice versa. I am sure it is just that one of the tests
is in reverse. Just need to look at it to see exactly what it does.
Just what I need, though.
Thanks a lot,
Tom
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:O84VJtZqFHA.2276@.TK2MSFTNGP10.phx.gbl...
> prices).
> the
> and
> ****************************************
**********************************
**
> ******************************
> Price
> --
> ****************************************
**********************************
**
> ****************************************
**
> date).
> --
> --
> --
> 2,050.00
> Select
>|||tshad wrote:
> How would I select one record and not the another related record in one
> Select statement?
> I have regular prices that I want to display on most days and promotional
> prices on particular days (christmas, summer etc) where I don't want to
> display the regular price if I am displaying the promotional price.
> I have a table that has 2 types of records in it.
> One record is a normal ticket price on a ship. The other record is a
> promotional price for that ship.
>
Can I ask why you don't have two tables - one with just the ship
pricing (for the regular price - surely every ship must have a regular
price), and then a seperate table, foreign keying to the first,
containing the promotions? It seems like you're actually trying to
squeeze two tables into one, even though they don't share any
attributes?
Damien

No comments:

Post a Comment