SQLTeam.com | Weblogs | Forums

Cant figure of proper syntax


#1

Hello everyone I am trying to figure out a question that i just can't get the answer to i hope that someone can help. Ok

I need to look at entries in a database for all transactions on a certain date. Transactions are recorded in two tables, ‘transactions’ containing data about the transaction generally, and transaction_item, which holds each line item in a transaction. The common field (foreign key in the transaction_item table) that relates the two tables is ‘trans_id’. Show a SELECT query that will retrieve the following fields in each row of the result: From ‘transaction’, the trans_date, trans_total, and payment_method columns; from transaction_item, price and item_code and i have to join the two tables appropriately.

Any help would be greatly appreciated. Thanks in advance for any help.


#2

Looks like homework. You need to make an attempt and post what you've got, working or not.


#3

Yea you got me. Alright let me see what i can come up with and ill post it. Thanks for not making it too easy for me and I'm not being facetious i really do mean that. ok ill be back.


#4

Some general guidelines are OK. The general structure you need to use is this:

SELECT t.<col_name>, t.<col_name2>, ..., ti.<col_name1>, ...
FROM transaction AS t --it's common to "alias" tables so you can reference them with a shorter name
INNER JOIN transaction_item AS ti ??you need to fill in this part??

When using more than one table in a query, always prefix a column with its table's alias, even if a given column name only appears in one of the tables for now. That is definitely a best practice for writing SQL.