SQL help

[saracrow]

I'm wondering if someone can give me a bit of help here. I need to query data from two tables into one result set. The end result is to gather information about a single product that a customer has purchased from us for the past 4 years.

I am using mySQL query browser.

The first table, "TransData", lists the customer's information (transaction number, receipt number, ect ) While the second table, TransProducts actually has what was purchased and the volume purchased.

Both tables share a common key "trans_ref_no"

So if i query the first table for the specific Customer_Num, by default i get a list of every Transaction_Ref number for that customer.

I want to then reference the second table to select from only those Trans_ref_no, when the product matches my query.

I thought I could nest a subquery into my query but I'm an amateur at best with databases.

I tried:

SELECT trans_ref_no, rec_no FROM TransData where customer_no = "0000001207"
(SELECT prod_no, gal_net, gal_gross FROM TransProducts where trans_ref_no = TransData.trans_ref_no)

Any suggestions or help would be greatly appreciated, and I hope I did a fair enough job describing this issue.

[Ragns]

My guess is that what you're trying to do is something like:

SELECT TransData.trans_ref_no, TransData.rec_no, TransProducts.prod_no, TransProducts.gal_net, TransProducts.gal_gross
FROM TransData
INNER JOIN TransProducts ON TransProducts.trans_ref_no = TransData.trans_ref_no
WHERE TransData.customer_no = '0000001207'

Syntax might be slightly different if you're using an old version of MySQL.

[saracrow]

Thanks so much. I see the error of my ways and wish I paid more attention in class ^^;

[Ragns]

[Furtwangler]

+1



(to the face, not my post count, although coincidentally it does go up by one)