Help with a SQL query

I am selecting search results from the following view. The problem I have is, t5 (a junction) is returning multiple rows, which makes each search result show up several times. I just need one uniqe row from t5 ... (it has to be one row from just t5, not one row from the whole query). Can someone show me how I would accomplish this?

If anymore information is needed, let me know and I will post here. Thanks!

SELECT t1.*, t2.doc_type_description AS doc_type_description, t3.file_id AS file_id, t3.file_path AS file_path, t3.active AS active,
t5.soq_id AS soq_id, t5.soq_name AS soq_name
FROM dbo.tblSOQDocuments t1 INNER JOIN
dbo.tblSOQDocTypes t2 ON t1.doc_type_id = t2.doc_type_id INNER JOIN
dbo.tblSOQFiles t3 ON t1.doc_id = t3.doc_id INNER JOIN
dbo.jctSOQDocument t4 ON t1.doc_id = t4.doc_id INNER JOIN
dbo.tblSOQs t5 ON t4.soq_id = t5.soq_id
ORDER BY t2.doc_type_description, t5.soq_name, t1.doc_nameUse sub-queries.

instead of doing:

INNER JOIN dbo.tblSOQs AS t5 ON t4.soq_id = t5.soq_id

do:

INNER JOIN (SELECT soq_id, MIN(soq_name) FROM dbo.tblSOQs GROUP BY soq_id) AS t5 ON t4.soq_id = t5.soq_id|||For a unique row from t5, use:

INNER JOIN (SELECT DISTINCT soq_id, soq_name FROM dbo.tblSOQs) AS t5 ON t4.soq_id = t5.soq_id|||For a unique row from t5, use:

INNER JOIN (SELECT DISTINCT soq_id, soq_name FROM dbo.tblSOQs) AS t5 ON t4.soq_id = t5.soq_id

That will potentially return multiple rows which is what the original poster wants to avoid|||"I just need one uniqe row from t5 ..."

He does not specify "One unique soq_id", which is why I asked for clarification. "MIN(soq_name)" seems kind of arbitrary to me. Why includ soq_name in the result set if it is going to exclude some values?

...but perhaps that is what he wants.|||When I select from this view, it will return several rows. Potentially 2 rows for each result (each of these two rows carries different file information). One result from this query can potentially be associated with several soq_id's, but I just want the first one for each result.

Clear as mud?|||Then do an EXISTS...no?

Do you need data from both tables or just the one?|||I need data from all the tables that I have joined here ... the relationship will always exist.|||Well either SELECT DISTINCT which would eliminate duplicates (I know...too easy) or you want 1 row, WHERE the keys are the same, but you will have different values for those keys.

YOU have to decided what to do with those values. They exists.

Either USE MAX or MIN, ect, or s series of temp tables.

Either way, it has to be handled.

And YOU have to make the decision.sql

Help w/ query

I need help typing to pull info out of a SMS database. We are selecting info
from three table. we inner join the tables to get selected info out. where
we are having trouble is by limiting what is returned. is there a way to say
"if the row has a specific character(s) then the row will be ignored". the
way it is now gives us about ten time the data, because the machine name is
associated with multiple collections within sms.
On Tue, 1 Feb 2005 19:51:01 -0800, Greg wrote:
(snip)
>is there a way to say
>"if the row has a specific character(s) then the row will be ignored".
Hi Greg,
Yes, there is.
Unfortunately, I can't give more specific help, since you didn't post any
specific information about your requirements. In order to help you, I need
to know the structure of your tables (as CREATE TABLE statements, omitting
irrelevant columns but including all constraints and properties), some
sample data (as INSERT statements) and expected output.
Consult www.aspfaq.com/5006, then post back with more details if you need
further help.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help selecting Veritas products

