I cannot write this 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)

I cannot write this query

Author

Topic

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-28 : 00:05:03

hello friends....

i have a table named Bills as follows:
Bills(id, account, bnumber, bdate, vendor);

i have two other tables which are quite similar in structure as follows:
BillBuy(id, bnumber, stock, qty, price);
BillSell(id, bnumber, stock, qty, price);

I want to write a query that will return the folllowing:
AllTransactions(bnumber, date_, vendor_, buy_stock, buy_qty, buy_price, sell_stock, sell_qty, sell_price);

EXPLAINATION:
Here the Bills detarmines a bill which conatin details of all transaction performed i.e. Buy and Sell.
(number of Buy Transaction != number of Sell Transactions), I maintain separate tables for storing the Buy and Sell Transactions, as BillBuy and BillSell.

WHAT IS ACTUALLY NEEDED IN THE QUERY:
I want to retrieve all the Transactions in all the Bills for a given account say 'xyz'. (Each Bill Belongs to an Account for which it is created).

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

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-28 : 00:28:07

You didn't say whether the tables are related on bnumber or id, it seems like probably bnumber but if it's id then change the ON clauses of the joins to use id instead.

SELECT Bills.bnumber, Bills.bdate as date_, Bills.vendor as vendor_
    , b.stock as buy_stock, b.qty as buy_qty, b.price as buy_price
    , s.stock as sell_stock, s.qty as sell_qty, s.price as sell_price
FROM Bills
LEFT JOIN BillBuy b ON Bills.bnumber = b.bnumber
LEFT JOIN BillSell s ON Bills.bnumber = s.bnumber
WHERE Bill.account = 'xyz'

Edit - my original post had Bill everywhere instead of Bills

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-28 : 01:42:45

Well thanx for the answer, but i am getting 'Syntax error in JOIN operation' on the bold part in the below query.

SELECT B.bnumber, B.bdate as date_, B.vendor as vendor_
    , b.stock as buy_stock, b.qty as buy_qty, b.price as buy_price
    , s.stock as sell_stock, s.qty as sell_qty, s.price as sell_price
FROM (Bills AS B
LEFT JOIN BillBuy AS b ON (B.bnumber=b.bnumber))
LEFT JOIN BillSell AS s ON B.bnumber=s.bnumber
WHERE B.account = 'xyz'

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 01:46:36

1) Remove the MS Access JOIN style, ie remove all paranthesis in the JOIN!
2) You can't alias two tables to the same name!


SELECT		b.bnumber,
		b.bdate as date_,
		b.vendor as vendor_,
		bb.stock as buy_stock,
		bb.qty as buy_qty,
		bb.price as buy_price,
		bs.stock as sell_stock,
		bs.qty as sell_qty,
		bs.price as sell_price
FROM		Bills b
LEFT JOIN	BillBuy AS bb ON bb.bnumber = b.bnumber
LEFT JOIN	BillSell AS bs ON bs.bnumber = b.bnumber
WHERE		b.account = 'xyz'

Also you must learn from previous post!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75396

Peter Larsson
Helsingborg, Sweden

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-28 : 01:47:29

ok...now i have corrected the error.....but i am not getting any record inside the result

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 01:49:36

You must JOIN a table to another table! Not self-join...

SELECT		b.bnumber,
		b.bdate as date_,
		b.vendor as vendor_,
		bb.stock as buy_stock,
		bb.qty as buy_qty,
		bb.price as buy_price,
		bs.stock as sell_stock,
		bs.qty as sell_qty,
		bs.price as sell_price
FROM		Bills b
LEFT JOIN	BillBuy AS bb ON bb.bnumber = b.bnumber
LEFT JOIN	BillSell AS bs ON bs.bnumber = b.bnumber
WHERE		b.account = 'xyz'

Also you must learn from previous post!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75396

Peter Larsson
Helsingborg, Sweden

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-28 : 01:49:56

i am writing the query in the MS Access....and it gives error if i dont put in the parantheses

In fact i had to enter modify the query to add the parantheses in the query to run it properly...

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 01:53:48

MS Access does not need paranthesis. If you use the query guide, the ACCESS engine puts the paranthesis there for some unknown reason.

And a free tip of advice: If you have ACCESS queries, please post them here
http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3

