Select Distinct

[Daniel Reber]

Cary will still get duplicate rows that way.

"frye3k" <frye3k@hotmail.com> wrote in message
news:3bad4a88$1@news.devx.com...
>
> 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
> >
>

[Daniel Reber]

I think that I forgot my right from my left. You are correct, a left join
should have been used with my sample sql.


"frye3k" <frye3k@hotmail.com> wrote in message
news:3bad4a88$1@news.devx.com...
>
> 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
> >
>

[Cary R]

Sorry to bother you guys again, however I'm still having a few problems.

The first error is due to the 's' after the first right join statement...so
I took that out.

Then it states that I don't have s1.serialnum, yet when I put it in, it gives
me a 'no group by clause' error.

Also, there is an error on the e.serialnum line.

Obviously, you can tell that I'm, by no means, on expert with SQL, so even
by playing around, I can't get the final product.

Thanx again, Cary


"Cary R" <cary.rotman@intria.com> wrote:
>
>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
>>> >> >> >>>> >
>>> >> >> >>>>
>>> >> >> >>>>
>>> >> >> >>>
>>> >> >> >>>
>>> >> >> >>
>>> >> >> >
>>> >> >>
>>> >> >
>>> >> >
>>> >>
>>> >
>>> >
>>>
>>
>>
>

[Daniel Reber]

I guess that I should check my work better.
try this:

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

I changed the right joins to lefts joins like frye3k suggested.


"Cary R" <cary.rotman@intria.com> wrote in message
news:3baf56c8@news.devx.com...
>
> Sorry to bother you guys again, however I'm still having a few problems.
>
> The first error is due to the 's' after the first right join
statement...so
> I took that out.
>
> Then it states that I don't have s1.serialnum, yet when I put it in, it
gives
> me a 'no group by clause' error.
>
> Also, there is an error on the e.serialnum line.
>
> Obviously, you can tell that I'm, by no means, on expert with SQL, so even
> by playing around, I can't get the final product.
>
> Thanx again, Cary
>
>
> "Cary R" <cary.rotman@intria.com> wrote:
> >
> >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
> >>> >> >> >>>> >
> >>> >> >> >>>>
> >>> >> >> >>>>
> >>> >> >> >>>
> >>> >> >> >>>
> >>> >> >> >>
> >>> >> >> >
> >>> >> >>
> >>> >> >
> >>> >> >
> >>> >>
> >>> >
> >>> >
> >>>
> >>
> >>
> >
>

[Cary R]

Unfortunately, it didn't work again. When I tried using the e.xxxx, there
was an error that e.xxxx is not a column, etc. I tried changing it to equipmnt.xxxx,
which then let the program run, however, the duplicates were still there!
It seems like nothing is working!!

I think I'll rethink the way I attempt this problem, unless there's another
great idea.

Thanx,
Cary


"Daniel Reber" <dreber@dminconline.com> wrote:
>I guess that I should check my work better.
>try this:
>
>select
> e.serialnum,
> e.[name],
> s2.status,
> s2.racfid,
> s1.dateout,
> s2.datein
> from equipmnt
> left join (select
> max(dateout) dateout, uniqueID, serialnum
> from signout
> group by uniqueID,serialnum) s1 on s1.serialnum = e.serialnum
> left join (select
> uniqueID,
> serialnum,
> status,
> racfid,
> datein
> from signout ) s2 on s2.uniqueID= s1.uniqueID
> order by s1.dateout desc
>
>I changed the right joins to lefts joins like frye3k suggested.
>
>
>"Cary R" <cary.rotman@intria.com> wrote in message
>news:3baf56c8@news.devx.com...
>>
>> Sorry to bother you guys again, however I'm still having a few problems.
>>
>> The first error is due to the 's' after the first right join
>statement...so
>> I took that out.
>>
>> Then it states that I don't have s1.serialnum, yet when I put it in, it
>gives
>> me a 'no group by clause' error.
>>
>> Also, there is an error on the e.serialnum line.
>>
>> Obviously, you can tell that I'm, by no means, on expert with SQL, so
even
>> by playing around, I can't get the final product.
>>
>> Thanx again, Cary
>>
>>
>> "Cary R" <cary.rotman@intria.com> wrote:
>> >
>> >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
>> >>> >> >> >>>> >
>> >>> >> >> >>>>
>> >>> >> >> >>>>
>> >>> >> >> >>>
>> >>> >> >> >>>
>> >>> >> >> >>
>> >>> >> >> >
>> >>> >> >>
>> >>> >> >
>> >>> >> >
>> >>> >>
>> >>> >
>> >>> >
>> >>>
>> >>
>> >>
>> >
>>
>
>

[Daniel Reber]

could you post the sql that you are using. thanks
"Cary R" <cary.rotman@intria.com> wrote in message
news:3baf6acd$1@news.devx.com...
>
> Unfortunately, it didn't work again. When I tried using the e.xxxx, there
> was an error that e.xxxx is not a column, etc. I tried changing it to
equipmnt.xxxx,
> which then let the program run, however, the duplicates were still there!
> It seems like nothing is working!!
>
> I think I'll rethink the way I attempt this problem, unless there's
another
> great idea.
>
> Thanx,
> Cary
>
>
> "Daniel Reber" <dreber@dminconline.com> wrote:
> >I guess that I should check my work better.
> >try this:
> >
> >select
> > e.serialnum,
> > e.[name],
> > s2.status,
> > s2.racfid,
> > s1.dateout,
> > s2.datein
> > from equipmnt
> > left join (select
> > max(dateout) dateout, uniqueID, serialnum
> > from signout
> > group by uniqueID,serialnum) s1 on s1.serialnum =
e.serialnum
> > left join (select
> > uniqueID,
> > serialnum,
> > status,
> > racfid,
> > datein
> > from signout ) s2 on s2.uniqueID= s1.uniqueID
> > order by s1.dateout desc
> >
> >I changed the right joins to lefts joins like frye3k suggested.
> >
> >
> >"Cary R" <cary.rotman@intria.com> wrote in message
> >news:3baf56c8@news.devx.com...
> >>
> >> Sorry to bother you guys again, however I'm still having a few
problems.
> >>
> >> The first error is due to the 's' after the first right join
> >statement...so
> >> I took that out.
> >>
> >> Then it states that I don't have s1.serialnum, yet when I put it in, it
> >gives
> >> me a 'no group by clause' error.
> >>
> >> Also, there is an error on the e.serialnum line.
> >>
> >> Obviously, you can tell that I'm, by no means, on expert with SQL, so
> even
> >> by playing around, I can't get the final product.
> >>
> >> Thanx again, Cary
> >>
> >>
> >> "Cary R" <cary.rotman@intria.com> wrote:
> >> >
> >> >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
> >> >>> >> >> >>>> >
> >> >>> >> >> >>>>
> >> >>> >> >> >>>>
> >> >>> >> >> >>>
> >> >>> >> >> >>>
> >> >>> >> >> >>
> >> >>> >> >> >
> >> >>> >> >>
> >> >>> >> >
> >> >>> >> >
> >> >>> >>
> >> >>> >
> >> >>> >
> >> >>>
> >> >>
> >> >>
> >> >
> >>
> >
> >
>

[Cary R]

select equipmnt.serialnum, equipmnt.equipment, s2.status, s2.racfid, s1.dateout,
s2.datein from equipmnt left join (select max(dateout) dateout, signoutID,
serialnum from signout group by signoutID, serialnum) s1 on s1.serialnum
= equipmnt.serialnum left join (select signoutID, serialnum, status, racfid,
datein from signout ) s2 on s2.signoutID= s1.signoutID order by s1.dateout
desc

I'm using Net.Data, a program that combines HTML, SQL, and other languages
to produce the website. Perhaps, this is the reason that my data isn't displayed
as desired. I don't know??

Cary


"Daniel Reber" <dreber@dminconline.com> wrote:
>could you post the sql that you are using. thanks
>"Cary R" <cary.rotman@intria.com> wrote in message
>news:3baf6acd$1@news.devx.com...
>>
>> Unfortunately, it didn't work again. When I tried using the e.xxxx, there
>> was an error that e.xxxx is not a column, etc. I tried changing it to
>equipmnt.xxxx,
>> which then let the program run, however, the duplicates were still there!
>> It seems like nothing is working!!
>>
>> I think I'll rethink the way I attempt this problem, unless there's
>another
>> great idea.
>>
>> Thanx,
>> Cary
>>
>>
>> "Daniel Reber" <dreber@dminconline.com> wrote:
>> >I guess that I should check my work better.
>> >try this:
>> >
>> >select
>> > e.serialnum,
>> > e.[name],
>> > s2.status,
>> > s2.racfid,
>> > s1.dateout,
>> > s2.datein
>> > from equipmnt
>> > left join (select
>> > max(dateout) dateout, uniqueID, serialnum
>> > from signout
>> > group by uniqueID,serialnum) s1 on s1.serialnum =
>e.serialnum
>> > left join (select
>> > uniqueID,
>> > serialnum,
>> > status,
>> > racfid,
>> > datein
>> > from signout ) s2 on s2.uniqueID= s1.uniqueID
>> > order by s1.dateout desc
>> >
>> >I changed the right joins to lefts joins like frye3k suggested.
>> >
>> >
>> >"Cary R" <cary.rotman@intria.com> wrote in message
>> >news:3baf56c8@news.devx.com...
>> >>
>> >> Sorry to bother you guys again, however I'm still having a few
>problems.
>> >>
>> >> The first error is due to the 's' after the first right join
>> >statement...so
>> >> I took that out.
>> >>
>> >> Then it states that I don't have s1.serialnum, yet when I put it in,
it
>> >gives
>> >> me a 'no group by clause' error.
>> >>
>> >> Also, there is an error on the e.serialnum line.
>> >>
>> >> Obviously, you can tell that I'm, by no means, on expert with SQL,
so
>> even
>> >> by playing around, I can't get the final product.
>> >>
>> >> Thanx again, Cary
>> >>
>> >>
>> >> "Cary R" <cary.rotman@intria.com> wrote:
>> >> >
>> >> >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
>> >> >>> >> >> >>>> >
>> >> >>> >> >> >>>>
>> >> >>> >> >> >>>>
>> >> >>> >> >> >>>
>> >> >>> >> >> >>>
>> >> >>> >> >> >>
>> >> >>> >> >> >
>> >> >>> >> >>
>> >> >>> >> >
>> >> >>> >> >
>> >> >>> >>
>> >> >>> >
>> >> >>> >
>> >> >>>
>> >> >>
>> >> >>
>> >> >
>> >>
>> >
>> >
>>
>
>

[Daniel Reber]

It seems that it may be data related.

on the s1 join you are only bringing back one row for each row in equipmnt.
could you run these scripts to test the values.

select count(*) from equipmnt
select distinct serialnum from equipmnt

the values should be the same

