Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2008
    Posts
    4

    Unanswered: Conditional Join

    hi,

    I was just searching how to join tables conditionally but no answers had a solution for me.

    say when the column ModuleNr of maintable has value of 3, i need to get the Explanation from tableX and when ModuleNR of maintable has value of 5, i need to get the Explanation from tableY and so on...


    i appreciate any help
    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    usually this means a problem with the design.

    Could you explain more? Why it is designed like this, why you don't know which table to join to, how many tables might be in this condition, will this number change etc.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Pootle is right, this sounds like a design flaw to me.

    I'd start here on the solution.

    -PatP

  4. #4
    Join Date
    Jun 2008
    Posts
    4
    modulenr has about 7 values and i know which table to join according to modulenr but when setting up joins you name the tables like
    join table3 t3 on t3.id=maintable.ref
    join table4 t4 on t4.id=maintable.ref
    join table5 t5 on t5.id=maintable.ref

    and when selecting

    SELECT maintable.modulenr, ??? from maintable
    join bla bla
    join bla bla

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is this supertype\ subtype?

    Easiest (not most efficient but you aren't providing much info) is ignore modulenr and just do:
    Code:
    SELECT maintable.columns, othertablecolumns = COALESCE(othert1.columns, othert2.columns, othert3.columns)
    FROM maintable
    LEFT OUTER JOIN
    othert1 ON othert1.joincol = maintable.joincol
    LEFT OUTER JOIN
    othert2 ON othert2.joincol = maintable.joincol
    LEFT OUTER JOIN
    othert3 ON othert3.joincol = maintable.joincol

  6. #6
    Join Date
    Jun 2008
    Posts
    4
    thanx for the answers

    i am trying to change my view, is it possible to run different queries depending on a value ?? like

    if maintable.modulenr=5 then the query would be select id, name, amount from table5
    if maintable.modulenr=6 then the query would be select logicalref, vat from table6
    if maintable.modulenr=7 then the query would be select definition, date_ from table 7

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Who designed this database?

    If a thousand monkeys banging away at typewriters for a million years could produce the works of William Shakespeare, I'd estimate you had maybe 10 monkeys and five or six days working on this database design.

    We can help you, if you want to come up with something better. But otherwise, every piece of code you write is digging yourself farther into a hole.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No. You can put these into a procedure or multi line function and run a different query\ view depending on a parameter supplied. A view can only containt a single DML statement - you cannot include the sort of conditions and multiple statements you have there.

    Again, you might be hitting this from the wrong angle. If you can provide information about the structure we might be able to help at a deeper level.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ya know - like blindman said but not as subtle

  10. #10
    Join Date
    Jun 2008
    Posts
    4
    thank you very much for your interest.
    the database i mention belongs to our commercial accounting software.
    so i guess it is professionally built, the problem seems to be my sql knowledge.

    i am working out with the transactions of clients table CLFLINE and listing all the transactions of say CLIENTREF=1200
    many modules insert transactions to the table, for example modulenr=1 means its cash, modulenr=6 means bank check transaction and modulenr=4 means invoiced amount.

    i am trying to prepare a reporting services report on visual studio so that when i click on the transaction number, details will be displayed. so each module has different attributes like check has owner, duedate, bank name attributes while invoice module has items, prices, vat, how its paid attributes.

    CLIENTREF,MODULENR,TRANNO,DATE,AMOUNT
    1200,1,2501,20080617,4000
    1200,6,45456,20080618,3500
    1200,5,48750,200806,18,250

    so what i really need is a query, function whatever which will display the attributes of the corresponding table, the query must be executed according to the modulenr

    if CLFLINE.modulenr=5 then the query would be select id, name, amount from CASHTRANSACTIONSTABLE
    if maintable.modulenr=6 then the query would be select logicalref, vat from CHECKSTABLE
    if maintable.modulenr=7 then the query would be select definition, date_ from INVOICESTABLE

    thanks again for your help and time

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by igmox
    the database i mention belongs to our commercial accounting software.
    so i guess it is professionally built, the problem seems to be my sql knowledge.
    No, as I pointed out above, the problem is with the database design, not you.

    Quote Originally Posted by igmox
    so what i really need is a query, function whatever which will display the attributes of the corresponding table, the query must be executed according to the modulenr

    if CLFLINE.modulenr=5 then the query would be select id, name, amount from CASHTRANSACTIONSTABLE
    if maintable.modulenr=6 then the query would be select logicalref, vat from CHECKSTABLE
    if maintable.modulenr=7 then the query would be select definition, date_ from INVOICESTABLE
    Ok...well....maybe its a little bit of both. Or the problem might be in the business requirements you were given.
    You do realize, don't you, that when modulenr = 5 you are returning three fields (int, varchar, money), while when modulenr = 6 or 7 you are returning only two fields and with completely different datatypes?
    What reporting tool are you expecting to be able to handle variable result sets like this?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Sep 2005
    Posts
    161
    I wondered if this guy was a troll, but you can't make this stuff up!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •