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?
 
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
 
Bill BachPresidentCommented:
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
 
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 SantosCustomer RelationsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.