simple query help needed
-
I am needing to create a query that exceeds my current knowledge though I imagine is not too complex.
I want to return results in the following format:
ID, FundName
____________________
1, Fund 1
2, Fund 2
3, Fund 3 (empty)
4, Fund 4
________
Where the "(empty)" string is added to any fund name that does *not* have a single "program" associated with it.
I have the following tables that define the relationship between funds and programs:
Fund, ProgramFundsList, Program
ProgramFundList is an intersection table that allows the many to many relationship with Program and Fund IDs.. The Fund and Program tables each have IDs, and name columns ....
Hope the question makes sense. Can anyone tell me how to construct this query?
-
Why not
SELECT FD.[ID], PG.Program
FROM Fund FD
INNER JOIN ProgramFundsList PF ON FD.Fund = PF.Fund
LEFT JOIN Program PG ON PF.Program = PG.Program
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Am I missing something? I don't see anything in this code that does the check I am looking for and adds "(empty)" string to every Fund name that has no programs associated with it. Thanks .. but unless I am mistaken there needs to be something a little more going on in the query.
-
Adding my 0.1c to AJ Ahrens work...
Why not
SELECT FD.[ID], PG.Program + (CASE WHEN PG.Program IS NULL '(empty)' ELSE '' END)
FROM Fund FD
INNER JOIN ProgramFundsList PF ON FD.Fund = PF.Fund
LEFT JOIN Program PG ON PF.Program = PG.Program
HTH
-
This variation of Luigi's solution should work:
SELECT DISTINCT
FD.[ID], FD.FundName + (CASE WHEN PF.FundID IS NULL THEN '(empty)' ELSE '' END)
FROM Fund FD
LEFT JOIN ProgramFundsList PF ON FD.ID = PF.FundID
----------------------------------------------------------
1 Fund 1
2 Fund 2
3 Fund 3 (empty)
4 Fund 4
Regards,Bob Monahon
-
Just ou of curiosity, why not make a new column for the (empty) string instead of concatenating it to the fund part?? seems to be more of a presentation problem than anything else.
like :
SELECT DISTINCT
FD.[ID], FD.FundName, (CASE WHEN PF.FundID IS NULL THEN '(empty)' ELSE '' END) as Empty
FROM Fund FD
LEFT JOIN ProgramFundsList PF ON FD.ID = PF.FundID
-
thanks for the examples!!
the answer about concatenating - is that the query populates a dropdown list UI element - and thus must be a single column presentation
... it might be better to display the "empty" funds elsewhere (leaving them out) - but that may not be an option so this is step one ...thx again all.
-
actually looking at your comment - now I think I understand your question better ...you mean performance wise - it would be better to concatenate the string in the C# - than in the query itself?
-
I don't think it would make that much of a difference.
Unless sql server is on another machine than the web server, then you could see where it is best to balance the load... but unless you have tens of thansounds of hits every day and that the list is very long you won't see a lot if any difference.
However in the case where this query is used extremely often and that its data is not changed often. Then you can query the server only once and save the dataset in memory on the server and requry it when the data is updated. The best exemple I can give you is the list of states and country on a purchase page. The data can be loaded a few times per second but it barely ever changes so there's no need to requery the server everytime a user loads the page.
But a simple action like that can save a bunch of resources on the data server (millions of roundtrips and all the requeries... even when the data is in memory, it still takes time to process it).
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply