select problem - 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)

select problem

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2003-07-03 : 16:36:48

My table structure/data:

AAA
lngIndex acct cycle amount tax
1 123 3/1/2003 12.34 1.12
2 123 3/1/2003 111.23 10.25
3 123 4/1/2003 86.88 7.78
4 222 3/1/2003 3.45 .28
5 222 5/2/2003 12.23 1.06
6 333 3/1/2003 123.23 11.12

BBB
lngIndex description quantity unit unitcost
1 pens 1 box 10.25
1 pencils 2 boxes 3.35
5 crayon 1 box 1.12
6 paint 2 gals 25.25

My recordset should be:
1 123 3/1/2003 12.34 1.12 pens 1 box 10.25
1 123 3/1/2003 12.34 1.12 pencils 2 boxes 3.35
2 123 3/1/2003 111.23 10.25
3 123 4/1/2003 86.88 7.78

What I want to do is to be able to select all records from table AAA with acct=123, and link AAA.lngIndex = BBB.lngIndex.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-03 : 16:39:57

You almost have the query in the question.

select *
from AAA , BBB
where AAA.lngIndex = BBB.lngIndex
and AAA.acct=123

select *
from AAA
join BBB
on AAA.lngIndex = BBB.lngIndex
where AAA.acct=123

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-03 : 16:40:30

select AAA.lngINdex, acct, cycle, amount, tax, description, unit, unitcost
from
AAA
left outer join
BBB
on
AAA.lngINdex = BBB.lngIndex
where
acct = 123

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-03 : 16:41:28

nigel -- based on his sample data and expected results, i think he needs an outer join...

- Jeff

PeterG
Posting Yak Master

156 Posts

Posted - 2003-07-03 : 16:59:30

Thanks for the quck reply Nigel and Jeff.

Here's how I reworded the query to apply to my tables:
SELECT T.txtAccountNum, T.dteCycleDate, T.dteTransacDate, T.txtMerchantName, T.curTransacAmnt, T.txtTransacCode,
P.ProductCode, P.description, P.Quantity, P.Unit, P.UnitCost
FROM Transactions T left outer join PurchaseDetails P on T.lngIndex=P.lngIndex WHERE (T.txtAccountNum = '0000000000040888')

This is giving me NULL values for the PurchaseDetails fields. Why? If I do an inner join, I don't get a record at all.

PeterG
Posting Yak Master

156 Posts

Posted - 2003-07-03 : 17:03:25

Never mind. I had the wrong parameters.

Thanks anyways.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources