extract data from multiple table with criteria

[genius_supreme]

Hi phpfreaks,

 

need help on the issue.

 

Im trying to get records of family (tblmember,tblspouse,tblchildren) all extracted based on two fileds (FamilyName,MembershipType) where FamilyName is from the tblmember.FamilyName and MembershipType (for all three tables) is user input ($membership)

 

i have tried all (joins) but i do not get the result that i want

 

my code 1:

SELECT tblmember.*,tblspouse.*,tblchildren.* FROM tblmember,tblspouse,tblchildren WHERE 
tblspouse.FamilyName=tblmember.FamilyName AND 
tblchildren.FamilyName=tblmember.FamilyName AND 
tblmember.MembershipType=$membership AND 
tblspouse.MembershipType=$membership AND 
tblchildren.MembershipType=$membership 
ORDER BY tblmember.FamilyName ASC

 

code 1 returns only records from tblchildren ( which is tblchildren.MemberName field)

 

code 2:

SELECT * FROM 
tblchildren JOIN tblspouse
ON tblchildren.FamilyName=tblspouse.FamilyName 
INNER JOIN tblmember
ON tblmember.FamilyName=tblchildren.FamilyName
WHERE tblmember.MembershipType=$membership AND 
tblspouse.MembershipType=$membership AND 
tblchildren.MembershipType=$membership 
ORDER BY tblmember.FamilyName ASC

 

returns only records from tblmember for the n number of records in tblchildren (tblchildren has 37 records and the result return will be 37 number of tblmember.MemberName)

 

 

Code 3:

SELECT tblmember.MemberName as `a`, tblmember.MembershipType, tblmember.FamilyName, 
tblspouse.MemberName as `b` , tblspouse.MembershipType, tblspouse.FamilyName, 
tblchildren.MemberName as `c`,tblchildren.MembershipType,tblchildren.FamilyName  
FROM tblspouse JOIN tblmember LEFT JOIN tblchildren ON tblchildren.FamilyName=tblmember.FamilyName WHERE tblmember.MembershipType=$membership";

 

Code 3 somehow manage to group by family but it shows in repeated manner where tblmember.MemberName of family-1 still appear with family-2 tblspouse.MemberName and family-1 tblchildren.MemberName (and more mixed-up families).

 

My records are displayed in tabled format where each family is group in one row. I want to display all the records from all 3 tables grouped by FamilyName where MembershipType=$membership  how can i accomplish this?

 

currently im using 3 different queries to extract

 

first query extract from tblmember where MembershipType=$membership 

second query extract from tblspouse where MembershipType=$membership

thrid query extract from tblchildren where  MembershipType=$membership

 

Is there a better way to combine all 3 queries into 1 which groups the result by field FamilyName? Each family members appear in the same row of the table.

 

Hope i didn't confuse y'all

[Barand]

Which columns are wanting to select from the tables. "*" tells us nothing about your table contents

[genius_supreme]

Which columns are wanting to select from the tables. "*" tells us nothing about your table contents

 

all three tables has many fields but only 3 are to be selected

MemberName

FamilyName

MembershipType

 

MembershipType is user select from dropdown menu. The result should show the MemberName from each tables grouped by FamilyName based on the MembershipType selected by user.

 

[SalientAnimal]

I'm not sure if this will help, but try a query along the lines of

 

SELECT * 
FROM 
Tablename1
, Tablename2
, Tablename3
WHERE
Tablename1.field1 = Tablename2.field1
AND Tablename2.field1 = Tablename3.field1

 

 

[Barand]

I rearranged your third query

 

SELECT tblmember.MemberName as `a`, tblmember.MembershipType, tblmember.FamilyName, 
    tblspouse.MemberName as `b` , 
    GROUP_CONCAT(tblchildren.MemberName) as `c`,  
FROM tblmember 
    LEFT JOIN tblspouse ON tblspouse.FamilyName=tblmember.FamilyName
    LEFT JOIN tblchildren ON tblchildren.FamilyName=tblmember.FamilyName 
