Inquiry on finding if a fields data is unique for the recordset
Expand Post »
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
Re: Inquiry on finding if a fields data is unique for the recordset
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"
Last edited by space1000; Mar 7th, 2008 at 10:28 am.
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
Last edited by AaronASterling; Mar 7th, 2008 at 12:01 pm. Reason: made a typo
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.
Re: Inquiry on finding if a fields data is unique for the recordset
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!
Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.
This thread is more than three months old
No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Offline 
Marked 