Peter Larsson
Helsingborg, Sweden

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-28 : 02:01:58

i copy pasted the above query as it is.....
but then it gives the following error

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

vaibhavpingle
Starting Member

28 Posts

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

Syntax error (missing operator)in query expression 'bb.bnumber=b.bnumber
LEFT JOIN BillSell AS bs ON bs.bnumber = b.bnumbe'.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 02:24:58

Try this one

SELECT b.bnumber, b.bdate, b.vendor, bs.stock, bs.qty, bs.price, bb.stock, bb.qty, bb.price
FROM (Bills AS b LEFT JOIN billsell AS bs ON b.bnumber = bs.bnumber) LEFT JOIN billbuy AS bb ON b.bnumber = bb.bnumber WHERE b.account = "xyz";

Peter Larsson
Helsingborg, Sweden

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-28 : 06:31:15

I have tried the same ....but it is not returning any rows in the query

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-28 : 06:51:25

It works fine on my sample data. Can you post your table data here?

Harsh Athalye
India.
"Nothing is Impossible"

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-28 : 07:08:20

sure
but i dont know how to send tables on sqlteam......

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

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-28 : 07:10:18

Bills Table
account bnumber bdate vendor
vaibhavpingle 001 02-04-2006 a
vaibhavpingle 002 09-06-2006 b

BillBuy Table
id bnumber stock qty price seen
53 001 RELIND 50 Rs.550.00 yes
54 001 BOMDYE 20 Rs.700.00 yes
55 002 RELCAP 100 Rs.360.00 yes

BillSell Table
id bnumber stock qty price seen
45 002 RELIND 75 Rs.850.00 yes
46 002 BOMDYE 10 Rs.900.00 yes

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-28 : 07:16:42

On the data you gave also, it works absolutely fine.

May be this is the thing in the query you forgot to change:

WHERE b.account = "vaibhavpingle"

instead of

WHERE b.account = "xyz"

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 07:24:26

NO WAY!!

Peter Larsson
Helsingborg, Sweden

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-28 : 07:32:15

heheheheheh

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

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-28 : 07:34:05

but there are in all 5 records...i am getting only 4

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

vaibhavpingle
Starting Member

28 Posts

Posted - 2006-11-28 : 07:39:13

also there are two occurences of RELCAP stock in the result, one with RELIND, one with BOMDYE

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 07:58:58

NULLs can have unexpected behaviour. Use this

-- prepare test data
declare @bills table (account varchar(13), bnumber varchar(3), bdate varchar(10), vendor varchar(1))

insert	@bills
select	'vaibhavpingle', '001', '02-04-2006', 'a' union all
select	'vaibhavpingle', '002', '09-06-2006', 'b'

declare @billbuy table (id tinyint, bnumber varchar(3), stock varchar(6), qty tinyint, price smallmoney, seen varchar(3))

insert	@billbuy
select	53, '001', 'RELIND', 50, 550.00, 'yes' union all
select	54, '001', 'BOMDYE', 20, 700.00, 'yes' union all
select	55, '002', 'RELCAP', 100, 360.00, 'yes'

declare	@billsell table (id tinyint, bnumber varchar(3), stock varchar(6), qty tinyint, price smallmoney, seen varchar(3))

insert	@billsell
select	45, '002', 'RELIND', 75, 850.00, 'yes' union all
select	46, '002', 'BOMDYE', 10, 900.00, 'yes'

-- do the work
SELECT		b.bnumber,
		b.bdate as date_,
		b.vendor as vendor_,
		bb.stock as buy_stock,
		bb.qty as buy_qty,
		bb.price as buy_price,
		NULL as sell_stock,
		NULL as sell_qty,
		NULL as sell_price
FROM		@Bills b
INNER JOIN	@BillBuy AS bb ON bb.bnumber = b.bnumber
WHERE		b.account = 'vaibhavpingle'

UNION ALL

SELECT		b.bnumber,
		b.bdate,
		b.vendor,
		NULL,
		NULL,
		NULL,
		bs.stock,
		bs.qty,
		bs.price
FROM		@Bills b
INNER JOIN	@BillSell AS bs ON bs.bnumber = b.bnumber
WHERE		b.account = 'vaibhavpingle'

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources