Inquiry on finding if a fields data is unique for the recordset

[Zangow]

Hello all. This is my first post. I'm a software engineer with a general knowledge of SQL, but am no where close to an expert, let alone an intermediate.

My current issue is I'm working on a query which combines 2 tables:
TableA
UniqueID Auto Increment
ProjectName String
<Extra Data>
TableB
UniqueID Auto Increment
ForeignKey Points to TableA.UniqueID - NOT Unique
<Extra Data>

More than one record (ForeignKey) in TableB can point to the same record (UniqueID) in TableA.

I have a web page that's printing out all the entries in TableB in a simple table format. However, the <Extra Data> needs to be ignored and the cells "blacked out" if the ForeignKey occurs more than once in TableB. So basically I'm looking for a way to add to my SELECT statement so when I start reading through the recordset in ASP, there's a column for each record to say how many times that ForeignKey exists in TableB.

I hope this makes sense as I've had trouble trying to put it into words. Any help is greatly appreciated :-D

[Ramy Mahrous]

I'll read your question again but can you please provide us with sample input and the output, it'd help me

[Ramy Mahrous]

However, the <Extra Data> needs to be ignored and the cells "blacked out" if the ForeignKey occurs more than once in TableB

It should be occur more than once in table b as the relationship is 1-to-many!!
I think you must support me with inputs and output..

[space1000]

Please suplly the original query. I find it a bit hard to imagine what the result must be.

The possible sollution solution is a "select ProjectName,extra_data) with a union, where the first sql statement should be "having count(A.UniqueID = 1) and the second should have "select ProjectName ,count(*) from .... having count(A.UniqueID) > 1"

[AaronASterling]

You want something like:

SELECT columnyouwant1,...,columnyouwantn, rowcount = COUNT(B.ForeignKey)
FROM TableB AS B
GROUPBY B.ForeignKey

Load that into a temporary table. lets call it T. then you can select based on rowcount
to handle the two cases differently. You may be able to do it in one query but this will be faster then using cursors

[space1000]

@AaronASterling

dont' know about that, That is a static solution and should be recalculated every time.

[AaronASterling]

@AaronASterling

dont' know about that, That is a static solution and should be recalculated every time.

It seems to be exactly what the original poster wanted. What exactly do you mean by 'its a static solution?' Wouldn't any query need to be recalculated every time to ensure accurate real time information? based on rereading the original post, he/she wouldn't even need to use a temporary table. the result my query gives returns the extra column asked for for the asp processing.

[Zangow]

Thanks everyone. I ended up using Aaron's suggestion and doing a quick query in the beginning that stores the column count in an array that I can access later while printing out the table.

I can see how the split second could effect a system that is constantly updating since the rows may not match up by the 2nd call. And sure, there's ways to make sure they do match up, but I decided in this case where data is rarely updated and also not viewed very often, it's not worth the headache at this time. It is working as I wanted it to though, so thanks again!