WHERE tblmember.MembershipType=$membership
GROUP BY tblmember.FamilyName";

[genius_supreme]

I rearranged your third query

 

SELECT tblmember.MemberName as `a`, tblmember.MembershipType, tblmember.FamilyName, 
    tblspouse.MemberName as `b` , 
    GROUP_CONCAT(tblchildren.MemberName) as `c`,  
FROM tblmember 
    LEFT JOIN tblspouse ON tblspouse.FamilyName=tblmember.FamilyName
    LEFT JOIN tblchildren ON tblchildren.FamilyName=tblmember.FamilyName 
WHERE tblmember.MembershipType=$membership
GROUP BY tblmember.FamilyName";

 

You query is good but i just realized that your in query tblspouse and tblchildren depends on the where clause of tblmember for the MembershipType field. So it produces incorrect records though it was a good improvement where it grouped in families.

 

Each table has their own MembershipType. for example a child may not be the same membership as its father and the same goes to spouse. How do I insert the where clause for each table to have MembershipType=$membership. All three tables are filtered by MembershipType=$membership then grouped by tblmember.FamilyName.

 

 

Good tweak and great accomplishment. Hope you could help me more in this issue..

[Barand]

Because of the LEFT JOINS put the criteria in the join condition

 

SELECT tblmember.MemberName as `a`, tblmember.MembershipType, tblmember.FamilyName, 
    tblspouse.MemberName as `b` , 
    GROUP_CONCAT(tblchildren.MemberName) as `c`,  
FROM tblmember 
    LEFT JOIN tblspouse ON tblspouse.FamilyName=tblmember.FamilyName
        AND tblspouse.MembershipType = $membership
    LEFT JOIN tblchildren ON tblchildren.FamilyName=tblmember.FamilyName 
        AND tblchildren.MembershipType = $membership
WHERE tblmember.MembershipType=$membership
GROUP BY tblmember.FamilyName";

[genius_supreme]

Because of the LEFT JOINS put the criteria in the join condition

 

SELECT tblmember.MemberName as `a`, tblmember.MembershipType, tblmember.FamilyName, 
    tblspouse.MemberName as `b` , 
    GROUP_CONCAT(tblchildren.MemberName) as `c`,  
FROM tblmember 
    LEFT JOIN tblspouse ON tblspouse.FamilyName=tblmember.FamilyName
        AND tblspouse.MembershipType = $membership
    LEFT JOIN tblchildren ON tblchildren.FamilyName=tblmember.FamilyName 
        AND tblchildren.MembershipType = $membership
WHERE tblmember.MembershipType=$membership
GROUP BY tblmember.FamilyName";

 

aahh.... join condition use AND not WHERE.. my bad...i've been trying with WHERE clause in join condition... anyway it did the job my friend..

mission accomplished.. thanks..

 

[Christian F.]

Not exactly, the JOIN condition uses ON. However, multiple conditions are added together with AND, just as with the WHERE condition.

Glad you got it figured out though.

[genius_supreme]

Because of the LEFT JOINS put the criteria in the join condition

 

SELECT tblmember.MemberName as `a`, tblmember.MembershipType, tblmember.FamilyName, 
    tblspouse.MemberName as `b` , 
    GROUP_CONCAT(tblchildren.MemberName) as `c`,  
FROM tblmember 
    LEFT JOIN tblspouse ON tblspouse.FamilyName=tblmember.FamilyName
        AND tblspouse.MembershipType = $membership
    LEFT JOIN tblchildren ON tblchildren.FamilyName=tblmember.FamilyName 
        AND tblchildren.MembershipType = $membership
WHERE tblmember.MembershipType=$membership
GROUP BY tblmember.FamilyName";

 

I'm not getting the result correctly though it does filter by membership & group by family.

 

After some manual validation check, I found that this query does displays 2repeated results from tblchildren of the same family and did miss out a few.

 

my result :$membership=1;

Actual result:

tblmember=55

tblspouse=24

tblchildren=41

Total=120

 

but display:

