Member Avatar for 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?

  • 4 Contributors
  • 8 Replies
  • 296 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by nadiam

Recommended Answers

All 8 Replies

Member Avatar for pritaeas

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

Edited by pritaeas
Member Avatar for cgeier

Please show your table relationships.

Member Avatar for nadiam

contact1:
0e5b6252a7e20279895fa2ec55aa5e5f

child1:
264cf0327dec227f391ac39c52f18696

spouse1:
20b94b7d4e38f3a773efd0117c33a287

Member Avatar for 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

Member Avatar for 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?

Member Avatar for 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:
ea2df440d2a5581949ea3e753919121f

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:
9d94b66cf7efbf50f679569fcbf6524f

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.

Member Avatar for 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)
Edited by urtrivedi
Member Avatar for 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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.