0

I have a set of (not very well normalised or relational) tables named PLAN, GROUP, PRODUCT CLIENT

Most have linkage i.e. PLAN -> CLIENT on clno GROUP to PRODUCT on PRODCD

However, the linkage between PLAN and GROUP is tricky. A plan has 2 field of interest GRPNO and PRODCD.

What I want to do is if GRPNO != 0 then join GROUP on GRPNO. However if GRPNO = 0 then I want to join GROUP on PRODCD.

The frustrating thing is that the fileds I want to return in my queries are the same across the board I just need to be able to vary the join, or join the same table twice.

The best I can come up with is 2 queries and merge them using datasets, or possibly using a union.

Is there a nifty way to do this in one select?

I should point out I am access Foxpro over ODBC to do this.

Thank you!

Jon H
  • 1,061
  • 4
  • 13
  • 32
  • If you use a `UNION` you are effectively doing them in one query, which is a perfectly valid solution. Note that you could also try a conditional join if Foxpro supports it; http://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition for example. – dash Oct 21 '13 at 09:56
  • I had read that a union would result in 2 passes over the table - thus impacting performance. I am currently looking at an inner select on Group. – Jon H Oct 21 '13 at 09:57
  • You could use `UNION ALL` as you'd be fairly confident that the sets do not overlap hence no need for distinct. Additionally, if you run the UNION ALL query, and it's fast, then is that good enough? Usually better to optimise later :-) – dash Oct 21 '13 at 09:59
  • I hear that... getting it to work is the first step! I am trying out your conditional join suggestion as the syntax seems exactly what I want. – Jon H Oct 21 '13 at 10:08
  • Sadly I can't get the case on JOIN to work - it just gives me a syntax error. Oddly this: JOIN GROUP AS G ON PL.GRPNO = 0 AND G.PRODCD = PL.PRODCD - JOIN GROUP AS G ON PL.GRPNO != 0 AND G.GRPNO = PL.GRPNO doesn't error - but also doesn't return any results – Jon H Oct 21 '13 at 11:06
  • It doesn't return any rows because you are requesting the rows where PL.GRPNO=0 and PL.GRPNO!=0. I am a bit surprised that you don't get a syntax error from giving G as an alias twice. – Taemyr Oct 21 '13 at 11:15
  • @Taemyr I think your slightly missing what I was trying to do - the opposite conditions were intended to drive a different join. However your answer below DOES look promising. I too am surprised there is no syntax error. – Jon H Oct 21 '13 at 11:58
  • Very few ODBC's care about what you are trying to do. It's how the query is parsed that is important. In your query you are doing two joins, the first restricts the result to those rows where GRPNO is 0, the second takes that result and only keeps the rows where GRPNO is different from 0. End result; the rows needs to have GRPNO be equal to 0 at the same time as GRPNO is different from 0. – Taemyr Oct 21 '13 at 12:30

1 Answers1

1

You can do:

JOIN GROUP AS G ON
(PL.GRPNO = 0 AND G.PRODCD = PL.PRODCD) OR
(PL.GRPNO !=0 AND G.GRPNO = PL.GRPNO)

However it would surprise me if this is faster than using UNION ALL.

Taemyr
  • 3,407
  • 16
  • 26
  • Well it works. It is dog slow though. It used to return in ~3~4 seconds it now runs upto nearly a minute. I looked into UNION but I couldn't get it to play ball with TOP and orderby... – Jon H Oct 21 '13 at 12:36
  • Wrap the UNION in a subquerry. And note that orderby also has a performance impact. – Taemyr Oct 21 '13 at 12:41
  • Tried that.. Foxpro isn't having any of it. – Jon H Oct 21 '13 at 12:53
  • This works - it is very slow however. For me Union was not an option. I have ended up making 2 seperate SQL calls and merging them as DataTables. This takes ~4~6 seconds rather than ~45~50 seconds using this join. – Jon H Oct 21 '13 at 14:51