tblmember=51 (missed out 5)

tblspouse=24

tblchildren=43 (repeat 2 and missed out 1)

total=118 (including 2 repeat)

[Barand]

What does this query return?

 

SELECT COUNT(DISTINCT FamilyName) FROM tblmember WHERE membership = 1

[genius_supreme]

Because of the LEFT JOINS put the criteria in the join condition

 

SELECT tblmember.MemberName as `a`, tblmember.MembershipType, tblmember.FamilyName, 
    tblspouse.MemberName as `b` , 
    GROUP_CONCAT(tblchildren.MemberName) as `c`,  
FROM tblmember 
    LEFT JOIN tblspouse ON tblspouse.FamilyName=tblmember.FamilyName
        AND tblspouse.MembershipType = $membership
    LEFT JOIN tblchildren ON tblchildren.FamilyName=tblmember.FamilyName 
        AND tblchildren.MembershipType = $membership
WHERE tblmember.MembershipType=$membership
GROUP BY tblmember.FamilyName";

 

I'm not getting the result correctly though it does filter by membership & group by family.

 

After some manual validation check, I found that this query does displays 2repeated results from tblchildren of the same family and did miss out a few.

 

my result :$membership=1;

Actual result:

tblmember=55

tblspouse=24

tblchildren=41

Total=120

 

but display:

tblmember=51 (missed out 5)

tblspouse=24

tblchildren=43 (repeat 2 and missed out 1)

total=118 (including 2 repeat)

 

my bad.. your code is good.. it was due to the data structure itself not your query.. it is simply superb.

 

Thanks alot Barand.

this is now solved.

[genius_supreme]

 

SELECT tblmember.MemberName as `a`, tblmember.MembershipType, tblmember.FamilyName, 
    tblspouse.MemberName as `b` , 
    GROUP_CONCAT(tblchildren.MemberName) as `c`,  
FROM tblmember 
    LEFT JOIN tblspouse ON tblspouse.FamilyName=tblmember.FamilyName
        AND tblspouse.MembershipType = $membership
    LEFT JOIN tblchildren ON tblchildren.FamilyName=tblmember.FamilyName 
        AND tblchildren.MembershipType = $membership
WHERE tblmember.MembershipType=$membership
GROUP BY tblmember.FamilyName";

 

i have done all the adjustment to the data(record) but still one particular name from tblmember does not print out using your code.

but if i run the filter from the database (MembershipType=1) that name appears in the list. And if i use another query

count(MemberName) WHERE MembershipType=1 that particular person is included in the total from table tblmember (54 records).

Strange!!

 

I have a function to count total members filtered (MembershipType=1) for each table and this is what i get

tblmember - 54

tblspouse - 24

tblchildren - 42

Total=120

but total printout on the page is 119 (short of that same particular person)

 

is there anything that i'm missing??

[Barand]

2 members with same family name?

 

try "GROUP BY tblmember.MemberName"

[genius_supreme]

2 members with same family name?

 

try "GROUP BY tblmember.MemberName"

 

Aahh you are right again.. 2 members with same family name.? Changed the Family name and all 120 records appear grouped by family..

 

Thanks a lot Barand

this is now officially closed 

 

[Barand]

As you have noticed, using keys like family name, which can easily be duplicated or misspelled, has its problems.

 

A better design would be to store the family name once only in the member table and use the member's id to define the relationship with spouse and children

+---------------+          +---------------+

|  tblmember    |          |  tblspouse    |

+---------------+          +---------------+

|  id          | ---+      |  id          |

|  memberName  |    |      |  spouseName  |

|  membership  |    |      |  membership  |

|  familyName  |    +----- |  member_id    |

+---------------+    |      +---------------+

                    |

                    |

                    |      +---------------+

                    |      |  tblchildren  |

                    |      +---------------+

                    |      |  id          |

                    |      |  childName    |

                    |      |  membership  |

                    +----<  |  member_id    |

                            +---------------+

[genius_supreme]

great work of creative art there barand.

i got your point..

thanks