"Cary R" <cary.rotman@intria.com> wrote in message
news:3baf6dd8$1@news.devx.com...
>
> select equipmnt.serialnum, equipmnt.equipment, s2.status, s2.racfid,
s1.dateout,
> s2.datein from equipmnt left join (select max(dateout) dateout, signoutID,
> serialnum from signout group by signoutID, serialnum) s1 on s1.serialnum
> = equipmnt.serialnum left join (select signoutID, serialnum, status,
racfid,
> datein from signout ) s2 on s2.signoutID= s1.signoutID order by s1.dateout
> desc
>
> I'm using Net.Data, a program that combines HTML, SQL, and other languages
> to produce the website. Perhaps, this is the reason that my data isn't
displayed
> as desired. I don't know??
>
> Cary
>
>
> "Daniel Reber" <dreber@dminconline.com> wrote:
> >could you post the sql that you are using. thanks
> >"Cary R" <cary.rotman@intria.com> wrote in message
> >news:3baf6acd$1@news.devx.com...
> >>
> >> Unfortunately, it didn't work again. When I tried using the e.xxxx,
there
> >> was an error that e.xxxx is not a column, etc. I tried changing it to
> >equipmnt.xxxx,
> >> which then let the program run, however, the duplicates were still
there!
> >> It seems like nothing is working!!
> >>
> >> I think I'll rethink the way I attempt this problem, unless there's
> >another
> >> great idea.
> >>
> >> Thanx,
> >> Cary
> >>
> >>
> >> "Daniel Reber" <dreber@dminconline.com> wrote:
> >> >I guess that I should check my work better.
> >> >try this:
> >> >
> >> >select
> >> > e.serialnum,
> >> > e.[name],
> >> > s2.status,
> >> > s2.racfid,
> >> > s1.dateout,
> >> > s2.datein
> >> > from equipmnt
> >> > left join (select
> >> > max(dateout) dateout, uniqueID, serialnum
> >> > from signout
> >> > group by uniqueID,serialnum) s1 on s1.serialnum =
> >e.serialnum
> >> > left join (select
> >> > uniqueID,
> >> > serialnum,
> >> > status,
> >> > racfid,
> >> > datein
> >> > from signout ) s2 on s2.uniqueID= s1.uniqueID
> >> > order by s1.dateout desc
> >> >
> >> >I changed the right joins to lefts joins like frye3k suggested.
> >> >
> >> >
> >> >"Cary R" <cary.rotman@intria.com> wrote in message
> >> >news:3baf56c8@news.devx.com...
> >> >>
> >> >> Sorry to bother you guys again, however I'm still having a few
> >problems.
> >> >>
> >> >> The first error is due to the 's' after the first right join
> >> >statement...so
> >> >> I took that out.
> >> >>
> >> >> Then it states that I don't have s1.serialnum, yet when I put it in,
> it
> >> >gives
> >> >> me a 'no group by clause' error.
> >> >>
> >> >> Also, there is an error on the e.serialnum line.
> >> >>
> >> >> Obviously, you can tell that I'm, by no means, on expert with SQL,
> so
> >> even
> >> >> by playing around, I can't get the final product.
> >> >>
> >> >> Thanx again, Cary
> >> >>
> >> >>
> >> >> "Cary R" <cary.rotman@intria.com> wrote:
> >> >> >
> >> >> >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
> >> >> >>> >> >> >>>> >
> >> >> >>> >> >> >>>>
> >> >> >>> >> >> >>>>
> >> >> >>> >> >> >>>
> >> >> >>> >> >> >>>
> >> >> >>> >> >> >>
> >> >> >>> >> >> >
> >> >> >>> >> >>
> >> >> >>> >> >
> >> >> >>> >> >
> >> >> >>> >>
> >> >> >>> >
> >> >> >>> >
> >> >> >>>
> >> >> >>
> >> >> >>
> >> >> >
> >> >>
> >> >
> >> >
> >>
> >
> >
>

[Cary R]

On the select count - it displayed '4'
The select distinct displayed the 4 unique serial numbers.

The select distinct of the serial numbers in the signout displays 3 unique
serial numbers, as desired, because 1 of the objects has never been signed
out.

When I display it on the webpage, there are 7 pieces of data shown, as in
my testing I have signed in and/or out an object 6 times, while the 1 object
has never been touched.

All I want (which you probably know) is to display the last event of each
piece of equipment or just the piece of equipment if its not in the signout
database.

I have access to a program which diplays both entire database, so I know
that all the data is entered correctly.

Cary



