Help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with query

Author

Topic

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-25 : 04:57:28

Hello all....!

I have two tables for with following attributes
Table 1: Trades
Attributes: id, stock, qty, buy_bill, buy_price, sell_bill, sell_price

Table 2: Bills
Attributes: bnumber, bdate.

Now I want to frame a query which return the following columns:

stock,qty,buy_bill,buy_date,buy_price,sell_bill,sell_date,sell_price

where
buy_date display the date from Bills table, where bnumber=buy_bill
and
sell_date will display the date from the Bills table, whose bnumber=sell_bill

but i want to display all the records from the Trades table, not less not more..

can anyone help me on this??

i am not able to frame a query on this

Trust in Technology mate .....'Not in Human Beings'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-25 : 05:04:30

Select stock, qty, buy_bill,
(Select bdate from Bills b where b.bnumber = t.buy_bill) as buy_date,
buy_price,sell_bill,
(Select bdate from Bills b where b.bnumber = t.sell_bill) as sell_date,
sell_price
from Trades t

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-25 : 05:07:36

Try something like this (I think they have better performance)

SELECT		t.Stock,
		t.Qty,
		t.Buy_Bill,
		CASE WHEN t.Buy_Bill = b.bNumber THEN b.bDate ELSE NULL END Buy_Date,
		t.Buy_Price,
		t.Sell_Bill,
		CASE WHEN t.Sell_Bill = b.bNumber THEN b.bDate ELSE NULL END Sell_Date,
		t.Sell_Price
FROM		Trades t
LEFT JOIN	Bills b ON b.bNumber IN (t.Buy_Bill, t.Sell_Bill)

Or simpler

SELECT		t.Stock,
		t.Qty,
		t.Buy_Bill,
		buy.bDate Buy_Date,
		t.Buy_Price,
		t.Sell_Bill,
		sell.bDate Sell_Date,
		t.Sell_Price
FROM		Trades t
LEFT JOIN	Bills buy ON buy.bNumber = t.Buy_Bill
LEFT JOIN	Bills sell ON sell.bNumber = t.Sell_Bill

Peter Larsson
Helsingborg, Sweden

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-25 : 05:08:24

thanx mitra.....are mihi tech try karat hoto......
just that i was writing the queries in the FROM clause....
hehehe...ridiculous.....

thnx....what do u do??

Trust in Technology mate .....'Not in Human Beings'

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-25 : 05:13:00

quote:

Originally posted by Peso

Try something like this (I think they have better performance)

SELECT		t.Stock,
		t.Qty,
		t.Buy_Bill,
		CASE WHEN t.Buy_Bill = b.bNumber THEN b.bDate ELSE NULL END Buy_Date,
		t.Buy_Price,
		t.Sell_Bill,
		CASE WHEN t.Sell_Bill = b.bNumber THEN b.bDate ELSE NULL END Sell_Date,
		t.Sell_Price
FROM		Trades t
LEFT JOIN	Bills b ON b.bNumber IN (t.Buy_Bill, t.Sell_Bill)

Or simpler

SELECT		t.Stock,
		t.Qty,
		t.Buy_Bill,
		b.bDate Buy_Date,
		t.Buy_Price,
		t.Sell_Bill,
		b.bDate Sell_Date,
		t.Sell_Price
FROM		Trades t
LEFT JOIN	Bills buy ON buy.bNumber = t.Buy_Bill
LEFT JOIN	Bills sell ON sell.bNumber = t.Sell_Bill

Peter Larsson
Helsingborg, Sweden

Thnx for the solution....but i dont know much of the SQL so may be the conditions u have given in the query......

the solution that the other guy has given is perfect

Trust in Technology mate .....'Not in Human Beings'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-25 : 05:21:06

Sorry. I prefixed my columns wrong

SELECT		t.Stock,
		t.Qty,
		t.Buy_Bill,
		buy.bDate Buy_Date,
		t.Buy_Price,
		t.Sell_Bill,
		sell.bDate Sell_Date,
		t.Sell_Price
FROM		Trades t
LEFT JOIN	Bills buy ON buy.bNumber = t.Buy_Bill
LEFT JOIN	Bills sell ON sell.bNumber = t.Sell_Bill

Harsh's solution uses two correlated subqueries, which can be very slow compared to this JOIN.

But hey, as long as it work it is good enough.

Peter Larsson
Helsingborg, Sweden

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-25 : 05:27:31

quote:
Originally posted by Peso

Harsh's solution uses two correlated subqueries, which can be very slow compared to this JOIN.

But hey, as long as it work it is good enough.

Peter Larsson
Helsingborg, Sweden

Yes...i will surely try urs too....
that one looks fine
thanks Peter

Trust in Technology mate .....'Not in Human Beings'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-25 : 05:29:58

quote:
Harsh's solution uses two correlated subqueries, which can be very slow compared to this JOIN

I knew that the moment I submitted it. But I was tricked by this:

"i want to display all the records from the Trades table, not less not more.."

which made me think LEFT JOIN would bloat the data.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-25 : 05:35:28

Yes, I thought so too, but isn't it better to retrieve duplicates and try to remove them with DISTINCT, rather than the query errors with "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."?
I know one can put MIN or MAX in the subquery, but then the query is even slower.
I think we need some more input from OP if there are multiple rows in Bills table for each record in Trades table.

SELECT DISTINCT	t.Stock,
		t.Qty,
		t.Buy_Bill,
		buy.bDate Buy_Date,
		t.Buy_Price,
		t.Sell_Bill,
		sell.bDate Sell_Date,
		t.Sell_Price
FROM		Trades t
LEFT JOIN	Bills buy ON buy.bNumber = t.Buy_Bill
LEFT JOIN	Bills sell ON sell.bNumber = t.Sell_Bill

Peter Larsson
Helsingborg, Sweden

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-25 : 06:07:56

quote:
Originally posted by Peso

Yes, I thought so too, but isn't it better to retrieve duplicates and try to remove them with DISTINCT, rather than the query errors with "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."?
I know one can put MIN or MAX in the subquery, but then the query is even slower.
I think we need some more input from OP if there are multiple rows in Bills table for each record in Trades table.
SELECT DISTINCT t.Stock,
t.Qty,
t.Buy_Bill,
buy.bDate Buy_Date,
t.Buy_Price,
t.Sell_Bill,
sell.bDate Sell_Date,
t.Sell_Price
FROM Trades t
LEFT JOIN Bills buy ON buy.bNumber = t.Buy_Bill
LEFT JOIN Bills sell ON sell.bNumber = t.Sell_Bill

Peter Larsson
Helsingborg, Sweden

it says it is getting a missing operator in this
buy.bNumber = t.Buy_Bill
LEFT JOIN Bills sell ON sell.bNumber = t.Sell_Bill

Trust in Technology mate .....'Not in Human Beings'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-25 : 06:25:53

If you are using Microsoft Access rather than SQL Server, you must use this

SELECT DISTINCT	t.Stock,
		t.Qty,
		t.Buy_Bill,
		buy.bDate AS Buy_Date,
		t.Buy_Price,
		t.Sell_Bill,
		sell.bDate AS Sell_Date,
		t.Sell_Price
FROM		Trades AS t
LEFT JOIN	Bills AS buy ON buy.bNumber = t.Buy_Bill
LEFT JOIN	Bills AS sell ON sell.bNumber = t.Sell_Bill

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources