Dont want every row i second table

Hi!

I've got 2 tables that are related

Tabel 1
CatID
CatName

Table2
SubID
CatID
SubName
(So this table can have more than one related CatID, but I would like to only retreive 1 row)

I've tried the diffrent Joins to get what I want, what I like is for the second table to show only one of more rows that are related to first table.

SQL SYNTAX

SELECT DISTINCT
dbo.Tb_DataPaNummer.CID, dbo.Tb_DataPaNummer.OrtID, dbo.Tb_DataPaNummer.PaNummer, dbo.Tb_DataPaNummer.GoodToKnowCom,
dbo.Tb_DataPaNummer.Station, dbo.Tb_DataPaNummer.Laptop, dbo.Tb_DataUser.UserID, dbo.Tb_DataUser.UserName
FROM dbo.Tb_DataPaNummer LEFT OUTER JOIN
dbo.Tb_DataUser ON dbo.Tb_DataPaNummer.CID = dbo.Tb_DataUser.CID
WHERE (dbo.Tb_DataPaNummer.OrtID = 2)

Please help

SELECT

e.CatID, d.CatName, e.SubID, e.SubNameFROM(SELECT a.CatID, a.CatName, c.MaxSubIDFROM CatTable1 aINNERJOIN(SELECT b.CatID,MAX(b.SubID)as MaxSubIDFROM CatSubTable1 b

GROUP

BY b.CatID) cON a.CatID= c.CatID) dINNERJOIN CatSubTable1 eON d.MaxSubID=e.SubID|||Thank you so much!!!!

dont understand the following locking behavior

can't figure out why the following locking scenario works the way it
does:

spid 1:
start transaction
select a row from table T1 with updlock
result: see an Update lock for the row and index key

spid 2:
query for the same row as in session 1
result: the query succeeds

spid 3:
do same as spid 1
result: blocks trying to get Update lock on the index key for row

Now I do the query again in spid 2 and it blocks trying to get a Shared
lock on the index key, and it's waiting for spid3. why was it able to
get a shared lock on a index key that had an Update lock at first but
then can't get the same shared lock when somebody else is also trying
to get an update lock?"hendershot" <eyusim@.yahoo.com> wrote in message
news:1108174034.874699.16400@.o13g2000cwo.googlegro ups.com...
> can't figure out why the following locking scenario works the way it
> does:
> spid 1:
> start transaction
> select a row from table T1 with updlock
> result: see an Update lock for the row and index key
> spid 2:
> query for the same row as in session 1
> result: the query succeeds
> spid 3:
> do same as spid 1
> result: blocks trying to get Update lock on the index key for row
> Now I do the query again in spid 2 and it blocks trying to get a Shared
> lock on the index key, and it's waiting for spid3. why was it able to
> get a shared lock on a index key that had an Update lock at first but
> then can't get the same shared lock when somebody else is also trying
> to get an update lock?

I tried this quickly, and I had no problems running a query for the row from
spid 2, even with multiple other spids all blocked and waiting for a lock on
the same index key. Perhaps you can post a script which reproduces what
you're seeing? And what version/servicepack are you using (I used Enterprise
Edition 2000 SP3a)?

Simon|||I was using Standard Edition, it says version 8.00.194 (not 100% sure
about the last 3 digits). does SQL Server 8 = SQL Server 2000?

I'll try to post a script on monday.
thanks.|||hendershot (eyusim@.yahoo.com) writes:
> I was using Standard Edition, it says version 8.00.194 (not 100% sure
> about the last 3 digits). does SQL Server 8 = SQL Server 2000?

8.00.194 = The original version of SQL 2000 with no service packs. You
can download the current service pack SP3a from
http://www.microsoft.com/sql/downloads/2000/sp3.asp. I stronly recommend
you download and install this service pack, since it includes a fix for
the Slammer worm.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||installed SP3a and now I am at version 8.00.760, still seeing the same
locking behavior.

this is what I am doing in Query Analyzer w/the Northwind database:

Window #1:

begin transaction
select description from categories
with (updlock) where categoryid = 1

Window #2:

select description from categories
where categoryid = 1

Window #3:

begin transaction
select description from categories
with (updlock) where categoryid = 1

I run the sql in window #1 and that gets the Update locks on the row
and key, run the sql in window #2 and it runs just fine, run the sql in
window #3 and it 's waiting to get update lock on the key which is what
you'd expect. now when I run the query in window #2 again it hangs
waiting on a Shared lock for the key.

If I change description to "*" it behaves the same way, if I change the
query in window 2 to get another field (categoryid) it's just fine.

Any ideas of why it can't get the shared lock when somebody else is
waiting for update lock?

Erland Sommarskog wrote:
> hendershot (eyusim@.yahoo.com) writes:
> > I was using Standard Edition, it says version 8.00.194 (not 100%
sure
> > about the last 3 digits). does SQL Server 8 = SQL Server 2000?
> 8.00.194 = The original version of SQL 2000 with no service packs.
You
> can download the current service pack SP3a from
> http://www.microsoft.com/sql/downloads/2000/sp3.asp. I stronly
recommend
> you download and install this service pack, since it includes a fix
for
> the Slammer worm.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||hendershot (eyusim@.yahoo.com) writes:
> installed SP3a and now I am at version 8.00.760, still seeing the same
> locking behavior.
> this is what I am doing in Query Analyzer w/the Northwind database:
> Window #1:
> begin transaction
> select description from categories
> with (updlock) where categoryid = 1
> ...
> If I change description to "*" it behaves the same way, if I change the
> query in window 2 to get another field (categoryid) it's just fine.
> Any ideas of why it can't get the shared lock when somebody else is
> waiting for update lock?

I don't really have a good answer, but I note that only happens if the
query in window #2 attempts to access a text column. (Or ntext or image.)

It may be a bug. I can't reproduce the problem in SP4 beta. However, when
I test in the latest drop of SQL 2005, the query does not get blocked.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||yes, I see that it doesn't occur with certain types. I guess I'll
just have to wait for SP4.

thanks|||hendershot (eyusim@.yahoo.com) writes:
> yes, I see that it doesn't occur with certain types. I guess I'll
> just have to wait for SP4.

It does not seem to be fixed in SP4. In SQL 2005, it is.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

don't show blank spaces caused by hidden duplicates in detail row.

I'm trying to solve on how to remove the blank row spaces in a table that is caused by hide duplicates. I have a lot of this record duplicate and it is hidden, but it display a very long blank spaces of rows before getting to the next group. Is there a way not to print the hidden row?Advance Thanks to the one giving the answers... :)
"Dzus" wrote:
> I'm trying to solve on how to remove the blank row spaces in a table that is caused by hide duplicates. I have a lot of this record duplicate and it is hidden, but it display a very long blank spaces of rows before getting to the next group. Is there a way not to print the hidden row?
>|||Hi Dzus,
For a whole row to be 'removed' in this way, I believe the 'hide duplicates' value must be true for every cell in the row.
Regards
Chris
"Dzus" wrote:
> Advance Thanks to the one giving the answers... :)
> "Dzus" wrote:
> > I'm trying to solve on how to remove the blank row spaces in a table that is caused by hide duplicates. I have a lot of this record duplicate and it is hidden, but it display a very long blank spaces of rows before getting to the next group. Is there a way not to print the hidden row?
> >
> >|||Hi Chris...
That was pretty fast response and thanks... But the problem still exists. I have already 'hide duplicates' on every cells on the same row that you've instructed, and it still show the blank rows...Hmmm...
This record that i've "hide duplicates" for every cells in the same row belongs to the detail grouping. Did i miss anything in editing the visibility properties under the details grouping?
Thanks again...,
Dzus
"Chris McGuigan" wrote:
> Hi Dzus,
> For a whole row to be 'removed' in this way, I believe the 'hide duplicates' value must be true for every cell in the row.
> Regards
> Chris
> "Dzus" wrote:
> > Advance Thanks to the one giving the answers... :)
> >
> > "Dzus" wrote:
> >
> > > I'm trying to solve on how to remove the blank row spaces in a table that is caused by hide duplicates. I have a lot of this record duplicate and it is hidden, but it display a very long blank spaces of rows before getting to the next group. Is there a way not to print the hidden row?
> > >
> > >|||You can simply delete the row from the table. Or, you can set the row
visibility to false.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dzus" <Dzus@.discussions.microsoft.com> wrote in message
news:7F901BAE-9054-4650-8574-3F7F3227479A@.microsoft.com...
> I'm trying to solve on how to remove the blank row spaces in a table that
> is caused by hide duplicates. I have a lot of this record duplicate and it
> is hidden, but it display a very long blank spaces of rows before getting
> to the next group. Is there a way not to print the hidden row?
>|||Hi Brian
Thanks for the reply.
But if i delete or set the row visibility to false, then all the 1st instance of the values i have for that particular row will not be displayed.
Actually i need the 1st instance of a value to be displayed and the duplicate ones not to be shown. I have set the properties to 'hide duplicates' for all the available fields in that particular row, and the thing is it managed to hide but it print out as a blank row before printing the next available record. Can i removed this blank row?
Thanks.
"Brian Welcker [MSFT]" wrote:
> You can simply delete the row from the table. Or, you can set the row
> visibility to false.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dzus" <Dzus@.discussions.microsoft.com> wrote in message
> news:7F901BAE-9054-4650-8574-3F7F3227479A@.microsoft.com...
> > I'm trying to solve on how to remove the blank row spaces in a table that
> > is caused by hide duplicates. I have a lot of this record duplicate and it
> > is hidden, but it display a very long blank spaces of rows before getting
> > to the next group. Is there a way not to print the hidden row?
> >
> >
>
>|||If you are setting hide duplicates on every single field, then you are
creating a group. You should create a group instead and delete the detail
row.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dzus" <Dzus@.discussions.microsoft.com> wrote in message
news:65D6F017-225C-4671-8957-F09815A34AAD@.microsoft.com...
> Hi Brian
> Thanks for the reply.
> But if i delete or set the row visibility to false, then all the 1st
> instance of the values i have for that particular row will not be
> displayed.
> Actually i need the 1st instance of a value to be displayed and the
> duplicate ones not to be shown. I have set the properties to 'hide
> duplicates' for all the available fields in that particular row, and the
> thing is it managed to hide but it print out as a blank row before
> printing the next available record. Can i removed this blank row?
> Thanks.
> "Brian Welcker [MSFT]" wrote:
>> You can simply delete the row from the table. Or, you can set the row
>> visibility to false.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Dzus" <Dzus@.discussions.microsoft.com> wrote in message
>> news:7F901BAE-9054-4650-8574-3F7F3227479A@.microsoft.com...
>> > I'm trying to solve on how to remove the blank row spaces in a table
>> > that
>> > is caused by hide duplicates. I have a lot of this record duplicate and
>> > it
>> > is hidden, but it display a very long blank spaces of rows before
>> > getting
>> > to the next group. Is there a way not to print the hidden row?
>> >
>> >
>>|||Oh yes...! Thanx a lot Brian... Maybe now i'm more understand between the group and the details...
"Brian Welcker [MSFT]" wrote:
> If you are setting hide duplicates on every single field, then you are
> creating a group. You should create a group instead and delete the detail
> row.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dzus" <Dzus@.discussions.microsoft.com> wrote in message
> news:65D6F017-225C-4671-8957-F09815A34AAD@.microsoft.com...
> > Hi Brian
> >
> > Thanks for the reply.
> >
> > But if i delete or set the row visibility to false, then all the 1st
> > instance of the values i have for that particular row will not be
> > displayed.
> >
> > Actually i need the 1st instance of a value to be displayed and the
> > duplicate ones not to be shown. I have set the properties to 'hide
> > duplicates' for all the available fields in that particular row, and the
> > thing is it managed to hide but it print out as a blank row before
> > printing the next available record. Can i removed this blank row?
> >
> > Thanks.
> >
> > "Brian Welcker [MSFT]" wrote:
> >
> >> You can simply delete the row from the table. Or, you can set the row
> >> visibility to false.
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Dzus" <Dzus@.discussions.microsoft.com> wrote in message
> >> news:7F901BAE-9054-4650-8574-3F7F3227479A@.microsoft.com...
> >> > I'm trying to solve on how to remove the blank row spaces in a table
> >> > that
> >> > is caused by hide duplicates. I have a lot of this record duplicate and
> >> > it
> >> > is hidden, but it display a very long blank spaces of rows before
> >> > getting
> >> > to the next group. Is there a way not to print the hidden row?
> >> >
> >> >
> >>
> >>
> >>
>
>