1. Registered User
Join Date
Jun 2008
Posts
4

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. King of Understatement
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Pootle is right, this sounds like a design flaw to me.

I'd start here on the solution.

-PatP

4. Registered User
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. King of Understatement
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. Registered User
Join Date
Jun 2008
Posts
4

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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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.

8. King of Understatement
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. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Ya know - like blindman said but not as subtle

10. Registered User
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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.

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?

12. Registered User
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
•