Joining Dummy Columns

Is it possible to create a join on two tables when one of the columns has been derived.

e.g.

Table 1

ID Name Price Qty

1 Sand 12,00 2

Table 2

Week Top_Sales

1 24

e.g.

Select a.ID, a.price, a.qty, a.qty *a.price as "Total Sales"

from Table 1 a

inner join on table 2 b

on

b.top_sales = Total Sales

Is this possible to perform this join without creating a temporary table or a view?

Sure, but you can't use the named column from the SELECT clause because that name hasn't been materialized yet. You will need to do:

Select a.ID, a.price, a.qty, a.qty *a.price as "Total Sales"
from Table 1 a
inner join on table 2 b
on b.top_sales = a.qty *a.price

This seems a very dangerous query, if you ask me though. In your table2, why not include the primary key of the top sales rows? Then you wouldn't have to do this wonky join and you could join on keys. I guess this because you have a fixed amount for the top seller, so you had to have calculated it from the quantity and price somewhere.

Joining and grouping using SQL

I have two tables... Table1 and table2 and I need to reconcile them
with each other.

Table1 has the fields Product number, invoice number, price, vat
amount and total.

Table2 has the same data but in a slightly different format...

It has Product Number, invoice number, Price and type.
Type will say Vat or sale and amount will be the vat amount or sale
amount

What is on one row in Table1, will be spread accross 2 rows in Table2.

It means that Invoice number is not unique in Table2.

How do I either group the data in Table2, so I can join it with Table1
or make Table2 the same format as Table1.

If there is something else you can think of to help me, by all means
suggest away.

Regards,
Ciarn[posted and mailed, please reply in news]

Ciar?n (chudson007@.hotmail.com) writes:
> Table1 has the fields Product number, invoice number, price, vat
> amount and total.
> Table2 has the same data but in a slightly different format...
> It has Product Number, invoice number, Price and type.
> Type will say Vat or sale and amount will be the vat amount or sale
> amount
> What is on one row in Table1, will be spread accross 2 rows in Table2.
> It means that Invoice number is not unique in Table2.
> How do I either group the data in Table2, so I can join it with Table1
> or make Table2 the same format as Table1.

SELECT ...
FROM Table1 t1
JOIN (SELECT ProductNumber, InvoiceNumber, Price = SUM(Price)
FROM Table2
GROUP BY ProductNumber, InvoiceNumber) AS t2
ON t1.ProductNumber = t2.ProductNumber
AND t1.InvoiceNumber = t2.InvoiceNumber

This may not be exactly what you need; your request is a bit vague. If
you want more help, I suggest that you include:

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'd suggest looking at the design of your tables first.

chudson007@.hotmail.com (Ciar?n) wrote in message news:<7f9b6870.0411040804.3e32e925@.posting.google.com>...
> I have two tables... Table1 and table2 and I need to reconcile them
> with each other.
> Table1 has the fields Product number, invoice number, price, vat
> amount and total.
> Table2 has the same data but in a slightly different format...
> It has Product Number, invoice number, Price and type.
> Type will say Vat or sale and amount will be the vat amount or sale
> amount
> What is on one row in Table1, will be spread accross 2 rows in Table2.
> It means that Invoice number is not unique in Table2.
> How do I either group the data in Table2, so I can join it with Table1
> or make Table2 the same format as Table1.
> If there is something else you can think of to help me, by all means
> suggest away.
> Regards,
> Ciarn