Categories: php, mysql, sql

how can select 3 records of foreign key with join in sql

2 answers

I'm sorry for my poor English . I have a table for project registration : like 

Type of service | Total Amount | prepayment | last payment  | Cost of tolls     web design   |      1000    |  1 (F-key) |  2 (F-key)    |   3 (F-key)

The second table for the status of payment and amount 

  id   |  amount  |   Payment status  |    Description   1    |   400    |        yes        |   for prepayment     2    |   600    |        no         |   for last payment    3    |    0     |        no         |   for Cost of tolls

Now my question is : How can I join two tables And pull out all THAT three columns ?

I've pulled out the prepayment column with this code

SELECT mzmx_request.service, mzmx_request.amount, (mzmx_payment.payment) as prepayment, (mzmx_payment.status) as prepayment_status  FROM mzmx_request JOIN mzmx_payment ON mzmx_request.prepayment = mzmx_payment.id

How do I add two more columns? 

JOIN mzmx_payment ON mzmx_request.payment = mzmx_payment.id JOIN mzmx_payment ON mzmx_request.pay_more = mzmx_payment.id
Received answers to this question:
The best answer according to the author of the question:

Just join three times. You already did it once. For example:

SELECT   r.service,    r.amount,   pp.payment as prepayment,   pp.status as prepayment_status,   lp.payment as last_payment,   lp.status as last_payment_status,   ct.payment as cost_of_tolls,   ct.status as cost_of_tolls_status  FROM mzmx_request r left JOIN mzmx_payment pp ON r.prepayment = pp.id left JOIN mzmx_payment lp ON r.lastpayment = lp.id left JOIN mzmx_payment ct ON r.costoftolls = ct.id

Since you are joining three times against the same table you'll need to alias the table. In this case I used the aliases pp, lp, and ct for prepayment, last payment, and cost of tolls respectively.

3 referenced fields are independent - so they needs in independent ref table copies:

SELECT * FROM main JOIN slave AS slave1 ON main.prepayment    = slave1.id JOIN slave AS slave2 ON main.last_payment  = slave2.id JOIN slave AS slave3 ON main.cost_of_tolls = slave3.id