"Daniel Reber" <dreber@dminconline.com> wrote:
>It seems that it may be data related.
>
>on the s1 join you are only bringing back one row for each row in equipmnt.
>could you run these scripts to test the values.
>
>select count(*) from equipmnt
>select distinct serialnum from equipmnt
>
>the values should be the same
>
>"Cary R" <cary.rotman@intria.com> wrote in message
>news:3baf6dd8$1@news.devx.com...
>>
>> select equipmnt.serialnum, equipmnt.equipment, s2.status, s2.racfid,
>s1.dateout,
>> s2.datein from equipmnt left join (select max(dateout) dateout, signoutID,
>> serialnum from signout group by signoutID, serialnum) s1 on s1.serialnum
>> = equipmnt.serialnum left join (select signoutID, serialnum, status,
>racfid,
>> datein from signout ) s2 on s2.signoutID= s1.signoutID order by s1.dateout
>> desc
>>
>> I'm using Net.Data, a program that combines HTML, SQL, and other languages
>> to produce the website. Perhaps, this is the reason that my data isn't
>displayed
>> as desired. I don't know??
>>
>> Cary
>>
>>
>> "Daniel Reber" <dreber@dminconline.com> wrote:
>> >could you post the sql that you are using. thanks
>> >"Cary R" <cary.rotman@intria.com> wrote in message
>> >news:3baf6acd$1@news.devx.com...
>> >>
>> >> Unfortunately, it didn't work again. When I tried using the e.xxxx,
>there
>> >> was an error that e.xxxx is not a column, etc. I tried changing it
to
>> >equipmnt.xxxx,
>> >> which then let the program run, however, the duplicates were still
>there!
>> >> It seems like nothing is working!!
>> >>
>> >> I think I'll rethink the way I attempt this problem, unless there's
>> >another
>> >> great idea.
>> >>
>> >> Thanx,
>> >> Cary
>> >>
>> >>
>> >> "Daniel Reber" <dreber@dminconline.com> wrote:
>> >> >I guess that I should check my work better.
>> >> >try this:
>> >> >
>> >> >select
>> >> > e.serialnum,
>> >> > e.[name],
>> >> > s2.status,
>> >> > s2.racfid,
>> >> > s1.dateout,
>> >> > s2.datein
>> >> > from equipmnt
>> >> > left join (select
>> >> > max(dateout) dateout, uniqueID, serialnum
>> >> > from signout
>> >> > group by uniqueID,serialnum) s1 on s1.serialnum =
>> >e.serialnum
>> >> > left join (select
>> >> > uniqueID,
>> >> > serialnum,
>> >> > status,
>> >> > racfid,
>> >> > datein
>> >> > from signout ) s2 on s2.uniqueID= s1.uniqueID
>> >> > order by s1.dateout desc
>> >> >
>> >> >I changed the right joins to lefts joins like frye3k suggested.
>> >> >
>> >> >
>> >> >"Cary R" <cary.rotman@intria.com> wrote in message
>> >> >news:3baf56c8@news.devx.com...
>> >> >>
>> >> >> Sorry to bother you guys again, however I'm still having a few
>> >problems.
>> >> >>
>> >> >> The first error is due to the 's' after the first right join
>> >> >statement...so
>> >> >> I took that out.
>> >> >>
>> >> >> Then it states that I don't have s1.serialnum, yet when I put it
in,
>> it
>> >> >gives
>> >> >> me a 'no group by clause' error.
>> >> >>
>> >> >> Also, there is an error on the e.serialnum line.
>> >> >>
>> >> >> Obviously, you can tell that I'm, by no means, on expert with SQL,
>> so
>> >> even
>> >> >> by playing around, I can't get the final product.
>> >> >>
>> >> >> Thanx again, Cary
>> >> >>
>> >> >>
>> >> >> "Cary R" <cary.rotman@intria.com> wrote:
>> >> >> >
>> >> >> >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]

To be honest, I have no idea, however the IBM reference book I have says version
4. I don't know if they're related though.

I was thinking of using a sub query, yet I wasn't quite sure on how to structure
it



