CODEWITHSUNDEEP
mysqlsql
ashlesha
ashlesha

Reputation: 109

JDBC Retrieve with Multiple Joins

I have three tables with one to many mapping i.e. Name has one to many relation with Shipping and Payment (** Ignore Case Sensitivity**)

    Table parent has 3 columns    id, name, name_id
    Table shipping has 3 columns  id, shippingName,name_id
    Table payment has 3 columns   id, paymentName, name_id

when I execute the below query 

    SELECT shipping.shippingName,payment.paymentName,parent.name,Parent.name_id
    FROM parent 
    JOIN shipping
    ON  parent.name_id = shipping.name_id
    JOIN payment
    ON  parent.name_id = payment.name_id
    WHERE parent.name_id= '3'

The above returns 

   shipping Name             paymentName          name          name_id
   Fedex                      Credit              AA              3
   Fedex                      Debit               AA              3
   USPS                       Credit              AA              3
   USPS                       Debit               AA              3

But what I wanted was 

   shipping Name             paymentName          name          name_id
   Fedex                      Credit              AA              3
   USPS                       Debit               AA              3

Is there a way? Or do I need to set any mapping between shipping and payment table

  Shipping Table

   ID        ShippingName    name_id
   1             FEDEX         3
   2             USPS          3

   Payment Table

   ID        PaymentName    name_id
   1         Credit           3
   2         Debit            3

   Parent Table

   ID        name        name_id
   1001        A           1
   1002        B           2
   1003       AA            3

Fedex doesnot have payment options.. Parent has one to many with Shipping && Parent has one to many with Payment.. There is no relation between Payment and Shipping My question was is it even possible to have that resultset I wanted in one database call or should be calling twice with 2 different queries ( Parent+Shipping) (Parent+Payment)

Upvotes: 0

Views: 689

Answers (3)

Slowcoder
Slowcoder

Reputation: 2120

Shipping Table

ID        ShippingName    name_id  Payment_ID
1             FEDEX         3         1
2             USPS          3         2

Payment Table

ID        PaymentName   
1         Credit        
2         Debit        

Parent Table

ID        name        name_id
1001        A           1
1002        B           2
1003       AA           3

Query:

SELECT shipping.shippingName,payment.paymentName,parent.name,Parent.name_id
FROM parent 
JOIN shipping
ON  parent.name_id = shipping.name_id
JOIN payment
ON  shipping.Payment_ID = payment.ID
WHERE parent.name_id= '3';

Upvotes: 1

user2069121
user2069121

Reputation: 1

Where PaymentName IN ('Credit',Debit') AND Name_ID =3

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

just add this 

GROUP BY shipping.shippingName ,payment.paymentName

at the end of your query

Upvotes: 0

Related Questions