DBF database conditional join correct syntax DBF - edited

Question

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");

Answer 1

This is what I understand from your limited explanation:

string sql = @"SELECT Id,Name,Per_Router,Prod_No,Rout_No,Seq,Type
from
(
    Select t0.*, t1.Name as Name 
    from RoutBom t0
    inner join Mat t1 on t0.Id = t1.Id
    where t0.type = 2
    union 
    Select t0.*, t1.Name as Name 
    from RoutBom t0
    inner join Hard t1 on t0.Id = t1.Id
    where t0.type = 1
    union
    Select t0.*, t1.Name as Name 
    from RoutBom t0
    inner join Rout t1 on t0.Prod_no = t1.Id
    where t0.type = 4 and Empty(Nvl(t0.id,''))
) tmp
";
DataTable tbl = new DataTable();
using (OleDbConnection con = new OleDbConnection(@"provider=VFPOLEDB;Data source=c:\test\Data"))
using (OleDbCommand cmd = new OleDbCommand(sql, con))
{
 con.Open();
 tbl.Load(cmd.ExecuteReader());
}
// Do something with tbl

It might be easier for you to use Linq instead. Check Tom Brother's Linq To VFP and VFP Entity Framework.

This is another way of doing that, and also takes care of the union problem:

void Main()
{
    string sql = @"SELECT *, 
    Cast(icase(
    type = '1', (select Name from Mat t1 where t0.Id = t1.Id),
    type = '2', (select Name from Hard t1 where t0.Id = t1.Id),
    empty(nvl(id,'')) and type='4', (select Name from Rout t1 where t0.Prod_No = t1.Id),
    '') as c(100)) as Name
    from RoutBom t0";
    DataTable tbl = new DataTable();
    using (OleDbConnection con = new OleDbConnection(@"provider=VFPOLEDB;Data source=c:\Test\Data"))
    using (OleDbCommand cmd = new OleDbCommand(sql, con))
    {
        con.Open();
        tbl.Load(cmd.ExecuteReader());
    }
    // Do something with tbl
    tbl.Dump(); // you said LinqPad above
}