CODEWITHSUNDEEP
sqlphpmyadmin
itsfawwaz
itsfawwaz

Reputation: 117

Retrieving data from multiple tables in phpmyadmin

I have 3 tables, all of them are linked with PK and FK. The tables are acc_details , acc_info , acc_bill

TABLE : acc_info

ID     |    acc_no         |  rate
______________________________________
1      |         00001     |     0
2      |         00002     |     21
3      |         00003     |     21
4      |         00004     |     21 

TABLE : acc_details

id_dls |    acc_type       |  address         |  **ID**
________________________________________________________
1      |        store      |     pekan        |  1
2      |     water plant   |     kuantan      |  2
3      |       store       |     kuantan      |  2
4      |      pump house   |     kuantan      |  4


TABLE : acc_bill

id_bill |        acc_no    |  charge_1        |  charge_2
________________________________________________________
1       |       00001      |     20.00        |  12.00
2       |       00002      |     15.00        |  16.00
3       |       00004      |    200.00        |  22.00

PK ---> ID , acc_no FROM acc_info FK ---> ID FROM acc_details , acc_no FROM acc_bill

i was hoping the outcome to be like this

address     |    acc_no       |    rate   |  charge_1  | charge_2
_________________________________________________________________________________
pekan       |    00001        |     0     |   20.00    |   12.00
kuantan     |    00002        |    21     |   15.00    |   16.00
kuantan     |    00004        |    21     |  200.00    |   22.00

now,i've tried this

SELECT address , acc_no , rate , charge_1 , charge_2
FROM acc_info , acc_details , acc_bill
WHERE acc_info.id = acc_details AND acc_info.acc_no = acc_bill.acc_no

But error #1052 - Column 'acc_no' in field list is ambiguous Anybody can help?

Upvotes: 0

Views: 4351

Answers (1)

David162795
David162795

Reputation: 1866

SELECT address ,
       acc_info.acc_no , 
       rate , 
       charge_1 , 
       charge_2
FROM acc_info 
JOIN acc_details ON acc_info.id = acc_details.id
JOIN acc_bill ON acc_info.acc_no = acc_bill.acc_no

it means acc_no exists in two joined tables, thus you need to specify which acc_no you want

Upvotes: 1

Related Questions