I have two Windows Server 2003 servers. One is a domain controller and the
other has SQL Server. The tape drive is in the domain controller. Am I OK
with Backup Exec 10d for Windows Servers and a remote agent for SQL Server
for Windows Server?
ThanksHi
You could back up your database to disc (a network resource) and then you
would not need the remote agent.
John
"r042wal" wrote:
> I have two Windows Server 2003 servers. One is a domain controller and the
> other has SQL Server. The tape drive is in the domain controller. Am I OK
> with Backup Exec 10d for Windows Servers and a remote agent for SQL Server
> for Windows Server?
> Thanks
>
>|||r042wal wrote:
> I have two Windows Server 2003 servers. One is a domain controller and the
> other has SQL Server. The tape drive is in the domain controller. Am I OK
> with Backup Exec 10d for Windows Servers and a remote agent for SQL Server
> for Windows Server?
> Thanks
>
Don't use a backup agent to do SQL backups, they tend to be problematic
at best, and take longer to backup than a "normal" SQL backup. Just use
the SQL command BACKUP to dump the database contents to a disk file,
then backup that disk file using your tape backup software.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy, can the backup command in SQL be automated? I want to do a scheduled
backup nightly.
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uXSKJRmuGHA.3392@.TK2MSFTNGP04.phx.gbl...
> r042wal wrote:
>> I have two Windows Server 2003 servers. One is a domain controller and
>> the other has SQL Server. The tape drive is in the domain controller.
>> Am I OK with Backup Exec 10d for Windows Servers and a remote agent for
>> SQL Server for Windows Server?
>> Thanks
> Don't use a backup agent to do SQL backups, they tend to be problematic at
> best, and take longer to backup than a "normal" SQL backup. Just use the
> SQL command BACKUP to dump the database contents to a disk file, then
> backup that disk file using your tape backup software.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hi
If you right click the database in the treeview of Enterprise Manager and
choose the backup option, then at the bottom of the general tab is an option
to schedule the backup. Alternatively you can look at using a maintenance
plan which will also allow you to schedule tasks for multiple databases and
allows you to do other things like index reorganization. To create a new
maintenance plan check out the Management branch, right click Database
Maintenance Plans and choose New Maintenance Plan.
John
"r042wal" wrote:
> Tracy, can the backup command in SQL be automated? I want to do a scheduled
> backup nightly.
> Thanks
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:uXSKJRmuGHA.3392@.TK2MSFTNGP04.phx.gbl...
> > r042wal wrote:
> >> I have two Windows Server 2003 servers. One is a domain controller and
> >> the other has SQL Server. The tape drive is in the domain controller.
> >> Am I OK with Backup Exec 10d for Windows Servers and a remote agent for
> >> SQL Server for Windows Server?
> >>
> >> Thanks
> >
> > Don't use a backup agent to do SQL backups, they tend to be problematic at
> > best, and take longer to backup than a "normal" SQL backup. Just use the
> > SQL command BACKUP to dump the database contents to a disk file, then
> > backup that disk file using your tape backup software.
> >
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com
>
>|||r042wal wrote:
> Tracy, can the backup command in SQL be automated? I want to do a scheduled
> backup nightly.
>
Absolutely, just setup a job in SQL Agent that runs the necessary BACKUP
command(s). You might also have a look at a script I've written that I
use to do backups:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Help selecting Veritas products

I have two Windows Server 2003 servers. One is a domain controller and the
other has SQL Server. The tape drive is in the domain controller. Am I OK
with Backup Exec 10d for Windows Servers and a remote agent for SQL Server
for Windows Server?
ThanksHi
You could back up your database to disc (a network resource) and then you
would not need the remote agent.
John
"r042wal" wrote:

> I have two Windows Server 2003 servers. One is a domain controller and th
e
> other has SQL Server. The tape drive is in the domain controller. Am I O
K
> with Backup Exec 10d for Windows Servers and a remote agent for SQL Server
> for Windows Server?
> Thanks
>
>|||r042wal wrote:
> I have two Windows Server 2003 servers. One is a domain controller and th
e
> other has SQL Server. The tape drive is in the domain controller. Am I O
K
> with Backup Exec 10d for Windows Servers and a remote agent for SQL Server
> for Windows Server?
> Thanks
>
Don't use a backup agent to do SQL backups, they tend to be problematic
at best, and take longer to backup than a "normal" SQL backup. Just use
the SQL command BACKUP to dump the database contents to a disk file,
then backup that disk file using your tape backup software.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy, can the backup command in SQL be automated? I want to do a scheduled
backup nightly.
Thanks
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uXSKJRmuGHA.3392@.TK2MSFTNGP04.phx.gbl...
> r042wal wrote:
> Don't use a backup agent to do SQL backups, they tend to be problematic at
> best, and take longer to backup than a "normal" SQL backup. Just use the
> SQL command BACKUP to dump the database contents to a disk file, then
> backup that disk file using your tape backup software.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hi
If you right click the database in the treeview of Enterprise Manager and
choose the backup option, then at the bottom of the general tab is an option
to schedule the backup. Alternatively you can look at using a maintenance
plan which will also allow you to schedule tasks for multiple databases and
allows you to do other things like index reorganization. To create a new
maintenance plan check out the Management branch, right click Database
Maintenance Plans and choose New Maintenance Plan.
John
"r042wal" wrote:

> Tracy, can the backup command in SQL be automated? I want to do a schedul
ed
> backup nightly.
> Thanks
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:uXSKJRmuGHA.3392@.TK2MSFTNGP04.phx.gbl...
>
>|||r042wal wrote:
> Tracy, can the backup command in SQL be automated? I want to do a schedul
ed
> backup nightly.
>
Absolutely, just setup a job in SQL Agent that runs the necessary BACKUP
command(s). You might also have a look at a script I've written that I
use to do backups:
http://realsqlguy.com/twiki/bin/vie...realsqlguy.com

Help selecting the proper child record

Good Morning,
I have a person table with personID. I have a personRate table with
personID, rateID, and effectiveDate.
I need to select fields from personRate, but I want the fields from the
proper record.

I need the one child record that has the most current date of the largest
rateID.

For example a person may have many rate records. I need the record that has
the most current date of the largest rateID they have. Does that make
sense?

I am making a view that has data from both tables. I need to display the
most current rate info.

