How to display 3 tables data in single json array?

SELECT shop_registration.shop_id, shop_name, shop_category
FROM  `shop_registration`
INNER JOIN system ON system.shop_id = shop_registration.shop_id
INNER JOIN bill ON bill.sys_id = system.sys_id
WHERE  `yb_id` =  '$yb_id' GROUP BY shop_id

I want the data from bill, system and shop_registration wherein I will get bill count, bill, date from bill and shop_id,shop_name,shop_category.
So far from the above query I'm getting shop_name, shop-id and shop-category based on yb_id
Shruti UpariWeb DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill BachPresident and Btrieve GuruCommented:
I'm not sure I fully understand the problem.  The SELECT statement indicates what you want.  Simply add the fields from the other tables to get them returned, too.  Something like this might be close:
SELECT shop_registration.shop_id, shop_name, shop_category, bill.count, bill.bill, bill.date
FROM  `shop_registration`
INNER JOIN system ON system.shop_id = shop_registration.shop_id
INNER JOIN bill ON bill.sys_id = system.sys_id
WHERE  `yb_id` =  '$yb_id' GROUP BY shop_id
Note that INNER JOINs will ONLY return data if data exists on BOTH sides of the JOIN.
0
lenamtlCommented:
Try using LEFT JOIN instead of INNER JOIN may help you to get what you need ...

Here is how the SQL join work, this will help you to understand the difference between
Inner JOIN, Left JOIN, Right JOIN, Outer JOIN...
https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

You can test the request from PhPMyAdmin or other MySQL tools so you will see if the result is what you need
then apply the request to your code and check the log for errors if you don't have the correct result
(also check for typo errors)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shruti UpariWeb DeveloperAuthor Commented:
Thank you for the solution. It helped me alot. But I have another question with respect to json format, will you please help me with that ?
0
Kyle SantosQuality AssuranceCommented:
Hi Shruti,

If you received a solution then you should close your question.  Here are the steps on how to close your question:  http://support.experts-exchange.com/customer/en/portal/articles/2527982-how-do-i-close-my-question-?b_id=44

If you need more help then you should ask a new question so the experts can help you again.  Click the big blue 'Ask a Question' button in the menu navigation to ask another question.

Let me know if this works or not.
0
Shruti UpariWeb DeveloperAuthor Commented:
Thank you for the help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.