Select Distinct

[Cary R]

I've noticed there's been a lot of questions on the select distinct statement,
and in many tries, I can't figure out how to use this correctly in my scenario.

I currently have 2 DBs - one that lists products and one that lists all items
that have been signed out (or in) at any time. This is for inventory purposes.
I am trying to create an HTML table that lists each of the items only once
with the last time they've been signed out/in or blank if they've never been.

My code currently is
SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS, SIGNOUT.RACFID,
SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN WEBSRV.EQUIPMNT
ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
ORDER BY SIGNOUT.DATEOUT DESC

This code still lists items that have been signed out/in more than once,
multiple times. I've tried using sub-queries, but I'm not quite sure of
the exact syntax to make this work correctly.

Hopefully this is enough information. Thanks to anyone who can help!
Cary

[Daniel Reber]

try this:

select
e.serialnum,
e.[name]
s.status,
s.racfid,
s.dateout,
s.datein,
from equipmnt
right join (select
max(dateout) dateout,
serialnum,
status,
racfid,
datein
from signout
group by serialnum,racfid,
status, datein ) s on s.serialnum = e.serialnum
order by dateout desc

you may have to change the group by in the derived table to get the data the
way you want it.

HTH

Daniel Reber
Datamasters, Inc

"Cary R" <cary.rotman@intria.com> wrote in message
news:3bab6314$1@news.devx.com...
>
> I've noticed there's been a lot of questions on the select distinct
statement,
> and in many tries, I can't figure out how to use this correctly in my
scenario.
>
> I currently have 2 DBs - one that lists products and one that lists all
items
> that have been signed out (or in) at any time. This is for inventory
purposes.
> I am trying to create an HTML table that lists each of the items only
once
> with the last time they've been signed out/in or blank if they've never
been.
>
> My code currently is
> SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,
SIGNOUT.RACFID,
> SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
WEBSRV.EQUIPMNT
> ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
> ORDER BY SIGNOUT.DATEOUT DESC
>
> This code still lists items that have been signed out/in more than once,
> multiple times. I've tried using sub-queries, but I'm not quite sure of
> the exact syntax to make this work correctly.
>
> Hopefully this is enough information. Thanks to anyone who can help!
> Cary
>

[Daniel Reber]

I forgot the comma after e.[name]

sorry.

"Daniel Reber" <dreber@dminconline.com> wrote in message
news:3bab6af0@news.devx.com...
> try this:
>
> select
> e.serialnum,
> e.[name]
> s.status,
> s.racfid,
> s.dateout,
> s.datein,
> from equipmnt
> right join (select
> max(dateout) dateout,
> serialnum,
> status,
> racfid,
> datein
> from signout
> group by serialnum,racfid,
> status, datein ) s on s.serialnum = e.serialnum
> order by dateout desc
>
> you may have to change the group by in the derived table to get the data
the
> way you want it.
>
> HTH
>
> Daniel Reber
> Datamasters, Inc
>
> "Cary R" <cary.rotman@intria.com> wrote in message
> news:3bab6314$1@news.devx.com...
> >
> > I've noticed there's been a lot of questions on the select distinct
> statement,
> > and in many tries, I can't figure out how to use this correctly in my
> scenario.
> >
> > I currently have 2 DBs - one that lists products and one that lists all
> items
> > that have been signed out (or in) at any time. This is for inventory
> purposes.
> > I am trying to create an HTML table that lists each of the items only
> once
> > with the last time they've been signed out/in or blank if they've never
> been.
> >
> > My code currently is
> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,
> SIGNOUT.RACFID,
> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
> WEBSRV.EQUIPMNT
> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
> > ORDER BY SIGNOUT.DATEOUT DESC
> >
> > This code still lists items that have been signed out/in more than once,
> > multiple times. I've tried using sub-queries, but I'm not quite sure of
> > the exact syntax to make this work correctly.
> >
> > Hopefully this is enough information. Thanks to anyone who can help!
> > Cary
> >
>
>

[Daniel Reber]

I also added a comma after s.datein by mistake.
I think that I need more coffee.


"Daniel Reber" <dreber@dminconline.com> wrote in message
news:3bab6af0@news.devx.com...
> try this:
>
> select
> e.serialnum,
> e.[name]
> s.status,
> s.racfid,
> s.dateout,
> s.datein,
> from equipmnt
> right join (select
> max(dateout) dateout,
> serialnum,
> status,
> racfid,
> datein
> from signout
> group by serialnum,racfid,
> status, datein ) s on s.serialnum = e.serialnum
> order by dateout desc
>
> you may have to change the group by in the derived table to get the data
the
> way you want it.
>
> HTH
>
> Daniel Reber
> Datamasters, Inc
>
> "Cary R" <cary.rotman@intria.com> wrote in message
> news:3bab6314$1@news.devx.com...
> >
> > I've noticed there's been a lot of questions on the select distinct
> statement,
> > and in many tries, I can't figure out how to use this correctly in my
> scenario.
> >
> > I currently have 2 DBs - one that lists products and one that lists all
> items
> > that have been signed out (or in) at any time. This is for inventory
> purposes.
> > I am trying to create an HTML table that lists each of the items only
> once
> > with the last time they've been signed out/in or blank if they've never
> been.
> >
> > My code currently is
> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,
> SIGNOUT.RACFID,
> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
> WEBSRV.EQUIPMNT
> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
> > ORDER BY SIGNOUT.DATEOUT DESC
> >
> > This code still lists items that have been signed out/in more than once,
> > multiple times. I've tried using sub-queries, but I'm not quite sure of
> > the exact syntax to make this work correctly.
> >
> > Hopefully this is enough information. Thanks to anyone who can help!
> > Cary
> >
>
>

[Cary R]

Thanx a lot for the quick response, however I'm still facing the same problems.


