Urgent help need

W

WebRaster

I have two table and I need to merge the results

The first table contains all the name of products (description, weight,
etc.) that I had to show
The second table contains the orders (quantity, discount rate etc.)

I had to show ALL the products and when his code is contained in the second
table (someone bought it) I want to show the discount rate and the quantity.


Thanks
 
J

JohnFol

Firstly, it sounds like the relationships are wrong, unless only 1 person
can by a product, or an order can contain only 1 product (Have a look at the
NWind example and the Products, Orders and Order Details tables)

To do what you want you need a single query with both tables in it. Just
place a criteria on the product code in table 2 and show the columns you
want.
 
W

WebRaster

My customer need to list in the result page the whole list of products in
the database and in this list He wants to specify the quantity and the
discount rates for the products the person XY have bought.

Thank for your carefull attention

Bye
 
J

John Spencer (MVP)

Perhaps you can use an outer join. It is hard to tell without knowing what
field(s) are in common between the two tables. In other words, what is the
relationship between the two tables.

SELECT *
FROM Table1 LEFT JOIN Table2
ON Table1.ProductID = Table2.ProductID

That should display all the fields from both tables. If there is no
corresponding record in Table2, there will be one row for that product where all
the table2 fields are blank.

If you are doing this in the query grid, drag from the table1 ProductID field to
the table2 productId. Double-click on the join line. Select the option that
says SHOW All from Table1 and only those from table2 that ...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top