Paul Paul - 1 month ago 7
MySQL Question

Querying multiple tables in MySQL

I am trying to fetch data from multiple tables depending on what is selected in a dropdown menu. My dropdown menu consists of a list of ID's (001, 002, etc).

Once a user selects one of them, I am using AJAX to dynamically fetch data depending on what was selected. I was able to fetch a single value depending on what was selected but having problems when multiple tables are involved.

My tables are set up like this:

Inventory table:

inven_ID (primary)
cost
description


Order table:

order_ID(primary)
orderdesc


Sale table:

inven_ID
order_ID
quantity
primary(inven_ID,order_ID)


My query is as follows:

$QRY = "SELECT
inven_ID,
order_ID,
cost,
description
FROM
Inventory,
Order,
Sale
WHERE Inventory.inven_ID = Sale.inven_id
AND Sale.order_ID = Order.order_ID
AND Order.order_ID ='".$q."'";


The
$q
represents the value from the dropdown menu (which I checked is valid). I am getting the error Column '
inven_ID
' in field list is ambiguous. Basically, when they select some order id from the drop down (say 001), it looks for
order_ID
in my Order table, and fetches the inven_ID/cost/description of that particular order ID.

Eg. if someone ordered parts xy, yz, xyz for cost 10,20,30.

Selecting 001 would bring up:
001 xy 10
001 yz 20
001 xyz 30


I think I am not joining tables properly since the error says its ambiguous.

Any help on this?

edit: yes that fixed the problem, quite obvious that I did not catch it.

Answer

In the column list of your select, you just need to specify which inven_ID you want to retrieve. For instance:

SELECT Inventory.inven_ID, ...