Any ideas? TIA ~ CKHi CK,

I kind of guessed on the DDL but something like the select statement below
(after the Creates and Inserts) should do it for you.

Create Table Person(
PersonID int identity(1,1) Primary Key,
PersonName varchar(50))

Create Table PersonRate(
RateID int identity(1,1) Primary Key,
PersonID int references Person(PersonID),
Rate decimal(9,2),
EffectiveDate smalldatetime)

insert Person(PersonName) values ('Fred')
insert Person(PersonName) values ('Barney')
insert Person(PersonName) values ('Wilma')

insert PersonRate(PersonID, Rate, EffectiveDate) values(1,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,12,'3/1/2005')

insert PersonRate(PersonID, Rate, EffectiveDate) values(2,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(2,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(2,12,'3/1/2005')

insert PersonRate(PersonID, Rate, EffectiveDate) values(3,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(3,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(3,12,'3/1/2005')

select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.Rate
from Person p
inner join (select pr.PersonID, pr.EffectiveDate, pr.RateID, pr.Rate
from PersonRate pr
inner join (select PersonID, Max(RateID) as MaxRateID
from PersonRate
group by PersonID) pr1
on pr.personid = pr1.personid
and pr.rateid = pr1.maxRateID) pp
on p.PersonID = pp.Personid

--
-Dick Christoph
"CK" <c_kettenbach@.hotmail.com> wrote in message
news:Z1CUf.58897$Jd.37489@.newssvr25.news.prodigy.n et...
> Good Morning,
> I have a person table with personID. I have a personRate table with
> personID, rateID, and effectiveDate.
> I need to select fields from personRate, but I want the fields from the
> proper record.
> I need the one child record that has the most current date of the largest
> rateID.
> For example a person may have many rate records. I need the record that
> has the most current date of the largest rateID they have. Does that make
> sense?
> I am making a view that has data from both tables. I need to display the
> most current rate info.
> Any ideas? TIA ~ CK|||See changes below. Also not every Person has a PersonRateRecord but I need
all the persons so I am thinking LEFT OUTER JOIN on PersonRate.

Thanks for the pointers!!

"DickChristoph" <dchristo99@.yahoo.com> wrote in message
news:XuEUf.18265$iR1.4137@.tornado.rdc-kc.rr.com...
> Hi CK,
> I kind of guessed on the DDL but something like the select statement below
> (after the Creates and Inserts) should do it for you.
> Create Table Person(
> PersonID int identity(1,1) Primary Key,
> PersonName varchar(50))
> Create Table PersonRate(
*****PersonRateID int identity(1,1) Primary Key,
***** RateID int references Rate(RateID),
> PersonID int references Person(PersonID),
> Rate decimal(9,2),
> EffectiveDate smalldatetime)

Create Table Rate(
RateID int identity(1,1) Primary Key,
RateType varchar(20))

> insert Person(PersonName) values ('Fred')
> insert Person(PersonName) values ('Barney')
> insert Person(PersonName) values ('Wilma')
>
> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,10,'1/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,11,'2/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,12,'3/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,10,'1/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,11,'2/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,12,'3/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,10,'1/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,11,'2/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,12,'3/1/2005')
>
> select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.Rate
> from Person p
> inner join (select pr.PersonID, pr.EffectiveDate, pr.RateID, pr.Rate
> from PersonRate pr
> inner join (select PersonID, Max(RateID) as MaxRateID
> from PersonRate
> group by PersonID) pr1
> on pr.personid = pr1.personid
> and pr.rateid = pr1.maxRateID) pp
> on p.PersonID = pp.Personid
> --
> -Dick Christoph
> "CK" <c_kettenbach@.hotmail.com> wrote in message
> news:Z1CUf.58897$Jd.37489@.newssvr25.news.prodigy.n et...
>> Good Morning,
>> I have a person table with personID. I have a personRate table with
>> personID, rateID, and effectiveDate.
>> I need to select fields from personRate, but I want the fields from the
>> proper record.
>>
>> I need the one child record that has the most current date of the largest
>> rateID.
>>
>> For example a person may have many rate records. I need the record that
>> has the most current date of the largest rateID they have. Does that
>> make sense?
>>
>> I am making a view that has data from both tables. I need to display the
>> most current rate info.
>>
>> Any ideas? TIA ~ CK
>>|||Hi CK

Alrighty then how about this. By the way I think the PersonRate.Rate column
is not where it should be. Probably it belongs in the Rate table but I don't
know what that column really means. You left it in PersonRate so I left it
in PersonRate.

Create Table Person(
PersonID int identity(1,1) Primary Key,
PersonName varchar(50))

Create Table Rate(
RateID int identity(1,1) Primary Key,
RateType varchar(20))

Create Table PersonRate(
PersonRateID int identity(1,1) Primary Key,
RateID int references Rate(RateID),
PersonID int references Person(PersonID),
Rate decimal(9,2),
EffectiveDate smalldatetime)

insert Person(PersonName) values ('Fred')
insert Person(PersonName) values ('Barney')
insert Person(PersonName) values ('Wilma')

insert Rate(RateType) values ('A')
insert Rate(RateType) values ('B')
insert Rate(RateType) values ('C')

--Fred
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(1,1,10,'1/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(2,1,11,'2/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(3,1,12,'3/1/2005')

--Barney
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(1,2,10,'1/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(2,2,11,'2/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(3,2,12,'3/1/2005')

--No PersonRate For Wilma

select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.RateType,
pp.Rate
from Person p
left outer join (select pr.PersonID, pr.EffectiveDate, pr.RateID,
r.RateType, pr.Rate
from PersonRate pr
inner join Rate r
on pr.RateID = r.RateID
inner join (select PersonID, Max(RateID) as MaxRateID
from PersonRate
group by PersonID) pr1
on pr.personid = pr1.personid
and pr.rateid = pr1.maxRateID) pp
on p.PersonID = pp.Personid

--
-Dick Christoph
dchristo@.mn.rr.com
612-724-9282
"CK" <c_kettenbach@.hotmail.com> wrote in message
news:5HEUf.62662$dW3.28210@.newssvr21.news.prodigy. com...
> See changes below. Also not every Person has a PersonRateRecord but I
> need all the persons so I am thinking LEFT OUTER JOIN on PersonRate.
> Thanks for the pointers!!
> "DickChristoph" <dchristo99@.yahoo.com> wrote in message
> news:XuEUf.18265$iR1.4137@.tornado.rdc-kc.rr.com...
>> Hi CK,
>>
>> I kind of guessed on the DDL but something like the select statement
>> below (after the Creates and Inserts) should do it for you.
>>
>> Create Table Person(
>> PersonID int identity(1,1) Primary Key,
>> PersonName varchar(50))
>>
>> Create Table PersonRate(
> *****PersonRateID int identity(1,1) Primary Key,
> ***** RateID int references Rate(RateID),
>> PersonID int references Person(PersonID),
>> Rate decimal(9,2),
>> EffectiveDate smalldatetime)
>>
> Create Table Rate(
> RateID int identity(1,1) Primary Key,
> RateType varchar(20))
>
>> insert Person(PersonName) values ('Fred')
>> insert Person(PersonName) values ('Barney')
>> insert Person(PersonName) values ('Wilma')
>>
>>
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,10,'1/1/2005')
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,11,'2/1/2005')
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,12,'3/1/2005')
>>
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,10,'1/1/2005')
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,11,'2/1/2005')
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,12,'3/1/2005')
>>
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,10,'1/1/2005')
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,11,'2/1/2005')
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,12,'3/1/2005')
>>
>>
>> select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.Rate
>> from Person p
>> inner join (select pr.PersonID, pr.EffectiveDate, pr.RateID, pr.Rate
>> from PersonRate pr
>> inner join (select PersonID, Max(RateID) as MaxRateID
>> from PersonRate
>> group by PersonID) pr1
>> on pr.personid = pr1.personid
>> and pr.rateid = pr1.maxRateID) pp
>> on p.PersonID = pp.Personid
>>
>> --
>> -Dick Christoph
>> "CK" <c_kettenbach@.hotmail.com> wrote in message
>> news:Z1CUf.58897$Jd.37489@.newssvr25.news.prodigy.n et...
>>> Good Morning,
>>> I have a person table with personID. I have a personRate table with
>>> personID, rateID, and effectiveDate.
>>> I need to select fields from personRate, but I want the fields from the
>>> proper record.
>>>
>>> I need the one child record that has the most current date of the
>>> largest rateID.
>>>
>>> For example a person may have many rate records. I need the record that
>>> has the most current date of the largest rateID they have. Does that
>>> make sense?
>>>
>>> I am making a view that has data from both tables. I need to display
>>> the most current rate info.
>>>
>>> Any ideas? TIA ~ CK
>>>
>>
>>|||Rate is a $ amount , rateType is string indicating what the $ amount is for.
Like a "raise", "offer" , "request". Just some entities this company uses.
Yes it is in the correct place. Thanks a lot. Max didn't give me the desired
result. I use SELECT TOP 1.