"Daniel Reber" <dreber@dminconline.com> wrote:
>I see what is wrong. I don't think that you can do a simple select
>statement to get the data that you want. What version of SQL Sever are
you
>using?
>
>
>
>"Cary R" <cary.rotman@intria.com> wrote in message
>news:3baf751b$1@news.devx.com...
>>
>> On the select count - it displayed '4'
>> The select distinct displayed the 4 unique serial numbers.
>>
>> The select distinct of the serial numbers in the signout displays 3 unique
>> serial numbers, as desired, because 1 of the objects has never been signed
>> out.
>>
>> When I display it on the webpage, there are 7 pieces of data shown, as
in
>> my testing I have signed in and/or out an object 6 times, while the 1
>object
>> has never been touched.
>>
>> All I want (which you probably know) is to display the last event of each
>> piece of equipment or just the piece of equipment if its not in the
>signout
>> database.
>>
>> I have access to a program which diplays both entire database, so I know
>> that all the data is entered correctly.
>>
>> Cary
>>
>>
>>
>> "Daniel Reber" <dreber@dminconline.com> wrote:
>> >It seems that it may be data related.
>> >
>> >on the s1 join you are only bringing back one row for each row in
>equipmnt.
>> >could you run these scripts to test the values.
>> >
>> >select count(*) from equipmnt
>> >select distinct serialnum from equipmnt
>> >
>> >the values should be the same
>> >
>> >"Cary R" <cary.rotman@intria.com> wrote in message
>> >news:3baf6dd8$1@news.devx.com...
>> >>
>> >> select equipmnt.serialnum, equipmnt.equipment, s2.status, s2.racfid,
>> >s1.dateout,
>> >> s2.datein from equipmnt left join (select max(dateout) dateout,
>signoutID,
>> >> serialnum from signout group by signoutID, serialnum) s1 on
>s1.serialnum
>> >> = equipmnt.serialnum left join (select signoutID, serialnum, status,
>> >racfid,
>> >> datein from signout ) s2 on s2.signoutID= s1.signoutID order by
>s1.dateout
>> >> desc
>> >>
>> >> I'm using Net.Data, a program that combines HTML, SQL, and other
>languages
>> >> to produce the website. Perhaps, this is the reason that my data isn't
>> >displayed
>> >> as desired. I don't know??
>> >>
>> >> Cary
>> >>
>> >>
>> >> "Daniel Reber" <dreber@dminconline.com> wrote:
>> >> >could you post the sql that you are using. thanks
>> >> >"Cary R" <cary.rotman@intria.com> wrote in message
>> >> >news:3baf6acd$1@news.devx.com...
>> >> >>
>> >> >> Unfortunately, it didn't work again. When I tried using the e.xxxx,
>> >there
>> >> >> was an error that e.xxxx is not a column, etc. I tried changing
it
>> to
>> >> >equipmnt.xxxx,
>> >> >> which then let the program run, however, the duplicates were still
>> >there!
>> >> >> It seems like nothing is working!!
>> >> >>
>> >> >> I think I'll rethink the way I attempt this problem, unless there's
>> >> >another
>> >> >> great idea.
>> >> >>
>> >> >> Thanx,
>> >> >> Cary
>> >> >>
>> >> >>
>> >> >> "Daniel Reber" <dreber@dminconline.com> wrote:
>> >> >> >I guess that I should check my work better.
>> >> >> >try this:
>> >> >> >
>> >> >> >select
>> >> >> > e.serialnum,
>> >> >> > e.[name],
>> >> >> > s2.status,
>> >> >> > s2.racfid,
>> >> >> > s1.dateout,
>> >> >> > s2.datein
>> >> >> > from equipmnt
>> >> >> > left join (select
>> >> >> > max(dateout) dateout, uniqueID, serialnum
>> >> >> > from signout
>> >> >> > group by uniqueID,serialnum) s1 on s1.serialnum =
>> >> >e.serialnum
>> >> >> > left join (select
>> >> >> > uniqueID,
>> >> >> > serialnum,
>> >> >> > status,
>> >> >> > racfid,
>> >> >> > datein
>> >> >> > from signout ) s2 on s2.uniqueID= s1.uniqueID
>> >> >> > order by s1.dateout desc
>> >> >> >
>> >> >> >I changed the right joins to lefts joins like frye3k suggested.
>> >> >> >
>> >> >> >
>> >> >> >"Cary R" <cary.rotman@intria.com> wrote in message
>> >> >> >news:3baf56c8@news.devx.com...
>> >> >> >>
>> >> >> >> Sorry to bother you guys again, however I'm still having a few
>> >> >problems.
>> >> >> >>
>> >> >> >> The first error is due to the 's' after the first right join
>> >> >> >statement...so
>> >> >> >> I took that out.
>> >> >> >>
>> >> >> >> Then it states that I don't have s1.serialnum, yet when I put
it
>> in,
>> >> it
>> >> >> >gives
>> >> >> >> me a 'no group by clause' error.
>> >> >> >>
>> >> >> >> Also, there is an error on the e.serialnum line.
>> >> >> >>
>> >> >> >> Obviously, you can tell that I'm, by no means, on expert with
>SQL,
>> >> so
>> >> >> even
>> >> >> >> by playing around, I can't get the final product.
>> >> >> >>
>> >> >> >> Thanx again, Cary
>> >> >> >>
>> >> >> >>
>> >> >> >> "Cary R" <cary.rotman@intria.com> wrote:
>> >> >> >> >
>> >> >> >> >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?
>> >> >> >> >>> >> >
>>
>
>