226 votes
1 answers
php - Is there a way to show all data with matching fields in sql?
Get the solution ↓↓↓
I got 2 tables
cbmsH
hh_id | name | age
9437556 | John | 40
9886016 | Doe | 35
cbms
hh_id | name | age
9437556 | Peter | 41
9886016 | Tony | 35
9886016 | Thor | 32
9886016 | Loki | 30
And I'm expecting like this
hh_id | name | age
9437556 | John | 40
9437556 | Peter | 41
____________________________
hh_id | name | age
9886016 | Doe | 35
9886016 | Tony | 35
9886016 | Thor | 32
9886016 | Loki | 30
____________________________
ive tried this
SELECT cbmsh.hh_id AS head_hhid, cbmsh.barangay AS head_barangay, cbmsh.memno AS head_memno,cbmsh.last_name AS head_last ,cbmsh.first_name AS head_first ,cbmsh.mid_name AS head_mid,cbmsh.relationship,cbmsh.sex AS head_sex,cbmsh.birthdate AS head_birthdate,cbmsh.age AS head_age,cbmsh.occupation AS head_occupation,cbmsh.pwd_ind, cbms.hh_id AS mem_hhid, cbms.barangay, cbms.memno,cbms.last_name AS mem_last,cbms.first_name AS mem_first,cbms.mid_name AS mem_mid,cbms.relationship AS mem_relationship,cbms.sex AS mem_sex,cbms.birthdate AS mem_birthdate,cbms.age AS mem_age,cbms.occupation AS mem_occupation,cbms.pwd_ind
FROM cbmsh, cbms
WHERE cbmsh.hh_id = cbms.hh_id
GROUP BY head_hhid
But it's showing only 1 data like this. Instead of showing the all the records with same hh_id
hh_id | name | age
9886016 | Doe | 35
9886016 | Tony | 35
Can someone help me this? Any help will be appreciated! Good day everyone
Undefined asked
453
votes
Answer
Solution:
You have two problems.
- You don't want to
JOINthe tables, you want toUNIONthem.JOINis for combining related rows of the two tables into a single row in the results. But you want to keep all the rows separate. - You don't want
GROUP BY, which is used for aggregating rows with the same value of a column into a single row (e.g. when counting or summing across rows). If you want to keep the rows separate but together, you useORDER BY.
So the query should be
SELECT *
FROM (
SELECT hh_id, name, age
FROM cbmsh
UNION
SELECT hh_id, name, age
FROM cbms
) x
ORDER BY hh_id
Share solution ↓
Additional Information:
Date the issue was resolved:
Link To Answer People are also looking for solutions of the problem: installation failed, reverting ./composer.json and ./composer.lock to their original content
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.