here's what worked. I added a couple other tables as i needed values from
them as well

SELECT vC.*, CR.EffectiveDate, rt.TypeName, jc.CategoryName,
CASE
WHEN CR.Rate IS NULL THEN CAST('None' AS char(4))
ELSE CAST(CR.Rate AS varchar(6))
END AS Rate
FROM vCandidates vC
LEFT OUTER JOIN CandidateRate CR
ON vC.CandidateID = CR.CandidateID
AND CR.CandidateRateID IN
(
SELECT TOP 1 CandidateRateID
FROM CandidateRate
WHERE RateTypeID < 4 AND CandidateID = CR.CandidateID
ORDER BY RateTypeID DESC, EffectiveDate DESC
)
LEFT OUTER JOIN RateType rt ON rt.RateTypeID = CR.RateTypeID
LEFT OUTER JOIN Innova.dbo.JobCategory jc ON jc.JobCatID = CR.JobCatID

"DickChristoph" <dchristo99@.yahoo.com> wrote in message
news:dYEUf.18266$iR1.4639@.tornado.rdc-kc.rr.com...
> Hi CK
> Alrighty then how about this. By the way I think the PersonRate.Rate
> column is not where it should be. Probably it belongs in the Rate table
> but I don't know what that column really means. You left it in PersonRate
> so I left it in PersonRate.
> Create Table Person(
> PersonID int identity(1,1) Primary Key,
> PersonName varchar(50))
> Create Table Rate(
> RateID int identity(1,1) Primary Key,
> RateType varchar(20))
> Create Table PersonRate(
> PersonRateID int identity(1,1) Primary Key,
> RateID int references Rate(RateID),
> PersonID int references Person(PersonID),
> Rate decimal(9,2),
> EffectiveDate smalldatetime)
>
> insert Person(PersonName) values ('Fred')
> insert Person(PersonName) values ('Barney')
> insert Person(PersonName) values ('Wilma')
> insert Rate(RateType) values ('A')
> insert Rate(RateType) values ('B')
> insert Rate(RateType) values ('C')
> --Fred
> insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
> values(1,1,10,'1/1/2005')
> insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
> values(2,1,11,'2/1/2005')
> insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
> values(3,1,12,'3/1/2005')
> --Barney
> insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
> values(1,2,10,'1/1/2005')
> insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
> values(2,2,11,'2/1/2005')
> insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
> values(3,2,12,'3/1/2005')
> --No PersonRate For Wilma
>
> select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.RateType,
> pp.Rate
> from Person p
> left outer join (select pr.PersonID, pr.EffectiveDate, pr.RateID,
> r.RateType, pr.Rate
> from PersonRate pr
> inner join Rate r
> on pr.RateID = r.RateID
> inner join (select PersonID, Max(RateID) as MaxRateID
> from PersonRate
> group by PersonID) pr1
> on pr.personid = pr1.personid
> and pr.rateid = pr1.maxRateID) pp
> on p.PersonID = pp.Personid
>
> --
> -Dick Christoph
> dchristo@.mn.rr.com
> 612-724-9282
> "CK" <c_kettenbach@.hotmail.com> wrote in message
> news:5HEUf.62662$dW3.28210@.newssvr21.news.prodigy. com...
>> See changes below. Also not every Person has a PersonRateRecord but I
>> need all the persons so I am thinking LEFT OUTER JOIN on PersonRate.
>>
>> Thanks for the pointers!!
>>
>> "DickChristoph" <dchristo99@.yahoo.com> wrote in message
>> news:XuEUf.18265$iR1.4137@.tornado.rdc-kc.rr.com...
>>> Hi CK,
>>>
>>> I kind of guessed on the DDL but something like the select statement
>>> below (after the Creates and Inserts) should do it for you.
>>>
>>> Create Table Person(
>>> PersonID int identity(1,1) Primary Key,
>>> PersonName varchar(50))
>>>
>>> Create Table PersonRate(
>> *****PersonRateID int identity(1,1) Primary Key,
>> ***** RateID int references Rate(RateID),
>>> PersonID int references Person(PersonID),
>>> Rate decimal(9,2),
>>> EffectiveDate smalldatetime)
>>>
>>
>> Create Table Rate(
>> RateID int identity(1,1) Primary Key,
>> RateType varchar(20))
>>
>>
>>> insert Person(PersonName) values ('Fred')
>>> insert Person(PersonName) values ('Barney')
>>> insert Person(PersonName) values ('Wilma')
>>>
>>>
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,10,'1/1/2005')
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,11,'2/1/2005')
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,12,'3/1/2005')
>>>
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,10,'1/1/2005')
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,11,'2/1/2005')
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,12,'3/1/2005')
>>>
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,10,'1/1/2005')
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,11,'2/1/2005')
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,12,'3/1/2005')
>>>
>>>
>>> select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.Rate
>>> from Person p
>>> inner join (select pr.PersonID, pr.EffectiveDate, pr.RateID, pr.Rate
>>> from PersonRate pr
>>> inner join (select PersonID, Max(RateID) as MaxRateID
>>> from PersonRate
>>> group by PersonID) pr1
>>> on pr.personid = pr1.personid
>>> and pr.rateid = pr1.maxRateID) pp
>>> on p.PersonID = pp.Personid
>>>
>>> --
>>> -Dick Christoph
>>> "CK" <c_kettenbach@.hotmail.com> wrote in message
>>> news:Z1CUf.58897$Jd.37489@.newssvr25.news.prodigy.n et...
>>>> Good Morning,
>>>> I have a person table with personID. I have a personRate table with
>>>> personID, rateID, and effectiveDate.
>>>> I need to select fields from personRate, but I want the fields from the
>>>> proper record.
>>>>
>>>> I need the one child record that has the most current date of the
>>>> largest rateID.
>>>>
>>>> For example a person may have many rate records. I need the record that
>>>> has the most current date of the largest rateID they have. Does that
>>>> make sense?
>>>>
>>>> I am making a view that has data from both tables. I need to display
>>>> the most current rate info.
>>>>
>>>> Any ideas? TIA ~ CK
>>>>
>>>
>>>
>>
>>

