get data from 3 different tables in a database

[nadiam]

hey guys. I've always had problems when it comes to getting data from multiple tables in a database and so now I have another one.

I want to get data from 3 different tables: contact1, child1 and spouse1.

columns in contact1 where i want to get data from:

contact_id | fullname |

columns in spouse1 where i want to get data from:

spouse_id | s_fullname | sspouse_name |

sspouse_name(spouse1) = fullname(contact1)
i want to get spouse_id and s_fullname

columns in child1 where i want to get data from:

child_id | c_fullname | primaryparent | secondaryparent |

primaryparent(child1) = fullname(contact1)
secondaryparent(child1) = s_fullname(spouse1)

Im trying to get these datas because I have a table to display a list of contacts and their dependents so something like this:

Name | Spouse | Children |
fullname | s_fullname | c_fullname |
fullname | s_fullname | c_fullname |

currently i have only one record for each table(allf filled up with the necessary values).

i tried this in the server(phpmyadmin):

SELECT
c.contact_id,c.fullname,
s.spouse_id,s.s_fullname,s.sspouse_name,
ch.child_id,ch.c_fullname,ch.primaryparent,ch.secondaryparent
FROM contact1 c
INNER JOIN spouse1 s ON c.fullname = s.sspouse_name
LEFT JOIN child1 ch ON c.fullname = ch.primaryparent

returned :

mysql returned an empty result set

even adding the WHERE clause produced the same result.

what am i doing wrong here?

[pritaeas]

Are you positive you have data that matches? Can you show some sample data?

[cgeier]

Please show your table relationships.

[nadiam]

contact1:

child1:

spouse1:

[urtrivedi]

Try with left outer join, and check whats missing

SELECT
c.contact_id,c.fullname,
s.spouse_id,s.s_fullname,s.sspouse_name,
ch.child_id,ch.c_fullname,ch.primaryparent,ch.secondaryparent
FROM contact1 c
left outer JOIN spouse1 s ON c.fullname = s.sspouse_name
LEFT JOIN child1 ch ON c.fullname = ch.primaryparent

Or check with trim

SELECT
c.contact_id,c.fullname,
s.spouse_id,s.s_fullname,s.sspouse_name,
ch.child_id,ch.c_fullname,ch.primaryparent,ch.secondaryparent
FROM contact1 c
INNER JOIN spouse1 s ON trim(c.fullname) = trim(s.sspouse_name)
LEFT JOIN child1 ch ON trim(c.fullname) = trim(ch.primaryparent)

But I feel table is not properly design. Joining on name is not a good idea

[nadiam]

But I feel table is not properly design.

yeah i kind of just threw it there.

Joining on name is not a good idea

use of id is much better right?

[nadiam]

okay so with this query:

SELECT
c.contact_id,c.fullname,
s.spouse_id,s.s_fullname,s.sspouse_name,
ch.child_id,ch.c_fullname,ch.primaryparent,ch.secondaryparent
FROM contact1 c
left outer JOIN spouse1 s ON c.fullname = s.sspouse_name
LEFT JOIN child1 ch ON c.fullname = ch.primaryparent

i got:

there are two records now, i just added the second because im testing something else. but that is the result i get.

with the second query:

SELECT
c.contact_id,c.fullname,
s.spouse_id,s.s_fullname,s.sspouse_name,
ch.child_id,ch.c_fullname,ch.primaryparent,ch.secondaryparent
FROM contact1 c
INNER JOIN spouse1 s ON trim(c.fullname) = trim(s.sspouse_name)
LEFT JOIN child1 ch ON trim(c.fullname) = trim(ch.primaryparent)

i got this:

only one record is selected.

do i need to create a query that has multiple queries in it? coz i was googling and found alot of people suggesting that to resolve wtv problems the person was facing. but it looked hella complicated.

[urtrivedi]

Now run combined version

If still record do not appear properly, then check spellings of values in all join columns

SELECT
c.contact_id,c.fullname,
s.spouse_id,s.s_fullname,s.sspouse_name,
ch.child_id,ch.c_fullname,ch.primaryparent,ch.secondaryparent
FROM contact1 c
left outer JOIN spouse1 s ON trim(c.fullname) = trim(s.sspouse_name)
LEFT JOIN child1 ch ON trim(c.fullname) = trim(ch.primaryparent)

[nadiam]

thanks urtivedi.

i altered the tables a bit and using id now instead of the names and also changed how the page that uses this query worked. because of the modification, instead of joining 3 tables i only need to get data from two tables. well, thats for this one query, i have 2 more queries i need to work on so ill leave this thread open for now.

cheers!