I have 4 tables (ROUTBOM, HARD, MAT, ROUT). In the main table ROUTBOM I have ID, PROD\_NO and TYPE columns. I had to select based on TYPE and (ID or PROD\_NO). Conditions below. Can this be made with an inner join - with condition before join?

Database is FoxPro

```
using (OleDbConnection con = new OleDbConnection(@"provider=VFPOLEDB;Data source=c:\test\Data"))

```

Data:
ROUTBOM

```
id      qty      prod_no    Type    Name
20322   0.15000             2       ???    get name from HARD
        2.00000 0066773     4       ???    get name from ROUT
37500   4.00000             2       ???    get name from HARD
29750   4.00000             1       ???    get name from MAT

```

HARD TABLE

```
ID      NAME
20322   H68NK0005
37500   HAS2-30XX-H HYBRID POWDER

```

MAT TABLE

```
ID      NAME
29750   NEOPRENE (McMASTER 8694K61)
02125   SPRING STEEL STRIP .008'' X .50'' X 60''

```

ROUT TABLE

```
ID      NAME
0066773 L.V. DOOR ARC PROOF J4
000198  DEVEL. L.V. DOOR J4

```

Required output

```
id      qty      prod_no    Type    Name
20322   0.15000             2       H68NK0005
        2.00000 0066773     4       L.V. DOOR ARC PROOF J4
37500   4.00000             2       HAS2-30XX-H HYBRID POWDER
29750   4.00000             1       NEOPRENE (McMASTER 8694K61)

```

Query:

Conditions:

```
if id is not null and type 2 JOIN ROUTBOM.id with MAT.id to get the name of material
if id is not null and type 1 JOIN ROUTBOM.id with HARD.id to get the name of hadware
if id is empty and type 4 JOIN ROUTBOM.prod_no with ROUT.id to get the name of part

```

Can be done in one query ? I do not know where condition have to be applied. Once I make the first JOIN the query bring the whole data from HARD.

Query for row data

```
@"SELECT    
                                    t0.Id, 
                                    t1.Name,
                                    t0.Per_Router,
                                    t0.Prod_No,
                                    t0.Rout_No,
                                    t0.Seq,
                                    t0.Type
                                    FROM {0} t0 , "Routbom", "Hardware", "Material", "Rout");

```