SQL join left and right

Question

This is my query:

select distinct * 
from purchase_records a 
inner join purchase_items b on a.id = b.purchase_id 
left join transactions c on a.id= c.purchase_id

It fetches records from two tables. From the left it gets one record, and from the right it gets the number of records which are more then one.

The issue is that when the table returns the record, it matches the exact record but repeat right side of the row according to the left side of row.

How can i get one record in the left and many records in the right? I want my left join to show one record, and right to show as many records as there are in the database.

the join table in the database

Your sql script fetches data from 3 tables but your description fetches from 2 tables ? I'm not sure what you expect from a join but the left side of the join will repeat for every record found in the right side of the join, — P.Salmon, Nov 10 '16 at 12:02
Refer (http://stackoverflow.com/questions/23786401/why-do-multiple-table-joins-produce-duplicate-rows) — Karthik Arthik, Nov 10 '16 at 12:14
Remove that image. Give us sample table data and the expected result as well formatted text. — jarlh, Nov 10 '16 at 13:07

score -2 · Answer 1 · answered Nov 10 '16 at 11:21

-2

you can try like as....

    select A.*,B.add1,C.add2 from(
    select 1 id,'A' name union
    select 2 ,'B'  union
    select 3 ,'C' union
    select 4 ,'D') A
    left join 
    (select 1 id,'A' name,'z' add1 union
    select 5 ,'B' ,'zz'  union
    select 6 ,'C' ,'zzz' union
    select 4 ,'D' ,'zzzz')B on A.id=B.id
    right join
    (select 1 id,'A' name,'1z' add2 union
    select 5 ,'B' ,'1zz'  union
    select 6 ,'C' ,'1zzz' union
    select 4 ,'D' ,'1zzzz')C on C.id=A.id

or like..

select A.columns,B.columns,C.columns from your_table1 A
left join your_table2 B on A.id=B.id
right join your_table3 C on C.id=A.id

answered Nov 10 '16 at 11:21

Sukhvindra Singh

200
2
14

please explain your logic - OP is clearly stuck with this. Code-only answers just encourage a copy/paste culture, but it would be good for OP and others reading to learn and understand the reasoning as well :-) – ADyson Nov 10 '16 at 11:30
there is nothing roket science to explane..this is a very simple left and right join example only – Sukhvindra Singh Nov 10 '16 at 11:37
perhaps, but the OP (and future readers) may not understand the underlying principles behind why your example might work and theirs doesn't. If you're not experienced with SQL it may not be obvious. Also you have given two examples so explaining the difference might also be helpful. – ADyson Nov 10 '16 at 11:49
@usman..i dont know your data/cas i just take some demo rows in example..post some of your data and what you want in result – Sukhvindra Singh Nov 10 '16 at 12:00

SQL join left and right

1 Answers1