Help selecting an alias

Hello guys, I need help in something as I don't know if it is possible
what I want.
I have a select like this...

SELECT Cod1 as SQL, Cod2 as Oracle FROM table

and I need to sort by alias SQL or Oracle as the select is composed
dinamically so it could be either Cod1 as SQL or Cod2 as SQL and the
user needs to filter the data using SQL or ORACLE.

I need something like this:

SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE SQL = 'one' AND
Oracle = 'two'

Any ideas?

Thank youHi

I don't understand what you mean. Is the column SQL, SQL Server Code and the
column Oracle, Oracle Code? If so why would the alias SQL be 'one' and the
alias Oracle be 'two'

> SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE SQL = 'one' AND
> Oracle = 'two'
As written I assume you mean something like

Select SQL, Oracle from
(SELECT Cod1 as SQL, Cod2 as Oracle FROM table) ss
WHERE SQL = 'one' AND Oracle = 'two'

Which is the same as

SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE Cod1 = 'one' AND Cod2 =
'two'

neither of which appear to be too useful.

Please post some DDL and expected output.
--
-Dick Christoph
"Pumkin" <PopClaudia@.gmail.com> wrote in message
news:1143202849.620253.156550@.z34g2000cwc.googlegr oups.com...
> Hello guys, I need help in something as I don't know if it is possible
> what I want.
> I have a select like this...
> SELECT Cod1 as SQL, Cod2 as Oracle FROM table
> and I need to sort by alias SQL or Oracle as the select is composed
> dinamically so it could be either Cod1 as SQL or Cod2 as SQL and the
> user needs to filter the data using SQL or ORACLE.
> I need something like this:
> SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE SQL = 'one' AND
> Oracle = 'two'
> Any ideas?
> Thank you|||On 24 Mar 2006 04:20:49 -0800, Pumkin wrote:

