Select Without Sub Query to Second Table? 1

[Auguy]

I'm loading a vb.net datagridview from an SQL 2000 table. Some of the records have an exception code that I display in the grid. This is working fine with the code in the main table. Now I am moving the exception codes to their own table to allow more than one code per record and I only want to show the main record once. I would like to display a blank if there are no exceptions, the exception code if there is only one, and "* Multiple" if there is more than one. I'm not sure how to approach selecting the records to generate this text. I could do a sub query of the exception codes using the value of a count() and group by PK to generate the text and then join it to the main table. Is there a better method of doing something like this that wouldn't use a sub query? FYI: most of the records will not have any exception codes.

Auguy
Sylvania/Toledo Ohio

 

[TamarGranor]

I haven't worked with SQL 2000, so it's possible something here won't work in that version.

I'm assuming that you have a primary key for the main table named iID; obviously, substitute the actual name of that field. I'm also assuming that, in the one-to-many Exceptions table, you called that field iMainID. Finally, assuming the exception code field is called cExcepCode

SELECT CASE nExcepCount WHEN 0 THEN '' WHEN 1 THEN Exceptions.cExcepCode ELSE '* Multiple' END
  FROM Main 
  JOIN (SELECT iMainID, CNT(*) AS nExcepCount 
          FROM Exceptions 
          GROUP BY iMainID) ExcepCount
    ON Main.iID = ExcepCount.iMainID
  JOIN Expections 
    ON Main.iID = Exceptions.iMainID

You'll need to add whatever other fields you want to the field list.

Tamar

 

[Auguy]

Thanks Tamar. That's what I was thinking of, but it would have taken me a while to get there. BTW I've enjoyed and benefited from reading your hints and posts for many years (sorry) back to the FoxPro Advisor days.

Auguy
Sylvania/Toledo Ohio

 

[drlex]

Given the line in the OP "ome of the records have an exception code", might I suggest that the equi-join in TamarGranor's code is changed to a Left join, so as not to exclude those records with no exception codes?

soi là, soi carré

 

[Auguy]

Nice catch drlex.

Auguy
Sylvania/Toledo Ohio