My main problem still exists in the duplication of data. For example, say
there are items A, B, C, D within the equipment database. Now, item A and
B have both been signed out, they appear fine. However, say C has been signed
out, signed in, then signed back out again, all 3 occurences of the C item
signout are visible on the page. (By changing Right Join to Full outer join,
I was able to see the D item with the table left blank as desired - I don't
know why the right join didn't work this time.)

My goal is to somehow use the distinct command to only show the last occurence
of each item. It is very likely that your code was really close, however
I've been workin on this one line for a while now, and I seem to always end
up with the same result.

Thanx again,
Cary


"Daniel Reber" <dreber@dminconline.com> wrote:
>I also added a comma after s.datein by mistake.
>I think that I need more coffee.
>
>
>"Daniel Reber" <dreber@dminconline.com> wrote in message
>news:3bab6af0@news.devx.com...
>> try this:
>>
>> select
>> e.serialnum,
>> e.[name]
>> s.status,
>> s.racfid,
>> s.dateout,
>> s.datein,
>> from equipmnt
>> right join (select
>> max(dateout) dateout,
>> serialnum,
>> status,
>> racfid,
>> datein
>> from signout
>> group by serialnum,racfid,
>> status, datein ) s on s.serialnum = e.serialnum
>> order by dateout desc
>>
>> you may have to change the group by in the derived table to get the data
>the
>> way you want it.
>>
>> HTH
>>
>> Daniel Reber
>> Datamasters, Inc
>>
>> "Cary R" <cary.rotman@intria.com> wrote in message
>> news:3bab6314$1@news.devx.com...
>> >
>> > I've noticed there's been a lot of questions on the select distinct
>> statement,
>> > and in many tries, I can't figure out how to use this correctly in my
>> scenario.
>> >
>> > I currently have 2 DBs - one that lists products and one that lists
all
>> items
>> > that have been signed out (or in) at any time. This is for inventory
>> purposes.
>> > I am trying to create an HTML table that lists each of the items only
>> once
>> > with the last time they've been signed out/in or blank if they've never
>> been.
>> >
>> > My code currently is
>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,
>> SIGNOUT.RACFID,
>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
>> WEBSRV.EQUIPMNT
>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
>> > ORDER BY SIGNOUT.DATEOUT DESC
>> >
>> > This code still lists items that have been signed out/in more than once,
>> > multiple times. I've tried using sub-queries, but I'm not quite sure
of
>> > the exact syntax to make this work correctly.
>> >
>> > Hopefully this is enough information. Thanks to anyone who can help!
>> > Cary
>> >
>>
>>
>
>

[Daniel Reber]

what is the unique id for the signout table?

"Cary R" <cary.rotman@intria.com> wrote in message
news:3bab8599$1@news.devx.com...
>
> Thanx a lot for the quick response, however I'm still facing the same
problems.
>
>
> My main problem still exists in the duplication of data. For example, say
> there are items A, B, C, D within the equipment database. Now, item A and
> B have both been signed out, they appear fine. However, say C has been
signed
> out, signed in, then signed back out again, all 3 occurences of the C item
> signout are visible on the page. (By changing Right Join to Full outer
join,
> I was able to see the D item with the table left blank as desired - I
don't
> know why the right join didn't work this time.)
>
> My goal is to somehow use the distinct command to only show the last
occurence
> of each item. It is very likely that your code was really close, however
> I've been workin on this one line for a while now, and I seem to always
end
> up with the same result.
>
> Thanx again,
> Cary
>
>
> "Daniel Reber" <dreber@dminconline.com> wrote:
> >I also added a comma after s.datein by mistake.
> >I think that I need more coffee.
> >
> >
> >"Daniel Reber" <dreber@dminconline.com> wrote in message
> >news:3bab6af0@news.devx.com...
> >> try this:
> >>
> >> select
> >> e.serialnum,
> >> e.[name]
> >> s.status,
> >> s.racfid,
> >> s.dateout,
> >> s.datein,
> >> from equipmnt
> >> right join (select
> >> max(dateout) dateout,
> >> serialnum,
> >> status,
> >> racfid,
> >> datein
> >> from signout
> >> group by serialnum,racfid,
> >> status, datein ) s on s.serialnum = e.serialnum
> >> order by dateout desc
> >>
> >> you may have to change the group by in the derived table to get the
data
> >the
> >> way you want it.
> >>
> >> HTH
> >>
> >> Daniel Reber
> >> Datamasters, Inc
> >>
> >> "Cary R" <cary.rotman@intria.com> wrote in message
> >> news:3bab6314$1@news.devx.com...
> >> >
> >> > I've noticed there's been a lot of questions on the select distinct
> >> statement,
> >> > and in many tries, I can't figure out how to use this correctly in my
> >> scenario.
> >> >
> >> > I currently have 2 DBs - one that lists products and one that lists
> all
> >> items
> >> > that have been signed out (or in) at any time. This is for inventory
> >> purposes.
> >> > I am trying to create an HTML table that lists each of the items
only
> >> once
> >> > with the last time they've been signed out/in or blank if they've
never
> >> been.
> >> >
> >> > My code currently is
> >> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,
> >> SIGNOUT.RACFID,
> >> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
> >> WEBSRV.EQUIPMNT
> >> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
> >> > ORDER BY SIGNOUT.DATEOUT DESC
> >> >
> >> > This code still lists items that have been signed out/in more than
once,
> >> > multiple times. I've tried using sub-queries, but I'm not quite sure
> of
> >> > the exact syntax to make this work correctly.
> >> >
> >> > Hopefully this is enough information. Thanks to anyone who can help!
> >> > Cary
> >> >
> >>
> >>
> >
> >
>

[Cary R]

>
>Thanx a lot for the quick response, however I'm still facing the same problems.
>
>
>My main problem still exists in the duplication of data. For example, say
>there are items A, B, C, D within the equipment database. Now, item A and
>B have both been signed out, they appear fine. However, say C has been
signed
>out, signed in, then signed back out again, all 3 occurences of the C item
>signout are visible on the page. (By changing Right Join to Full outer
join,
>I was able to see the D item with the table left blank as desired - I don't
>know why the right join didn't work this time.)
>
>My goal is to somehow use the distinct command to only show the last occurence
>of each item. It is very likely that your code was really close, however
>I've been workin on this one line for a while now, and I seem to always
end up with the same result.

Maybe, to make it more simple...If I were just to type: select distinct e.serialnum,
then it shows up correctly (obviously without other data), however then I
type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and so on, the
distinct command no longer functions as I want it to.
>
>Thanx again,
>Cary
>
>
>"Daniel Reber" <dreber@dminconline.com> wrote:
>>I also added a comma after s.datein by mistake.
>>I think that I need more coffee.
>>
>>
>>"Daniel Reber" <dreber@dminconline.com> wrote in message
>>news:3bab6af0@news.devx.com...
>>> try this:
>>>
>>> select
>>> e.serialnum,
>>> e.[name]
>>> s.status,
>>> s.racfid,
>>> s.dateout,
>>> s.datein,
>>> from equipmnt
>>> right join (select
>>> max(dateout) dateout,
>>> serialnum,
>>> status,
>>> racfid,
>>> datein
>>> from signout
>>> group by serialnum,racfid,
>>> status, datein ) s on s.serialnum = e.serialnum
>>> order by dateout desc
>>>
>>> you may have to change the group by in the derived table to get the data
>>the
>>> way you want it.
>>>
>>> HTH
>>>
>>> Daniel Reber
>>> Datamasters, Inc
>>>
>>> "Cary R" <cary.rotman@intria.com> wrote in message
>>> news:3bab6314$1@news.devx.com...
>>> >
>>> > I've noticed there's been a lot of questions on the select distinct
>>> statement,
>>> > and in many tries, I can't figure out how to use this correctly in
my
>>> scenario.
>>> >
>>> > I currently have 2 DBs - one that lists products and one that lists
>all
>>> items
>>> > that have been signed out (or in) at any time. This is for inventory
>>> purposes.
>>> > I am trying to create an HTML table that lists each of the items only
>>> once
>>> > with the last time they've been signed out/in or blank if they've never
>>> been.
>>> >
>>> > My code currently is
>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,
>>> SIGNOUT.RACFID,
>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
>>> WEBSRV.EQUIPMNT
>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
>>> > ORDER BY SIGNOUT.DATEOUT DESC
>>> >
>>> > This code still lists items that have been signed out/in more than
once,
>>> > multiple times. I've tried using sub-queries, but I'm not quite sure
>of
>>> > the exact syntax to make this work correctly.
>>> >
>>> > Hopefully this is enough information. Thanks to anyone who can help!
>>> > Cary
>>> >
>>>
>>>
>>
>>
>

[Cary R]

Serialnum for both

"Cary R" <cary.rotman@intria.com> wrote:
>
>
>>
>>Thanx a lot for the quick response, however I'm still facing the same problems.
>>
>>
>>My main problem still exists in the duplication of data. For example,
say
>>there are items A, B, C, D within the equipment database. Now, item A and
>>B have both been signed out, they appear fine. However, say C has been
>signed
>>out, signed in, then signed back out again, all 3 occurences of the C item
>>signout are visible on the page. (By changing Right Join to Full outer
>join,
>>I was able to see the D item with the table left blank as desired - I don't
>>know why the right join didn't work this time.)
>>
>>My goal is to somehow use the distinct command to only show the last occurence
>>of each item. It is very likely that your code was really close, however
>>I've been workin on this one line for a while now, and I seem to always
>end up with the same result.
>
>Maybe, to make it more simple...If I were just to type: select distinct
e.serialnum,
>then it shows up correctly (obviously without other data), however then
I
>type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and so on, the
>distinct command no longer functions as I want it to.
>>
>>Thanx again,
>>Cary
>>
>>
>>"Daniel Reber" <dreber@dminconline.com> wrote:
>>>I also added a comma after s.datein by mistake.
>>>I think that I need more coffee.
>>>
>>>
>>>"Daniel Reber" <dreber@dminconline.com> wrote in message
>>>news:3bab6af0@news.devx.com...
>>>> try this:
>>>>
>>>> select
>>>> e.serialnum,
>>>> e.[name]
>>>> s.status,
>>>> s.racfid,
>>>> s.dateout,
>>>> s.datein,
>>>> from equipmnt
>>>> right join (select
>>>> max(dateout) dateout,
>>>> serialnum,
>>>> status,
>>>> racfid,
>>>> datein
>>>> from signout
>>>> group by serialnum,racfid,
>>>> status, datein ) s on s.serialnum = e.serialnum
>>>> order by dateout desc
>>>>
>>>> you may have to change the group by in the derived table to get the
data
>>>the
>>>> way you want it.
>>>>
>>>> HTH
>>>>
>>>> Daniel Reber
>>>> Datamasters, Inc
>>>>
>>>> "Cary R" <cary.rotman@intria.com> wrote in message
>>>> news:3bab6314$1@news.devx.com...
>>>> >
>>>> > I've noticed there's been a lot of questions on the select distinct
>>>> statement,
>>>> > and in many tries, I can't figure out how to use this correctly in
>my
>>>> scenario.
>>>> >
>>>> > I currently have 2 DBs - one that lists products and one that lists
>>all
>>>> items
>>>> > that have been signed out (or in) at any time. This is for inventory
>>>> purposes.
>>>> > I am trying to create an HTML table that lists each of the items
only
>>>> once
>>>> > with the last time they've been signed out/in or blank if they've
never
>>>> been.
>>>> >
>>>> > My code currently is
>>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,
>>>> SIGNOUT.RACFID,
>>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
>>>> WEBSRV.EQUIPMNT
>>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
>>>> > ORDER BY SIGNOUT.DATEOUT DESC
>>>> >
>>>> > This code still lists items that have been signed out/in more than
>once,
>>>> > multiple times. I've tried using sub-queries, but I'm not quite sure
>>of
>>>> > the exact syntax to make this work correctly.
>>>> >
>>>> > Hopefully this is enough information. Thanks to anyone who can help!
>>>> > Cary
>>>> >
>>>>
>>>>
>>>
>>>
>>
>

[Daniel Reber]

how can that be the unique id for signout if the serialnum is there multiple
times?

"Cary R" <cary.rotman@intria.com> wrote in message
news:3bab884b$1@news.devx.com...
>
> Serialnum for both
>
> "Cary R" <cary.rotman@intria.com> wrote:
> >
> >
> >>
> >>Thanx a lot for the quick response, however I'm still facing the same
problems.
> >>
> >>
> >>My main problem still exists in the duplication of data. For example,
> say
> >>there are items A, B, C, D within the equipment database. Now, item A
and
> >>B have both been signed out, they appear fine. However, say C has been
> >signed
> >>out, signed in, then signed back out again, all 3 occurences of the C
item
> >>signout are visible on the page. (By changing Right Join to Full outer
> >join,
> >>I was able to see the D item with the table left blank as desired - I
don't
> >>know why the right join didn't work this time.)
> >>
> >>My goal is to somehow use the distinct command to only show the last
occurence
> >>of each item. It is very likely that your code was really close,
however
> >>I've been workin on this one line for a while now, and I seem to always
> >end up with the same result.
> >
> >Maybe, to make it more simple...If I were just to type: select distinct
> e.serialnum,
> >then it shows up correctly (obviously without other data), however then
> I
> >type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and so on, the
> >distinct command no longer functions as I want it to.
> >>
> >>Thanx again,
> >>Cary
> >>
> >>
> >>"Daniel Reber" <dreber@dminconline.com> wrote:
> >>>I also added a comma after s.datein by mistake.
> >>>I think that I need more coffee.
> >>>
> >>>
> >>>"Daniel Reber" <dreber@dminconline.com> wrote in message
> >>>news:3bab6af0@news.devx.com...
> >>>> try this:
> >>>>
> >>>> select
> >>>> e.serialnum,
> >>>> e.[name]
> >>>> s.status,
> >>>> s.racfid,
> >>>> s.dateout,
> >>>> s.datein,
> >>>> from equipmnt
> >>>> right join (select
> >>>> max(dateout) dateout,
> >>>> serialnum,
> >>>> status,
> >>>> racfid,
> >>>> datein
> >>>> from signout
> >>>> group by serialnum,racfid,
> >>>> status, datein ) s on s.serialnum = e.serialnum
> >>>> order by dateout desc
> >>>>
> >>>> you may have to change the group by in the derived table to get the
> data
> >>>the
> >>>> way you want it.
> >>>>
> >>>> HTH
> >>>>
> >>>> Daniel Reber
> >>>> Datamasters, Inc
> >>>>
> >>>> "Cary R" <cary.rotman@intria.com> wrote in message
> >>>> news:3bab6314$1@news.devx.com...
> >>>> >
> >>>> > I've noticed there's been a lot of questions on the select distinct
> >>>> statement,
> >>>> > and in many tries, I can't figure out how to use this correctly in
> >my
> >>>> scenario.
> >>>> >
> >>>> > I currently have 2 DBs - one that lists products and one that lists
> >>all
> >>>> items
> >>>> > that have been signed out (or in) at any time. This is for
inventory
> >>>> purposes.
> >>>> > I am trying to create an HTML table that lists each of the items
> only
> >>>> once
> >>>> > with the last time they've been signed out/in or blank if they've
> never
> >>>> been.
> >>>> >
> >>>> > My code currently is
> >>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,
> >>>> SIGNOUT.RACFID,
> >>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
> >>>> WEBSRV.EQUIPMNT
> >>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
> >>>> > ORDER BY SIGNOUT.DATEOUT DESC
> >>>> >
> >>>> > This code still lists items that have been signed out/in more than
> >once,
> >>>> > multiple times. I've tried using sub-queries, but I'm not quite
sure
> >>of
> >>>> > the exact syntax to make this work correctly.
> >>>> >
> >>>> > Hopefully this is enough information. Thanks to anyone who can
help!
> >>>> > Cary
> >>>> >
> >>>>
> >>>>
> >>>
> >>>
> >>
> >
>

[Cary R]

Oh...sorry about that.
It's actually a signoutID which is formed by concatenating the serial number,
username, and the signoutdate. (That actually brings up a flaw in that if
someone signs an object in and out on the same day, there will be some problems,
so thank your for bringing that up.)

But assuming that doesn't happen, will that help at all??



"Daniel Reber" <dreber@dminconline.com> wrote:
>how can that be the unique id for signout if the serialnum is there multiple
>times?
>
>"Cary R" <cary.rotman@intria.com> wrote in message
>news:3bab884b$1@news.devx.com...
>>
>> Serialnum for both
>>
>> "Cary R" <cary.rotman@intria.com> wrote:
>> >
>> >
>> >>
>> >>Thanx a lot for the quick response, however I'm still facing the same
>problems.
>> >>
>> >>
>> >>My main problem still exists in the duplication of data. For example,
>> say
>> >>there are items A, B, C, D within the equipment database. Now, item
A
>and
>> >>B have both been signed out, they appear fine. However, say C has been
>> >signed
>> >>out, signed in, then signed back out again, all 3 occurences of the
C
>item
>> >>signout are visible on the page. (By changing Right Join to Full outer
>> >join,
>> >>I was able to see the D item with the table left blank as desired -
I
>don't
>> >>know why the right join didn't work this time.)
>> >>
>> >>My goal is to somehow use the distinct command to only show the last
>occurence
>> >>of each item. It is very likely that your code was really close,
>however
>> >>I've been workin on this one line for a while now, and I seem to always
>> >end up with the same result.
>> >
>> >Maybe, to make it more simple...If I were just to type: select distinct
>> e.serialnum,
>> >then it shows up correctly (obviously without other data), however then
>> I
>> >type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and so on,
the
>> >distinct command no longer functions as I want it to.
>> >>
>> >>Thanx again,
>> >>Cary
>> >>
>> >>
>> >>"Daniel Reber" <dreber@dminconline.com> wrote:
>> >>>I also added a comma after s.datein by mistake.
>> >>>I think that I need more coffee.
>> >>>
>> >>>
>> >>>"Daniel Reber" <dreber@dminconline.com> wrote in message
>> >>>news:3bab6af0@news.devx.com...
>> >>>> try this:
>> >>>>
>> >>>> select
>> >>>> e.serialnum,
>> >>>> e.[name]
>> >>>> s.status,
>> >>>> s.racfid,
>> >>>> s.dateout,
>> >>>> s.datein,
>> >>>> from equipmnt
>> >>>> right join (select
>> >>>> max(dateout) dateout,
>> >>>> serialnum,
>> >>>> status,
>> >>>> racfid,
>> >>>> datein
>> >>>> from signout
>> >>>> group by serialnum,racfid,
>> >>>> status, datein ) s on s.serialnum = e.serialnum
>> >>>> order by dateout desc
>> >>>>
>> >>>> you may have to change the group by in the derived table to get the
>> data
>> >>>the
>> >>>> way you want it.
>> >>>>
>> >>>> HTH
>> >>>>
>> >>>> Daniel Reber
>> >>>> Datamasters, Inc
>> >>>>
>> >>>> "Cary R" <cary.rotman@intria.com> wrote in message
>> >>>> news:3bab6314$1@news.devx.com...
>> >>>> >
>> >>>> > I've noticed there's been a lot of questions on the select distinct
>> >>>> statement,
>> >>>> > and in many tries, I can't figure out how to use this correctly
in
>> >my
>> >>>> scenario.
>> >>>> >
>> >>>> > I currently have 2 DBs - one that lists products and one that lists
>> >>all
>> >>>> items
>> >>>> > that have been signed out (or in) at any time. This is for
>inventory
>> >>>> purposes.
>> >>>> > I am trying to create an HTML table that lists each of the items
>> only
>> >>>> once
>> >>>> > with the last time they've been signed out/in or blank if they've
>> never
>> >>>> been.
>> >>>> >
>> >>>> > My code currently is
>> >>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS,
>> >>>> SIGNOUT.RACFID,
>> >>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
>> >>>> WEBSRV.EQUIPMNT
>> >>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
>> >>>> > ORDER BY SIGNOUT.DATEOUT DESC
>> >>>> >
>> >>>> > This code still lists items that have been signed out/in more than
>> >once,
>> >>>> > multiple times. I've tried using sub-queries, but I'm not quite
>sure
>> >>of
>> >>>> > the exact syntax to make this work correctly.
>> >>>> >
>> >>>> > Hopefully this is enough information. Thanks to anyone who can
>help!
>> >>>> > Cary
>> >>>> >
>> >>>>
>> >>>>
>> >>>
>> >>>
>> >>
>> >
>>
>
>

[Daniel Reber]

you might want to consider changing that to an auto number. I would make
things easier.
the reason why I asked is so we can change the query to

select
e.serialnum,
e.[name],
s2.status,
s2.racfid,
s1.dateout,
s2.datein
from equipmnt
right join (select
max(dateout) dateout, uniqueID
from signout
group by uniqueID) s1 s on s1.serialnum = e.serialnum
right join (select
uniqueID,
serialnum,
status,
racfid,
datein
from signout ) s2 on s2.uniqueID= s1.uniqueID
order by s1.dateout desc


"Cary R" <cary.rotman@intria.com> wrote in message
news:3bab8a53$1@news.devx.com...
>
> Oh...sorry about that.
> It's actually a signoutID which is formed by concatenating the serial
number,
> username, and the signoutdate. (That actually brings up a flaw in that if
> someone signs an object in and out on the same day, there will be some
problems,
> so thank your for bringing that up.)
>
> But assuming that doesn't happen, will that help at all??
>
>
>
> "Daniel Reber" <dreber@dminconline.com> wrote:
> >how can that be the unique id for signout if the serialnum is there
multiple
> >times?
> >
> >"Cary R" <cary.rotman@intria.com> wrote in message
> >news:3bab884b$1@news.devx.com...
> >>
> >> Serialnum for both
> >>
> >> "Cary R" <cary.rotman@intria.com> wrote:
> >> >
> >> >
> >> >>
> >> >>Thanx a lot for the quick response, however I'm still facing the same
> >problems.
> >> >>
> >> >>
> >> >>My main problem still exists in the duplication of data. For
example,
> >> say
> >> >>there are items A, B, C, D within the equipment database. Now, item
> A
> >and
> >> >>B have both been signed out, they appear fine. However, say C has
been
> >> >signed
> >> >>out, signed in, then signed back out again, all 3 occurences of the
> C
> >item
> >> >>signout are visible on the page. (By changing Right Join to Full
outer
> >> >join,
> >> >>I was able to see the D item with the table left blank as desired -
> I
> >don't
> >> >>know why the right join didn't work this time.)
> >> >>
> >> >>My goal is to somehow use the distinct command to only show the last
> >occurence
> >> >>of each item. It is very likely that your code was really close,
> >however
> >> >>I've been workin on this one line for a while now, and I seem to
always
> >> >end up with the same result.
> >> >
> >> >Maybe, to make it more simple...If I were just to type: select
distinct
> >> e.serialnum,
> >> >then it shows up correctly (obviously without other data), however
then
> >> I
> >> >type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and so on,
> the
> >> >distinct command no longer functions as I want it to.
> >> >>
> >> >>Thanx again,
> >> >>Cary
> >> >>
> >> >>
> >> >>"Daniel Reber" <dreber@dminconline.com> wrote:
> >> >>>I also added a comma after s.datein by mistake.
> >> >>>I think that I need more coffee.
> >> >>>
> >> >>>
> >> >>>"Daniel Reber" <dreber@dminconline.com> wrote in message
> >> >>>news:3bab6af0@news.devx.com...
> >> >>>> try this:
> >> >>>>
> >> >>>> select
> >> >>>> e.serialnum,
> >> >>>> e.[name]
> >> >>>> s.status,
> >> >>>> s.racfid,
> >> >>>> s.dateout,
> >> >>>> s.datein,
> >> >>>> from equipmnt
> >> >>>> right join (select
> >> >>>> max(dateout) dateout,
> >> >>>> serialnum,
> >> >>>> status,
> >> >>>> racfid,
> >> >>>> datein
> >> >>>> from signout
> >> >>>> group by serialnum,racfid,
> >> >>>> status, datein ) s on s.serialnum = e.serialnum
> >> >>>> order by dateout desc
> >> >>>>
> >> >>>> you may have to change the group by in the derived table to get
the
> >> data
> >> >>>the
> >> >>>> way you want it.
> >> >>>>
> >> >>>> HTH
> >> >>>>
> >> >>>> Daniel Reber
> >> >>>> Datamasters, Inc
> >> >>>>
> >> >>>> "Cary R" <cary.rotman@intria.com> wrote in message
> >> >>>> news:3bab6314$1@news.devx.com...
> >> >>>> >
> >> >>>> > I've noticed there's been a lot of questions on the select
distinct
> >> >>>> statement,
> >> >>>> > and in many tries, I can't figure out how to use this correctly
> in
> >> >my
> >> >>>> scenario.
> >> >>>> >
> >> >>>> > I currently have 2 DBs - one that lists products and one that
lists
> >> >>all
> >> >>>> items
> >> >>>> > that have been signed out (or in) at any time. This is for
> >inventory
> >> >>>> purposes.
> >> >>>> > I am trying to create an HTML table that lists each of the
items
> >> only
> >> >>>> once
> >> >>>> > with the last time they've been signed out/in or blank if
they've
> >> never
> >> >>>> been.
> >> >>>> >
> >> >>>> > My code currently is
> >> >>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME
SIGNOUT.STATUS,
> >> >>>> SIGNOUT.RACFID,
> >> >>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
> >> >>>> WEBSRV.EQUIPMNT
> >> >>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
> >> >>>> > ORDER BY SIGNOUT.DATEOUT DESC
> >> >>>> >
> >> >>>> > This code still lists items that have been signed out/in more
than
> >> >once,
> >> >>>> > multiple times. I've tried using sub-queries, but I'm not quite
> >sure
> >> >>of
> >> >>>> > the exact syntax to make this work correctly.
> >> >>>> >
> >> >>>> > Hopefully this is enough information. Thanks to anyone who can
> >help!
> >> >>>> > Cary
> >> >>>> >
> >> >>>>
> >> >>>>
> >> >>>
> >> >>>
> >> >>
> >> >
> >>
> >
> >
>

[Cary R]

You're really a great guy for helping.

I changed the ID to include time which fixed that matter, however, I'm somewhat
confused by your suggestion. Is S2 a second database I should create??


"Daniel Reber" <dreber@dminconline.com> wrote:
>you might want to consider changing that to an auto number. I would make
>things easier.
>the reason why I asked is so we can change the query to
>
>select
> e.serialnum,
> e.[name],
> s2.status,
> s2.racfid,
> s1.dateout,
> s2.datein
> from equipmnt
> right join (select
> max(dateout) dateout, uniqueID
> from signout
> group by uniqueID) s1 s on s1.serialnum = e.serialnum
> right join (select
> uniqueID,
> serialnum,
> status,
> racfid,
> datein
> from signout ) s2 on s2.uniqueID= s1.uniqueID
> order by s1.dateout desc
>
>
>"Cary R" <cary.rotman@intria.com> wrote in message
>news:3bab8a53$1@news.devx.com...
>>
>> Oh...sorry about that.
>> It's actually a signoutID which is formed by concatenating the serial
>number,
>> username, and the signoutdate. (That actually brings up a flaw in that
if
>> someone signs an object in and out on the same day, there will be some
>problems,
>> so thank your for bringing that up.)
>>
>> But assuming that doesn't happen, will that help at all??
>>
>>
>>
>> "Daniel Reber" <dreber@dminconline.com> wrote:
>> >how can that be the unique id for signout if the serialnum is there
>multiple
>> >times?
>> >
>> >"Cary R" <cary.rotman@intria.com> wrote in message
>> >news:3bab884b$1@news.devx.com...
>> >>
>> >> Serialnum for both
>> >>
>> >> "Cary R" <cary.rotman@intria.com> wrote:
>> >> >
>> >> >
>> >> >>
>> >> >>Thanx a lot for the quick response, however I'm still facing the
same
>> >problems.
>> >> >>
>> >> >>
>> >> >>My main problem still exists in the duplication of data. For
>example,
>> >> say
>> >> >>there are items A, B, C, D within the equipment database. Now, item
>> A
>> >and
>> >> >>B have both been signed out, they appear fine. However, say C has
>been
>> >> >signed
>> >> >>out, signed in, then signed back out again, all 3 occurences of the
>> C
>> >item
>> >> >>signout are visible on the page. (By changing Right Join to Full
>outer
>> >> >join,
>> >> >>I was able to see the D item with the table left blank as desired
-
>> I
>> >don't
>> >> >>know why the right join didn't work this time.)
>> >> >>
>> >> >>My goal is to somehow use the distinct command to only show the last
>> >occurence
>> >> >>of each item. It is very likely that your code was really close,
>> >however
>> >> >>I've been workin on this one line for a while now, and I seem to
>always
>> >> >end up with the same result.
>> >> >
>> >> >Maybe, to make it more simple...If I were just to type: select
>distinct
>> >> e.serialnum,
>> >> >then it shows up correctly (obviously without other data), however
>then
>> >> I
>> >> >type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and so
on,
>> the
>> >> >distinct command no longer functions as I want it to.
>> >> >>
>> >> >>Thanx again,
>> >> >>Cary
>> >> >>
>> >> >>
>> >> >>"Daniel Reber" <dreber@dminconline.com> wrote:
>> >> >>>I also added a comma after s.datein by mistake.
>> >> >>>I think that I need more coffee.
>> >> >>>
>> >> >>>
>> >> >>>"Daniel Reber" <dreber@dminconline.com> wrote in message
>> >> >>>news:3bab6af0@news.devx.com...
>> >> >>>> try this:
>> >> >>>>
>> >> >>>> select
>> >> >>>> e.serialnum,
>> >> >>>> e.[name]
>> >> >>>> s.status,
>> >> >>>> s.racfid,
>> >> >>>> s.dateout,
>> >> >>>> s.datein,
>> >> >>>> from equipmnt
>> >> >>>> right join (select
>> >> >>>> max(dateout) dateout,
>> >> >>>> serialnum,
>> >> >>>> status,
>> >> >>>> racfid,
>> >> >>>> datein
>> >> >>>> from signout
>> >> >>>> group by serialnum,racfid,
>> >> >>>> status, datein ) s on s.serialnum = e.serialnum
>> >> >>>> order by dateout desc
>> >> >>>>
>> >> >>>> you may have to change the group by in the derived table to get
>the
>> >> data
>> >> >>>the
>> >> >>>> way you want it.
>> >> >>>>
>> >> >>>> HTH
>> >> >>>>
>> >> >>>> Daniel Reber
>> >> >>>> Datamasters, Inc
>> >> >>>>
>> >> >>>> "Cary R" <cary.rotman@intria.com> wrote in message
>> >> >>>> news:3bab6314$1@news.devx.com...
>> >> >>>> >
>> >> >>>> > I've noticed there's been a lot of questions on the select
>distinct
>> >> >>>> statement,
>> >> >>>> > and in many tries, I can't figure out how to use this correctly
>> in
>> >> >my
>> >> >>>> scenario.
>> >> >>>> >
>> >> >>>> > I currently have 2 DBs - one that lists products and one that
>lists
>> >> >>all
>> >> >>>> items
>> >> >>>> > that have been signed out (or in) at any time. This is for
>> >inventory
>> >> >>>> purposes.
>> >> >>>> > I am trying to create an HTML table that lists each of the
>items
>> >> only
>> >> >>>> once
>> >> >>>> > with the last time they've been signed out/in or blank if
>they've
>> >> never
>> >> >>>> been.
>> >> >>>> >
>> >> >>>> > My code currently is
>> >> >>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME
>SIGNOUT.STATUS,
>> >> >>>> SIGNOUT.RACFID,
>> >> >>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN
>> >> >>>> WEBSRV.EQUIPMNT
>> >> >>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
>> >> >>>> > ORDER BY SIGNOUT.DATEOUT DESC
>> >> >>>> >
>> >> >>>> > This code still lists items that have been signed out/in more
>than
>> >> >once,
>> >> >>>> > multiple times. I've tried using sub-queries, but I'm not quite
>> >sure
>> >> >>of
>> >> >>>> > the exact syntax to make this work correctly.
>> >> >>>> >
>> >> >>>> > Hopefully this is enough information. Thanks to anyone who
can
>> >help!
>> >> >>>> > Cary
>> >> >>>> >
>> >> >>>>
>> >> >>>>
>> >> >>>
>> >> >>>
>> >> >>
>> >> >
>> >>
>> >
>> >
>>
>
>

[Daniel Reber]

since you are trying to get distinct rows out of data that isn't distinct
you will first need to pull the max(dateout) out of the first derived table
then join it to the rest of the information in the second derived table.
You will not need to make any other changes, derived tables are virtual
tables.


"Cary R" <cary.rotman@intria.com> wrote in message
news:3bab9020$1@news.devx.com...
>
> You're really a great guy for helping.
>
> I changed the ID to include time which fixed that matter, however, I'm
somewhat
> confused by your suggestion. Is S2 a second database I should create??
>
>
> "Daniel Reber" <dreber@dminconline.com> wrote:
> >you might want to consider changing that to an auto number. I would make
> >things easier.
> >the reason why I asked is so we can change the query to
> >
> >select
> > e.serialnum,
> > e.[name],
> > s2.status,
> > s2.racfid,
> > s1.dateout,
> > s2.datein
> > from equipmnt
> > right join (select
> > max(dateout) dateout, uniqueID
> > from signout
> > group by uniqueID) s1 s on s1.serialnum = e.serialnum
> > right join (select
> > uniqueID,
> > serialnum,
> > status,
> > racfid,
> > datein
> > from signout ) s2 on s2.uniqueID= s1.uniqueID
> > order by s1.dateout desc
> >
> >
> >"Cary R" <cary.rotman@intria.com> wrote in message
> >news:3bab8a53$1@news.devx.com...
> >>
> >> Oh...sorry about that.
> >> It's actually a signoutID which is formed by concatenating the serial
> >number,
> >> username, and the signoutdate. (That actually brings up a flaw in that
> if
> >> someone signs an object in and out on the same day, there will be some
> >problems,
> >> so thank your for bringing that up.)
> >>
> >> But assuming that doesn't happen, will that help at all??
> >>
> >>
> >>
> >> "Daniel Reber" <dreber@dminconline.com> wrote:
> >> >how can that be the unique id for signout if the serialnum is there
> >multiple
> >> >times?
> >> >
> >> >"Cary R" <cary.rotman@intria.com> wrote in message
> >> >news:3bab884b$1@news.devx.com...
> >> >>
> >> >> Serialnum for both
> >> >>
> >> >> "Cary R" <cary.rotman@intria.com> wrote:
> >> >> >
> >> >> >
> >> >> >>
> >> >> >>Thanx a lot for the quick response, however I'm still facing the
> same
> >> >problems.
> >> >> >>
> >> >> >>
> >> >> >>My main problem still exists in the duplication of data. For
> >example,
> >> >> say
> >> >> >>there are items A, B, C, D within the equipment database. Now,
item
> >> A
> >> >and
> >> >> >>B have both been signed out, they appear fine. However, say C has
> >been
> >> >> >signed
> >> >> >>out, signed in, then signed back out again, all 3 occurences of
the
> >> C
> >> >item
> >> >> >>signout are visible on the page. (By changing Right Join to Full
> >outer
> >> >> >join,
> >> >> >>I was able to see the D item with the table left blank as desired
> -
> >> I
> >> >don't
> >> >> >>know why the right join didn't work this time.)
> >> >> >>
> >> >> >>My goal is to somehow use the distinct command to only show the
last
> >> >occurence
> >> >> >>of each item. It is very likely that your code was really close,
> >> >however
> >> >> >>I've been workin on this one line for a while now, and I seem to
> >always
> >> >> >end up with the same result.
> >> >> >
> >> >> >Maybe, to make it more simple...If I were just to type: select
> >distinct
> >> >> e.serialnum,
> >> >> >then it shows up correctly (obviously without other data), however
> >then
> >> >> I
> >> >> >type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and so
> on,
> >> the
> >> >> >distinct command no longer functions as I want it to.
> >> >> >>
> >> >> >>Thanx again,
> >> >> >>Cary
> >> >> >>
> >> >> >>
> >> >> >>"Daniel Reber" <dreber@dminconline.com> wrote:
> >> >> >>>I also added a comma after s.datein by mistake.
> >> >> >>>I think that I need more coffee.
> >> >> >>>
> >> >> >>>
> >> >> >>>"Daniel Reber" <dreber@dminconline.com> wrote in message
> >> >> >>>news:3bab6af0@news.devx.com...
> >> >> >>>> try this:
> >> >> >>>>
> >> >> >>>> select
> >> >> >>>> e.serialnum,
> >> >> >>>> e.[name]
> >> >> >>>> s.status,
> >> >> >>>> s.racfid,
> >> >> >>>> s.dateout,
> >> >> >>>> s.datein,
> >> >> >>>> from equipmnt
> >> >> >>>> right join (select
> >> >> >>>> max(dateout) dateout,
> >> >> >>>> serialnum,
> >> >> >>>> status,
> >> >> >>>> racfid,
> >> >> >>>> datein
> >> >> >>>> from signout
> >> >> >>>> group by serialnum,racfid,
> >> >> >>>> status, datein ) s on s.serialnum = e.serialnum
> >> >> >>>> order by dateout desc
> >> >> >>>>
> >> >> >>>> you may have to change the group by in the derived table to get
> >the
> >> >> data
> >> >> >>>the
> >> >> >>>> way you want it.
> >> >> >>>>
> >> >> >>>> HTH
> >> >> >>>>
> >> >> >>>> Daniel Reber
> >> >> >>>> Datamasters, Inc
> >> >> >>>>
> >> >> >>>> "Cary R" <cary.rotman@intria.com> wrote in message
> >> >> >>>> news:3bab6314$1@news.devx.com...
> >> >> >>>> >
> >> >> >>>> > I've noticed there's been a lot of questions on the select
> >distinct
> >> >> >>>> statement,
> >> >> >>>> > and in many tries, I can't figure out how to use this
correctly
> >> in
> >> >> >my
> >> >> >>>> scenario.
> >> >> >>>> >
> >> >> >>>> > I currently have 2 DBs - one that lists products and one that
> >lists
> >> >> >>all
> >> >> >>>> items
> >> >> >>>> > that have been signed out (or in) at any time. This is for
> >> >inventory
> >> >> >>>> purposes.
> >> >> >>>> > I am trying to create an HTML table that lists each of the
> >items
> >> >> only
> >> >> >>>> once
> >> >> >>>> > with the last time they've been signed out/in or blank if
> >they've
> >> >> never
> >> >> >>>> been.
> >> >> >>>> >
> >> >> >>>> > My code currently is
> >> >> >>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME
> >SIGNOUT.STATUS,
> >> >> >>>> SIGNOUT.RACFID,
> >> >> >>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT
JOIN
> >> >> >>>> WEBSRV.EQUIPMNT
> >> >> >>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
> >> >> >>>> > ORDER BY SIGNOUT.DATEOUT DESC
> >> >> >>>> >
> >> >> >>>> > This code still lists items that have been signed out/in more
> >than
> >> >> >once,
> >> >> >>>> > multiple times. I've tried using sub-queries, but I'm not
quite
> >> >sure
> >> >> >>of
> >> >> >>>> > the exact syntax to make this work correctly.
> >> >> >>>> >
> >> >> >>>> > Hopefully this is enough information. Thanks to anyone who
> can
> >> >help!
> >> >> >>>> > Cary
> >> >> >>>> >
> >> >> >>>>
> >> >> >>>>
> >> >> >>>
> >> >> >>>
> >> >> >>
> >> >> >
> >> >>
> >> >
> >> >
> >>
> >
> >
>

[Cary R]

Perfect...thanx a lot for everything!

Cary

"Daniel Reber" <dreber@dminconline.com> wrote:
>since you are trying to get distinct rows out of data that isn't distinct
>you will first need to pull the max(dateout) out of the first derived table
>then join it to the rest of the information in the second derived table.
>You will not need to make any other changes, derived tables are virtual
>tables.
>
>
>"Cary R" <cary.rotman@intria.com> wrote in message
>news:3bab9020$1@news.devx.com...
>>
>> You're really a great guy for helping.
>>
>> I changed the ID to include time which fixed that matter, however, I'm
>somewhat
>> confused by your suggestion. Is S2 a second database I should create??
>>
>>
>> "Daniel Reber" <dreber@dminconline.com> wrote:
>> >you might want to consider changing that to an auto number. I would
make
>> >things easier.
>> >the reason why I asked is so we can change the query to
>> >
>> >select
>> > e.serialnum,
>> > e.[name],
>> > s2.status,
>> > s2.racfid,
>> > s1.dateout,
>> > s2.datein
>> > from equipmnt
>> > right join (select
>> > max(dateout) dateout, uniqueID
>> > from signout
>> > group by uniqueID) s1 s on s1.serialnum = e.serialnum
>> > right join (select
>> > uniqueID,
>> > serialnum,
>> > status,
>> > racfid,
>> > datein
>> > from signout ) s2 on s2.uniqueID= s1.uniqueID
>> > order by s1.dateout desc
>> >
>> >
>> >"Cary R" <cary.rotman@intria.com> wrote in message
>> >news:3bab8a53$1@news.devx.com...
>> >>
>> >> Oh...sorry about that.
>> >> It's actually a signoutID which is formed by concatenating the serial
>> >number,
>> >> username, and the signoutdate. (That actually brings up a flaw in
that
>> if
>> >> someone signs an object in and out on the same day, there will be some
>> >problems,
>> >> so thank your for bringing that up.)
>> >>
>> >> But assuming that doesn't happen, will that help at all??
>> >>
>> >>
>> >>
>> >> "Daniel Reber" <dreber@dminconline.com> wrote:
>> >> >how can that be the unique id for signout if the serialnum is there
>> >multiple
>> >> >times?
>> >> >
>> >> >"Cary R" <cary.rotman@intria.com> wrote in message
>> >> >news:3bab884b$1@news.devx.com...
>> >> >>
>> >> >> Serialnum for both
>> >> >>
>> >> >> "Cary R" <cary.rotman@intria.com> wrote:
>> >> >> >
>> >> >> >
>> >> >> >>
>> >> >> >>Thanx a lot for the quick response, however I'm still facing the
>> same
>> >> >problems.
>> >> >> >>
>> >> >> >>
>> >> >> >>My main problem still exists in the duplication of data. For
>> >example,
>> >> >> say
>> >> >> >>there are items A, B, C, D within the equipment database. Now,
>item
>> >> A
>> >> >and
>> >> >> >>B have both been signed out, they appear fine. However, say C
has
>> >been
>> >> >> >signed
>> >> >> >>out, signed in, then signed back out again, all 3 occurences of
>the
>> >> C
>> >> >item
>> >> >> >>signout are visible on the page. (By changing Right Join to Full
>> >outer
>> >> >> >join,
>> >> >> >>I was able to see the D item with the table left blank as desired
>> -
>> >> I
>> >> >don't
>> >> >> >>know why the right join didn't work this time.)
>> >> >> >>
>> >> >> >>My goal is to somehow use the distinct command to only show the
>last
>> >> >occurence
>> >> >> >>of each item. It is very likely that your code was really close,
>> >> >however
>> >> >> >>I've been workin on this one line for a while now, and I seem
to
>> >always
>> >> >> >end up with the same result.
>> >> >> >
>> >> >> >Maybe, to make it more simple...If I were just to type: select
>> >distinct
>> >> >> e.serialnum,
>> >> >> >then it shows up correctly (obviously without other data), however
>> >then
>> >> >> I
>> >> >> >type: select distinct e.serialnum, e.xxxx, s.xxxx, s.xxxx, and
so
>> on,
>> >> the
>> >> >> >distinct command no longer functions as I want it to.
>> >> >> >>
>> >> >> >>Thanx again,
>> >> >> >>Cary
>> >> >> >>
>> >> >> >>
>> >> >> >>"Daniel Reber" <dreber@dminconline.com> wrote:
>> >> >> >>>I also added a comma after s.datein by mistake.
>> >> >> >>>I think that I need more coffee.
>> >> >> >>>
>> >> >> >>>
>> >> >> >>>"Daniel Reber" <dreber@dminconline.com> wrote in message
>> >> >> >>>news:3bab6af0@news.devx.com...
>> >> >> >>>> try this:
>> >> >> >>>>
>> >> >> >>>> select
>> >> >> >>>> e.serialnum,
>> >> >> >>>> e.[name]
>> >> >> >>>> s.status,
>> >> >> >>>> s.racfid,
>> >> >> >>>> s.dateout,
>> >> >> >>>> s.datein,
>> >> >> >>>> from equipmnt
>> >> >> >>>> right join (select
>> >> >> >>>> max(dateout) dateout,
>> >> >> >>>> serialnum,
>> >> >> >>>> status,
>> >> >> >>>> racfid,
>> >> >> >>>> datein
>> >> >> >>>> from signout
>> >> >> >>>> group by serialnum,racfid,
>> >> >> >>>> status, datein ) s on s.serialnum = e.serialnum
>> >> >> >>>> order by dateout desc
>> >> >> >>>>
>> >> >> >>>> you may have to change the group by in the derived table to
get
>> >the
>> >> >> data
>> >> >> >>>the
>> >> >> >>>> way you want it.
>> >> >> >>>>
>> >> >> >>>> HTH
>> >> >> >>>>
>> >> >> >>>> Daniel Reber
>> >> >> >>>> Datamasters, Inc
>> >> >> >>>>
>> >> >> >>>> "Cary R" <cary.rotman@intria.com> wrote in message
>> >> >> >>>> news:3bab6314$1@news.devx.com...
>> >> >> >>>> >
>> >> >> >>>> > I've noticed there's been a lot of questions on the select
>> >distinct
>> >> >> >>>> statement,
>> >> >> >>>> > and in many tries, I can't figure out how to use this
>correctly
>> >> in
>> >> >> >my
>> >> >> >>>> scenario.
>> >> >> >>>> >
>> >> >> >>>> > I currently have 2 DBs - one that lists products and one
that
>> >lists
>> >> >> >>all
>> >> >> >>>> items
>> >> >> >>>> > that have been signed out (or in) at any time. This is for
>> >> >inventory
>> >> >> >>>> purposes.
>> >> >> >>>> > I am trying to create an HTML table that lists each of the
>> >items
>> >> >> only
>> >> >> >>>> once
>> >> >> >>>> > with the last time they've been signed out/in or blank if
>> >they've
>> >> >> never
>> >> >> >>>> been.
>> >> >> >>>> >
>> >> >> >>>> > My code currently is
>> >> >> >>>> > SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME
>> >SIGNOUT.STATUS,
>> >> >> >>>> SIGNOUT.RACFID,
>> >> >> >>>> > SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT
>JOIN
>> >> >> >>>> WEBSRV.EQUIPMNT
>> >> >> >>>> > ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
>> >> >> >>>> > ORDER BY SIGNOUT.DATEOUT DESC
>> >> >> >>>> >
>> >> >> >>>> > This code still lists items that have been signed out/in
more
>> >than
>> >> >> >once,
>> >> >> >>>> > multiple times. I've tried using sub-queries, but I'm not
>quite
>> >> >sure
>> >> >> >>of
>> >> >> >>>> > the exact syntax to make this work correctly.
>> >> >> >>>> >
>> >> >> >>>> > Hopefully this is enough information. Thanks to anyone who
>> can
>> >> >help!
>> >> >> >>>> > Cary
>> >> >> >>>> >
>> >> >> >>>>
>> >> >> >>>>
>> >> >> >>>
>> >> >> >>>
>> >> >> >>
>> >> >> >
>> >> >>
>> >> >
>> >> >
>> >>
>> >
>> >
>>
>
>

[frye3k]

from what i can see, the code snippet from the other guy is good, however
i feel like you should do a LEFT OUTER JOIN rather than a RIGHT, or a FULL...
let me know how it turns out...

Thanks,
Garland


"Cary R" <cary.rotman@intria.com> wrote:
>
>I've noticed there's been a lot of questions on the select distinct statement,
>and in many tries, I can't figure out how to use this correctly in my scenario.
>
>I currently have 2 DBs - one that lists products and one that lists all
items
>that have been signed out (or in) at any time. This is for inventory purposes.
> I am trying to create an HTML table that lists each of the items only once
>with the last time they've been signed out/in or blank if they've never
been.
>
>My code currently is
> SELECT DISTINCT EQUIPMNT.SERIALNUM, EQUIPMNT.NAME SIGNOUT.STATUS, SIGNOUT.RACFID,
>SIGNOUT.DATEOUT, SIGNOUT.DATEIN, FROM WEBSRV.SIGNOUT RIGHT JOIN WEBSRV.EQUIPMNT
>ON SIGNOUT.SERIALNUM=EQUIPMNT.SERIALNUM
>ORDER BY SIGNOUT.DATEOUT DESC
>
>This code still lists items that have been signed out/in more than once,
>multiple times. I've tried using sub-queries, but I'm not quite sure of
>the exact syntax to make this work correctly.
>
>Hopefully this is enough information. Thanks to anyone who can help!
>Cary
>