>Hello guys, I need help in something as I don't know if it is possible
>what I want.
>I have a select like this...
>SELECT Cod1 as SQL, Cod2 as Oracle FROM table
>and I need to sort by alias SQL or Oracle as the select is composed
>dinamically so it could be either Cod1 as SQL or Cod2 as SQL and the
>user needs to filter the data using SQL or ORACLE.
>I need something like this:
>SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE SQL = 'one' AND
>Oracle = 'two'
>Any ideas?
>Thank you

Hi Pumkin,

You can't reference an alias directly (except in the ORDER BY clause),
but you can do it indirectly if you use a derived table:

SELECT SQL, Oracle
FROM (SELECT Cod1 AS SQL, Cod2 AS Oracle
FROM YourTable) AS Der
WHERE SQL = 'one'
AND Oracle = 'two'
ORDER BY SQL ASC, Oracle DESC

> the select is composed
>dinamically

Please read the following article very carefully:

http://www.sommarskog.se/dynamic_sql.html

--
Hugo Kornelis, SQL Server MVP|||Pumkin (PopClaudia@.gmail.com) writes:
> Hello guys, I need help in something as I don't know if it is possible
> what I want.
> I have a select like this...
> SELECT Cod1 as SQL, Cod2 as Oracle FROM table
> and I need to sort by alias SQL or Oracle as the select is composed
> dinamically so it could be either Cod1 as SQL or Cod2 as SQL and the
> user needs to filter the data using SQL or ORACLE.
> I need something like this:
> SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE SQL = 'one' AND
> Oracle = 'two'

I think I would have needed to have more information about where
this query appears.

What you can do is:

SELECT SQL, Oracle
FROM (SELECT Cod1 AS SQL, Cod2 AS Oracle FROM tbl) AS d
WHERE SQL = 'one' AND Oracle = 'two'

The thiing in a parentheses in a derived table. You can use a derived
table for several purposes. Here the purpose is to define queries that
are defined in the rest in the query.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank You all... Your idea with the table within the table really
worked.

SELECT SQL, Oracle
FROM (SELECT Cod1 AS SQL, Cod2 AS Oracle FROM tbl) AS d
WHERE SQL = 'one' AND Oracle = 'two'
That is the final select that worked for me.

help selecting all rows that contains no null value

I have data on MS SQL Server that have over 60 columns, I would like to
select 30 of these columns that may or may not contain NULL and I don't
want to write out all 30 columns and check for IN NOT NULL. Does anyone
know how to do that?Not trying to be harsh, but... start typing. :)
Is this a recurring problem, or are you shooting for a one time
solution? If one-time, the easiest way to do it is to just write the
SQL query that you're attempting to avoid. If you're trying to develop
some sort of administrative tool to help you validate data, you could
script something that uses dynamic SQL (using the syscolumns table).
That's not something that I would advocate giving to the average user.
Stu
timhz...@.gmail.com wrote:
> I have data on MS SQL Server that have over 60 columns, I would like to
> select 30 of these columns that may or may not contain NULL and I don't
> want to write out all 30 columns and check for IN NOT NULL. Does anyone
> know how to do that?|||You must perform each IS NOT NULL test in the query. However, you can
save some typing. This will generate a query to get you started, just
copy the results.
declare @.tbl varchar(50)
set @.tbl = 'Categories'
SELECT CASE WHEN C.ORDINAL_POSITION =
(select min(ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS C2
where C2.TABLE_NAME = @.tbl
and C2.IS_NULLABLE = 'YES')
THEN 'SELECT * FROM ' + C.TABLE_NAME +
char(13) + CHAR(10) +
' WHERE '
ELSE ' AND '
END +
C.COLUMN_NAME + 'IS NOT NULL' + char(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = @.tbl
AND C.IS_NULLABLE = 'YES'
ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.ORDINAL_POSITION
On 29 Jun 2006 08:48:16 -0700, timhzhou@.gmail.com wrote:

>I have data on MS SQL Server that have over 60 columns, I would like to
>select 30 of these columns that may or may not contain NULL and I don't
>want to write out all 30 columns and check for IN NOT NULL. Does anyone
>know how to do that?|||No, there are no lazy shortcuts in T-SQL to select "a set of columns".
But, there are lazy shortcuts to generating the list so that you can create
a valid and reasonable T-SQL statement more quickly. What are you using,
6.5, 7.0, 2000, 2005? In Query Analyzer or Management Studio, when you
expand a table and you see a folder called columns, drag it to the query
window. Voila, like magic, huh?
Laziness is not enough of a reason to use SELECT * (or s a similar
alternative).
A
<timhzhou@.gmail.com> wrote in message
news:1151596096.534039.98000@.p79g2000cwp.googlegroups.com...
>I have data on MS SQL Server that have over 60 columns, I would like to
> select 30 of these columns that may or may not contain NULL and I don't
> want to write out all 30 columns and check for IN NOT NULL. Does anyone
> know how to do that?
>|||before you begin anything .. you might want to consider clean up the
data to update those columnes with a default value.
ie.
update table
set column = ''
where column is null
possibly put them in a temporary table?
timhzhou@.gmail.com wrote:
> I have data on MS SQL Server that have over 60 columns, I would like to
> select 30 of these columns that may or may not contain NULL and I don't
> want to write out all 30 columns and check for IN NOT NULL. Does anyone
> know how to do that?|||Contrary to Developer opinion, sometimes NULL is an appropriate value.
(Albieit, not as often as it is used just because folks don't take time to
understand the implications.)
A question I posit is: How many responses are there to a Yes/No question?
The correct response is Four. Yes, No, Not Answered, Not Applicable.
The use of the data determines whether or not there is a distinction between
the last two. For example, if I'm analyzing survey results, I wouldn't want
to confound the analysis by combining Not Answered and Not Applicable.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"BurgerKING" <syi916@.gmail.com> wrote in message
news:1151597871.024544.185460@.b68g2000cwa.googlegroups.com...
> before you begin anything .. you might want to consider clean up the
> data to update those columnes with a default value.
> ie.
> update table
> set column = ''
> where column is null
> possibly put them in a temporary table?
> timhzhou@.gmail.com wrote:
>

help selecting

Hi,
I have the following tables, and I would like to filter the categories
to a specific department (and then supplier). Something like an
intersection.
How can I do it? I'm new to SQL, especially with joins, so are the
following 2 joins correct?
1) Filtering to Department can be easy:
SELECT Category.CategoryId, Category.CategoryName FROM Category
JOIN Product ON Product.CategoryId = Category.CategoryId
WHERE Product.DepartmentId = @.DepartmentId
2) Adding a filtering to Supplier and I'm lost...
SELECT Category.CategoryId, Category.CategoryName FROM Category
JOIN Product ON Product.CategoryId = Category.CategoryId
JOIN ProductSupplier ON ProductSupplier.ProductId = Product.ProductId
WHERE (Product.DepartmentId = @.DepartmentId)
AND (ProductSupplier.SupplierId = @.SupplierId)
table Product
ProductId: PK
CategoryId: FK
DepartmentId: FK
table Category
CategoryId: PK
CategoryName
table Department
DepartmentId: PK
table ProductSupplier
ProductId, SupplierId: combined PK
table Supplier
SupplierId
Explanation: a category can be found in one department, and also in
another department, example is category Basin can be in department
Plastic ware and also in department Aluminium ware. Some departments do
not necessarily have a category, example is Department Plastic ware do
not have category Boy short.
If the table are poorly designed, just let me know, I can still change
them now before it's too late.
Thanks for the help--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I don't believe you should have the DepartmentID in the Products table.
You should have an intersection table for Departments & Products, as you
do in the table ProductSupplier.
CREATE TABLE Product (
ProductID INTEGER NOT NULL PRIMARY KEY ,
CategoryID INTEGER NOT NULL
REFERENCES Category
ON UPDATE CASCADE
)
CREATE TABLE DepartmentProducts (
DepartmentID INTEGER NOT NULL
REFERENCES Departments
ON DELETE CASCADE ,
ProductID INTEGER NOT NULL
REFERENCES Products
ON DELETE CASCADE ,
CONSTRAINT PK_DP PRIMARY KEY (DepartmentID, ProductID)
)
Perhaps:
SELECT C.CategoryId, C.CategoryName
FROM Category As C
INNER JOIN
Product As P ON C.CategorID = P.CategorID
INNER JOIN
ProductSupplier AS PS ON PS.ProductId = P.ProductId
INNER JOIN
DepartmentProducts As DP ON DP.ProductID = P.ProductID
WHERE DP.DepartmentId = @.DepartmentId
AND PS.SupplierId = @.SupplierId
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAi3i4echKqOuFEgEQJsCwCg0uoaQoPF+YzN
qSw63TfoergZKDEAnjss
6/qYpDxxxrlemVg9YppKsJIj
=g7sR
--END PGP SIGNATURE--
Michael Wong wrote:
> Hi,
> I have the following tables, and I would like to filter the categories
> to a specific department (and then supplier). Something like an
> intersection.
> How can I do it? I'm new to SQL, especially with joins, so are the
> following 2 joins correct?
> 1) Filtering to Department can be easy:
> SELECT Category.CategoryId, Category.CategoryName FROM Category
> JOIN Product ON Product.CategoryId = Category.CategoryId
> WHERE Product.DepartmentId = @.DepartmentId
> 2) Adding a filtering to Supplier and I'm lost...
> SELECT Category.CategoryId, Category.CategoryName FROM Category
> JOIN Product ON Product.CategoryId = Category.CategoryId
> JOIN ProductSupplier ON ProductSupplier.ProductId = Product.ProductId
> WHERE (Product.DepartmentId = @.DepartmentId)
> AND (ProductSupplier.SupplierId = @.SupplierId)
>
> table Product
> ProductId: PK
> CategoryId: FK
> DepartmentId: FK
> table Category
> CategoryId: PK
> CategoryName
> table Department
> DepartmentId: PK
> table ProductSupplier
> ProductId, SupplierId: combined PK
> table Supplier
> SupplierId
>
> Explanation: a category can be found in one department, and also in
> another department, example is category Basin can be in department
> Plastic ware and also in department Aluminium ware. Some departments do
> not necessarily have a category, example is Department Plastic ware do
> not have category Boy short.
> If the table are poorly designed, just let me know, I can still change
> them now before it's too late.
> Thanks for the help|||Hi MGFoster,
So what you suggest is that a product can be part of multiple departments.
I have initially designed a product to be also part of multiple
categories, but then I simplifed it to be only part of one single
category. Maybe I'll have to think more carefullt about it again.
Thank you for the quick reply.
MGFoster wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> I don't believe you should have the DepartmentID in the Products table.
> You should have an intersection table for Departments & Products, as you
> do in the table ProductSupplier.
> CREATE TABLE Product (
> ProductID INTEGER NOT NULL PRIMARY KEY ,
> CategoryID INTEGER NOT NULL
> REFERENCES Category
> ON UPDATE CASCADE
> )
> CREATE TABLE DepartmentProducts (
> DepartmentID INTEGER NOT NULL
> REFERENCES Departments
> ON DELETE CASCADE ,
> ProductID INTEGER NOT NULL
> REFERENCES Products
> ON DELETE CASCADE ,
> CONSTRAINT PK_DP PRIMARY KEY (DepartmentID, ProductID)
> )
> Perhaps:
> SELECT C.CategoryId, C.CategoryName
> FROM Category As C
> INNER JOIN
> Product As P ON C.CategorID = P.CategorID
> INNER JOIN
> ProductSupplier AS PS ON PS.ProductId = P.ProductId
> INNER JOIN
> DepartmentProducts As DP ON DP.ProductID = P.ProductID
> WHERE DP.DepartmentId = @.DepartmentId
> AND PS.SupplierId = @.SupplierId
>

Help Required - Selecting the last transaction for a client..

Hi all,
Happy New Year!!!
I have a table that has the following fields:
Client, Date
Data:
Microsoft, 1/1/05
Microsoft, 2/2/05
IBM, 3/8/05
HP, 2/4/05
HP, 2/5/05
How do I construct a query so that the results are:
Microsoft, 2/2/05
IBM, 3/8/05
HP, 2/5/05
Thus it is only giving me the last transaction or most recent transaction
for each client.
Your help is greatly appreciated.
ClintClint (ccpatriot12@.yahoo.com) writes:
> I have a table that has the following fields:
> Client, Date
> Data:
> Microsoft, 1/1/05
> Microsoft, 2/2/05
> IBM, 3/8/05
> HP, 2/4/05
> HP, 2/5/05
> How do I construct a query so that the results are:
> Microsoft, 2/2/05
> IBM, 3/8/05
> HP, 2/5/05
> Thus it is only giving me the last transaction or most recent transaction
> for each client.
SELECT Client, MAX(Date)
FROM tbl
GROUP BY Client
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks!
Worked perfectly.