5 Replies - 2518 Views - Last Post: 10 April 2012 - 06:22 AM

#1 zeroofmhx   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 28
  • Joined: 02-October 10

getting the balance from two tables sql query

Posted 10 April 2012 - 03:13 AM

I have two tables namely tbl_transaction and tbl_payment
tbl_transaction contains(transactid,idnum,paymentid,paymentname,price,syear,semester) and tbl_payment(paymentid,paymentname,price,syear,semester)


my current sql statement is this and im using vb.net as front end
 
Dim fill as New ArrayList
sql = "select a.paymentid, b.paymentid from tbl_transaction a, tbl_payment b where a.idnum='" & TextBox1.Text & "' and b.syear='" & ComboBox1.Text & "' and b.semester ='" & ComboBox2.Text & "' and a.paymentid <> b.paymentid group by a.paymentid, b.paymentid"
            cmd = New SqlCommand(sql, conn)
            dr = cmd.ExecuteReader
            While dr.Read
                MsgBox(dr(1))
                fill.Add(dr(1))
            End While
            dr.Close()
            cmd.Dispose()

            For i = 0 To fill.Count - 1
                sql = "select * from tbl_payment where paymentid <>'" & fill(i) & "' and paymentid<>'" & fill(i + 1) & "' and syear='" & ComboBox1.Text & "' and semester='" & ComboBox2.Text & "'"
                cmd = New SqlCommand(sql, conn)
                dr = cmd.ExecuteReader
                While dr.Read
                    DataGridView1.Rows.Add(dr(1), dr(2))
                End While
                dr.Close()
                cmd.Dispose()
            Next


my problem is i can get the other payment that isn't paid WHEN only one PAYMENTID added on tbl_transaction however when two or more paymentid's are added the results are jumbled and even all payments are paid what changes should be made on my sql queries and yeah syear meas school year eg. (2011 - 2012) and semseter only have (1st and 2nd)

Is This A Good Question/Topic? 0
  • +

Replies To: getting the balance from two tables sql query

#2 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7506
  • View blog
  • Posts: 15,556
  • Joined: 16-October 07

Re: getting the balance from two tables sql query

Posted 10 April 2012 - 04:37 AM

I'm not really getting your first query:
select a.paymentid, b.paymentid 
	from tbl_transaction a
		inner join tbl_payment b 
			on ????
	where a.idnum = @idnum
		and b.syear = @syear
		and b.semester = @semester
		and a.paymentid != b.paymentid
	group by a.paymentid, b.paymentid



Your second one at least parses:
select * 
	from tbl_payment 
	where paymentid not in (@paymentid1, @paymentid2)
		and syear = @syear
		and semester = @semester



I'm going to just throw the logic back at you. tbl_payment is a single payment owed and tbl_transaction are checks applied to that payment? It's unclear to me why you have so many fields, to start out with.
tbl_payment
	paymentid(PK)
	paymentname
	price
	syear
	semester

tbl_transaction
	transactid (PK),
	idnum(UNIQUE?), -- check num?
	paymentid (FK)
	-- the rest defined in parent?
	-- paymentname,
	-- syear,
	-- semester
	price -- except this.  this might be the transaction amount?



If my assumptions are correct, which I really don't know...

If you want the total of all tbl_transaction for a given paymentid, you can do this:
select paymentid, sum(price) as TotalAmt
	from tbl_transaction
	group by paymentid



Given this, you can find how tbl_transaction relates to all of tbl_payment via:
select a.paymentid, a.paymentname, a.syear, a.semester, 
		a.price as PaymentPrice,
		IsNull(b.TotalAmt, 0) as TotalAmt,
		IsNull(b.TransCount, 0) as TransCount
	from tbl_payment a
		left outer join (
			select paymentid, sum(price) as TotalAmt, count(*) as TransCount
				from tbl_transaction
				group by paymentid
			) b
				on b.paymentid = a.paymentid



Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#3 zeroofmhx   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 28
  • Joined: 02-October 10

Re: getting the balance from two tables sql query

Posted 10 April 2012 - 04:49 AM

i think is there something wrong in my query ill make it sentence for you xD sorry english is bad

the first query will fetch all the paymentid's from tbl_payment that weren't added on the tbl_transaction via by year and semester and the second query is only adding the fetched paymentid's from the first query and the idnum is part of tbl_members unique id for each members

This post has been edited by zeroofmhx: 10 April 2012 - 04:51 AM

Was This Post Helpful? 0
  • +
  • -

#4 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7506
  • View blog
  • Posts: 15,556
  • Joined: 16-October 07

Re: getting the balance from two tables sql query

Posted 10 April 2012 - 05:46 AM

View Postzeroofmhx, on 10 April 2012 - 06:49 AM, said:

the first query will fetch all the paymentid's from tbl_payment that weren't added on the tbl_transaction via by year and semester


Excellent! That would be this:
select a.paymentid
	from tbl_payment a
		left outer join tbl_transaction b 
			on a.paymentid = b.paymentid
	where a.syear = @syear
		and a.semester = @semester
		and b.paymentid is null



Note the join type. This will return all records from tbl_payment even if they don't exist at all in tbl_transaction. So, by looking for nulls, you find those that don't exist in the other table.


View Postzeroofmhx, on 10 April 2012 - 06:49 AM, said:

the second query is only adding the fetched paymentid's from the first query and the idnum is part of tbl_members unique id for each members


You'd want
select * 
	from tbl_payment 
	where paymentid = @paymentid



The reasoning here is that the set of paymentids you've already retrieved already conform to your syear and semester filter. Since idnum belongs to tbl_transaction and we've already determined that there is no tbl_transaction record for the paymentid, I'm not sure where it comes in.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#5 zeroofmhx   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 28
  • Joined: 02-October 10

Re: getting the balance from two tables sql query

Posted 10 April 2012 - 06:05 AM

View Postbaavgai, on 10 April 2012 - 05:46 AM, said:

View Postzeroofmhx, on 10 April 2012 - 06:49 AM, said:

the first query will fetch all the paymentid's from tbl_payment that weren't added on the tbl_transaction via by year and semester


Excellent! That would be this:
select a.paymentid
	from tbl_payment a
		left outer join tbl_transaction b 
			on a.paymentid = b.paymentid
	where a.syear = @syear
		and a.semester = @semester
		and b.paymentid is null





Note the join type. This will return all records from tbl_payment even if they don't exist at all in tbl_transaction. So, by looking for nulls, you find those that don't exist in the other table.


View Postzeroofmhx, on 10 April 2012 - 06:49 AM, said:

the second query is only adding the fetched paymentid's from the first query and the idnum is part of tbl_members unique id for each members


You'd want
select * 
	from tbl_payment 
	where paymentid = @paymentid



The reasoning here is that the set of paymentids you've already retrieved already conform to your syear and semester filter. Since idnum belongs to tbl_transaction and we've already determined that there is no tbl_transaction record for the paymentid, I'm not sure where it comes in.

Hope this helps.


great it works! thanks! but i forgot where to put the idnum to be part of the first query also since idnum is unique id number for a member
Was This Post Helpful? 0
  • +
  • -

#6 zeroofmhx   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 28
  • Joined: 02-October 10

Re: getting the balance from two tables sql query

Posted 10 April 2012 - 06:22 AM

thank you for the help baavgai i get it already!
thanks a lot! :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1