Inner join example

[Zeff]

Hi all,

I have a relational database, where all info is kept in separate tables
and just the id's from those tables are stored in one central table
(tblMaster)...
I want to perform a query, so all data from the separate tables is shown
in a view (instead of the reference id's pointing to the separate tables...)
I have some troubles formulating the SQL statement:
I tried:

SELECT tblMaster.*, tblHelper1.*, tblHelper2.*
FROM (tblMaster INNER JOIN tblHelper1 ON
tblMaster.to_idHelper1=tblHelper1.id) INNER JOIN tblHelper2 ON
tblMaster.to_idHelper2=tblHelper2.id;

But I don't get any results...

Any suggestions?

Thanks in advance,

Zeff

[Jason Lepack]

Your query will select all records from these three tables where a
specific Master record has both a helper1 and helper2.

What does this query return?

SELECT *
FROM tblMaster
WHERE to_idHelper1 is not null and to_idHelper2 is not null

If this query returns no records then your issue is that you aren't
doing what you want to do.

Otherwise, what are the structures of the tables? Are the fields
included in the joins of the same data type?

Cheers,
Jason Lepack

On Apr 11, 10:03 am, Zeff <[e-mail]:

Hi all,

I have a relational database, where all info is kept in separate tables
and just the id's from those tables are stored in one central table
(tblMaster)...
I want to perform a query, so all data from the separate tables is shown
in a view (instead of the reference id's pointing to the separate tables...)
I have some troubles formulating the SQL statement:
I tried:

SELECT tblMaster.*, tblHelper1.*, tblHelper2.*
FROM (tblMaster INNER JOIN tblHelper1 ON
tblMaster.to_idHelper1=tblHelper1.id) INNER JOIN tblHelper2 ON
tblMaster.to_idHelper2=tblHelper2.id;

But I don't get any results...

Any suggestions?

Thanks in advance,

Zeff

[Zeff]

Dear Jason,

Thanks for your reply. Inner joins show only corresponding records in
all tables...

I found that this shows all coresponding rows:
SELECT tblMaster.*, tbl1.naam1, tbl2.naam2, tbl3.naam3
FROM tbl3 INNER JOIN (tbl2 INNER JOIN (tbl1 INNER JOIN tblMaster ON
tbl1.id_table1=tblMaster.ref_tbl1) ON tbl2.id_table2=tblMaster.ref_tbl2)
ON tbl3.id_table3=tblMaster.ref_tbl3;

Table schemes:
tblMaster(master_id,date,ref_tbl1,ref_tbl2, ref_tbl3)
tbl1(id_tbl1, naam1)
tbl2(id_tbl2, naam2)
tbl3(id_tbl3, naam3)

Can you help me with the syntax for LEFT JOINS, to display also non
corresponding records from tblMaster?

Many thanks,

Zeff

Jason Lepack wrote:

Your query will select all records from these three tables where a
specific Master record has both a helper1 and helper2.

What does this query return?

SELECT *
FROM tblMaster
WHERE to_idHelper1 is not null and to_idHelper2 is not null

If this query returns no records then your issue is that you aren't
doing what you want to do.

Otherwise, what are the structures of the tables? Are the fields
included in the joins of the same data type?

Cheers,
Jason Lepack

On Apr 11, 10:03 am, Zeff <[e-mail]:

>Hi all,

I have a relational database, where all info is kept in separate tables
and just the id's from those tables are stored in one central table
(tblMaster)...
I want to perform a query, so all data from the separate tables is shown
in a view (instead of the reference id's pointing to the separate tables...)
I have some troubles formulating the SQL statement:
I tried:

SELECT tblMaster.*, tblHelper1.*, tblHelper2.*
FROM (tblMaster INNER JOIN tblHelper1 ON
tblMaster.to_idHelper1=tblHelper1.id) INNER JOIN tblHelper2 ON
tblMaster.to_idHelper2=tblHelper2.id;

But I don't get any results...

Any suggestions?

Thanks in advance,

Zeff

[Jason Lepack]

What exactly are you trying to accomplish and why must it be done this
way?

You have a many to many relationship, so I would store the data like
this:

(stores the master info)
tbl_master:
master_id
master_date

(stores the name info)
tbl_names:
name_id
name_text

(links a name to a master)
tbl_master_names:
master_id
name_id

The best way I can think to do it if you can't change your design is
either with nesting subqueries or 3 queries (both work in the same
way).

Create this query and call it step1
SELECT tblMaster.*, tbl1.naam1
FROM tbl1 RIGHT JOIN tblMaster ON tbl1.id_table1 = tblMaster.ref_tbl1;

Create this query and call it step2
SELECT step1.*, tbl2.naam2
FROM step1 LEFT JOIN tbl2 ON step1.ref_tbl2 = tbl2.id_table2;

This is your final result
SELECT step2.*, tbl3.naam3
FROM step2 LEFT JOIN tbl3 ON step2.ref_tbl3 = tbl3.id_table3;

As one big conglomerate query it looks like this:
SELECT step2.*,
tbl3.naam3
FROM (SELECT step1.*,
tbl2.naam2
FROM (SELECT tblMaster.*,
tbl1.naam1
FROM tbl1
RIGHT JOIN tblMaster
ON tbl1.id_table1 = tblMaster.ref_tbl1) AS
step1
LEFT JOIN tbl2
ON step1.ref_tbl2 = tbl2.id_table2) AS step2
LEFT JOIN tbl3
ON step2.ref_tbl3 = tbl3.id_table3;

That will perform what you want, though I think it would be better to
revisit your design.

Cheers,
Jason Lepack

On Apr 12, 3:08 am, Zeff <[e-mail]:

Dear Jason,

Thanks for your reply. Inner joins show only corresponding records in
all tables...

I found that this shows all coresponding rows:
SELECT tblMaster.*, tbl1.naam1, tbl2.naam2, tbl3.naam3
FROM tbl3 INNER JOIN (tbl2 INNER JOIN (tbl1 INNER JOIN tblMaster ON
tbl1.id_table1=tblMaster.ref_tbl1) ON tbl2.id_table2=tblMaster.ref_tbl2)
ON tbl3.id_table3=tblMaster.ref_tbl3;

Table schemes:
tblMaster(master_id,date,ref_tbl1,ref_tbl2, ref_tbl3)
tbl1(id_tbl1, naam1)
tbl2(id_tbl2, naam2)
tbl3(id_tbl3, naam3)

Can you help me with the syntax for LEFT JOINS, to display also non
corresponding records from tblMaster?

Many thanks,

Zeff

Jason Lepack wrote:

Your query will select all records from these three tables where a
specific Master record has both a helper1 and helper2.

What does this query return?

SELECT *
FROM tblMaster
WHERE to_idHelper1 is not null and to_idHelper2 is not null

If this query returns no records then your issue is that you aren't
doing what you want to do.

Otherwise, what are the structures of the tables? Are the fields
included in the joins of the same data type?

Cheers,
Jason Lepack

On Apr 11, 10:03 am, Zeff <[e-mail]:

Hi all,

I have a relational database, where all info is kept in separate tables
and just the id's from those tables are stored in one central table
(tblMaster)...
I want to perform a query, so all data from the separate tables is shown
in a view (instead of the reference id's pointing to the separate tables...)
I have some troubles formulating the SQL statement:
I tried:

SELECT tblMaster.*, tblHelper1.*, tblHelper2.*
FROM (tblMaster INNER JOIN tblHelper1 ON
tblMaster.to_idHelper1=tblHelper1.id) INNER JOIN tblHelper2 ON
tblMaster.to_idHelper2=tblHelper2.id;

But I don't get any results...

Any suggestions?

Thanks in advance,

Zeff- Hide quoted text -

- Show quoted text -