Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Sep 2016
    Posts
    55
    Thanks
    20
    Thanked 0 Times in 0 Posts

    How to display data only related to single person among 3 tables?

    Hello sir,
    I am trying to display only related data to a single person from 3 tables. For better look please see the image with tables and my query with expected outcome.
    3 tables with expected outcome

    -sql_query.jpg

    I can display data related single person with the following query but displays with multiplying like cartesian product! what is not realistic.

    Code:
    SELECT * FROM customer , incomes, inctype WHERE customer.cust_id = 1 AND incomes.inctype_id = inctype.inctype_id ORDER BY inc_date DESC;
    I am new learner, please help me with this. By googling I have only learned with two tables but 3 tables not getting. Thank you for spending time for me.

  2. #2
    Senior Coder benanamen's Avatar
    Join Date
    Oct 2015
    Posts
    1,596
    Thanks
    10
    Thanked 162 Times in 157 Posts
    You will need to learn how to do a JOIN on the other tables.
    To save time, lets just assume I am almost never wrong.

    The XY Problem
    The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

    Make A Donation https://www.paypal.me/KevinRubio

  3. #3
    Master Coder sunfighter's Avatar
    Join Date
    Jan 2011
    Location
    Washington
    Posts
    8,080
    Thanks
    37
    Thanked 1,080 Times in 1,076 Posts
    Spaces added for clarification:
    Code:
    SELECT i.Inc_date, t.Inctype_type, i.Inc_amount, c.cust_no, c.Cust_name, i.Inc_receipt
    
    From Customer AS c
    
    LEFT JOIN incomes AS i ON c.cust_id = i.cust_id 
    
    LEFT JOIN Inctype AS t ON i.inctype_id = t.inctype_id
    Let me know if this works
    Evolution - The non-random survival of random variants.
    Physics is actually atoms trying to understand themselves.

  4. Users who have thanked sunfighter for this post:

    prohor (May 12th, 2019)

  5. #4
    New Coder
    Join Date
    Sep 2016
    Posts
    55
    Thanks
    20
    Thanked 0 Times in 0 Posts
    @sunfighter,
    Thank you a lot sir. It has worked what I was looking for. I could not figure out from online tutorial how to Left Join 2 tables with customer table.
    like you said
    Code:
    LEFT JOIN incomes AS i ON c.cust_id = i.cust_id 
    
    LEFT JOIN Inctype AS t ON i.inctype_id = t.inctype_id
    Thank you again sir.

  6. #5
    Senior Coder deathshadow's Avatar
    Join Date
    Feb 2016
    Location
    Keene, NH
    Posts
    3,422
    Thanks
    4
    Thanked 485 Times in 473 Posts
    Quote Originally Posted by prohor View Post
    Thank you a lot sir. It has worked what I was looking for. I could not figure out from online tutorial how to Left Join 2 tables with customer table.
    The use of "ON" for each join instead of "WHERE" for all of them trips up a lot of beginners. Easy mistake.
    “There are two ways of constructing a software design: One way is to make it so simple that there are obviously no deficiencies and the other way is to make it so complicated that there are no obvious deficiencies.” – C.A.R. Hoare, The 1980 ACM Turing Award Lecture
    http://www.cutcodedown.com


 

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •