How can we help you today? How can we help you today?

Joining multiple tables

Hi,

I have some troubles with Prompt when joining multiple tables. Let's say that I have three tables: Visit, VisitProduct and VisitCustomer.
Visit table has the following PK:
Visit (number of the visit)
Employee (employee code)

VisitProduct has the following PK (first two fields are FK from Visit table):
Visit
Employee
Product (product code)

VisitCustomer has the following PK (first two fields are FK from Visit table):
Visit
Employee
Customer (customer code)

Now, if write the following query (join of the first table with the second and the third):

select * from Visit v
inner join VisitProduct vp
on v.Visit = vp.Visit and v.Employee = vp.Employee
inner join VisitCustomer vc
on v.Visit = vc.Visit and v.Employee = vc.Employee

the SQL Prompt offers me the correct join condititon when I type the join statement for the table VisitProduct (second table). But, when I am typing join for the third table, SQL Prompt only offers me the join condition between the VisitProduct (second table) and VisitCustomer (third table), but it does not offer me the join condition between Visit (first table) and VisitCustomer (third table). And even the join condititon offered between the second and the third table only consists of only one field (e.g. vp.Visit = vc.Visit) and not the complete key.

Any help?

Janez
Janez
0

Comments

1 comment

Sort by
  • Tilman
    Hi Janez,
    Now, if write the following query (join of the first table with the second and the third):

    select * from Visit v
    inner join VisitProduct vp
    on v.Visit = vp.Visit and v.Employee = vp.Employee
    inner join VisitCustomer vc
    on v.Visit = vc.Visit and v.Employee = vc.Employee

    the SQL Prompt offers me the correct join condititon when I type the join statement for the table VisitProduct (second table). But, when I am typing join for the third table, SQL Prompt only offers me the join condition between the VisitProduct (second table) and VisitCustomer (third table), but it does not offer me the join condition between Visit (first table) and VisitCustomer (third table).
    We did this on purpose to avoid cluttering things up too much with lots of join conditions. I've added a suggestion to our system, now. For the moment you could restructure your query, i.e. put 'Visit' in the middle, then you should get the correct suggestions.
    And even the join condititon offered between the second and the third table only consists of only one field (e.g. vp.Visit = vc.Visit) and not the complete key.
    That is because there is no actual foreign key relationship between VisitProduct and VisitCustomer.

    Regards,

    Tilman
    Tilman
    0

Add comment

Please sign in to leave a comment.