SQL question - thanks

[rayxu]

When I use the following query

select t.trantype
FROM rpttrantype t

where t.trantype = 33 or t.trantype =35

I got the list

trantype
--------
33
35

(2 row(s) affected)

this is correct because it is only two records (33 and 35) in the db.


but when I tried to join two tables using the following

select t.trantype
FROM rpttrantype t
JOIN rptstatecode s
on t.rttkey = s.rttkey

where t.trantype = 33 or t.trantype =35

the result is different

trantype
--------
33
35
35
35
35
35
35
35

(8 row(s) affected)

I don't know why??? I only expect two records (33 and 35) listed

Thanks

[Johnny101]

This is because the rpstatecode table has multiple entries in it that match one entry in the rptrantype table. to limit the number of returned rows from that query - add the DISTINCT flag to the select:

SELECT DISTINCT t.trantype
FROM rpttrantype t
JOIN rptstatecode s
on t.rttkey = s.rttkey
WHERE t.trantype = 33 or t.trantype = 35




hope this helps,

john

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[rayxu]

thanks a lot.

But why I got more than two record (33 and 35) when I ran the following query? and how can I get only record (33 and 35)?



select distinct t.trantype, s.SummValue, s.ItemAmount, d.DepositNumber, d.Amount, sf.paymenttotamt, sf.sendfilename
FROM rpttrantype t
inner JOIN rptstatecode s
on t.rttkey = s.rttkey
join rptbatchtype b
on t.rbtkey = b.rbtkey,
micrinfo001115 m
join pocketcut p
on m.cutno = p.cutno
join deposits d
on p.depositkey = d.depositkey,
sendfilebatches sb
join sendfile sf
on sb.sendfilekey = sf.sendfilekey
where t.trantype = 33 or t.trantype =35

[Johnny101]

You have to joing each of the tables together - on some way - to limit the number of rows.

a -> b
b -> c
c -> d
a -> e
c -> f

and so on. if you have

a -> b
b -> c
d -> e

that wont work - because the d to e relationship isn't limited by the a-b-c relationship.

hope that makes sense.

John

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org

[rayxu]

thanks again.

it makes sense to me.

but when I tried the following, it still didn't work.

select distinct t.trantype, s.SummValue, s.ItemAmount
FROM rpttrantype t
inner JOIN rptstatecode s
on t.rttkey = s.rttkey
where t.trantype = 33 or t.trantype =35

the result was

trantype SummValue ItemAmount
-------- -------------------- ---------------------
33 352.1100
35 101 205.0200
35 202 199.7500
35 303 188.6800
35 505 58.1400
35 606 156.3200
35 707 108.9800
35 808 79.2500

(8 row(s) affected)

It still not only two records (33 and 35). Please help me out. thanks!

[Jie Ma]

You have more than two records which have 33, 35 trantype in table rptstatecode replated trantype in rpttrantype. Try this

select distinct t.trantype FROM rpttrantype t
inner JOIN rptstatecode s
on t.rttkey = s.rttkey
where t.trantype = 33 or t.trantype =35