SQL 2005 using multiple join in same query

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

I have a query as follow,

SQL

SELECT payment_txn_status, COUNT(*) AS "count", YEAR(tb.date_created) AS date_created 
FROM tpayment_txn tt 
JOIN tbooking tb 
  ON tb.booking_ref_id = tt.booking_ref_id 
GROUP BY tt.payment_txn_status, YEAR(tb.date_created)

which gives me result like ,

SQL

+-------------------+------+--------------+
|payment_txn_status |count | date_created |
+-------------------+------+--------------+
|  210              |   68 |     2011     |
|  201              |   34 |     2012     |
|  205              |   57 |     2011     |

(8 rows affected)

I have one more table named

SQL

payment_txn_status_ref

which contains description for each payment staus code as follow,

SQL

+---------------+-----------+
|payment_status |description|
+---------------+-----------+
|201            | Created   |
|202            | Progress  |
|202            | Collected |
|205            | Paid      |

So I want to modify above query so that it will replace payment_txn_status code with their respective description from payment_txn_status_ref table

The result should be look like ,

SQL

+-------------------+------+--------------+
|payment_txn_status |count | date_created |
+-------------------+------+--------------+
|  Created          |   68 |     2011     |
|  Progress         |   34 |     2012     |
|  Paid             |   57 |     2011     |

and so on....

Posted 23-Feb-12 22:52pm

vikram_shinde

Add a Solution

Comments

CGN007 24-Feb-12 6:12am

Please update the question with the three table structure...!!!

2 solutions

Solution 2

It get solved... many thanks

SELECT ps.payment_status_desc AS payment_txn_status, COUNT(*) AS "count", YEAR(tb.date_created) AS date_created
FROM tpayment_txn tt
JOIN tbooking tb
ON tb.booking_ref_id = tt.booking_ref_id
JOIN tpayment_txn_status_ref ps
ON tt.payment_txn_status = ps.payment_status
GROUP BY ps.payment_status_desc, YEAR(tb.date_created)

Posted 24-Feb-12 5:40am

vikram_shinde

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Accepted Answer · 2012-02-23T23:07:00

Solution 1

Try something like this:

SQL

SELECT ps.description AS payment_txn_status, COUNT(*) AS "count", YEAR(tb.date_created) AS date_created
FROM tpayment_txn tt
JOIN tbooking tb
  ON tb.booking_ref_id = tt.booking_ref_id
JOIN payment_txn_status_ref ps 
  ON payment_txn_status = payment_status
GROUP BY tt.payment_txn_status, YEAR(tb.date_created)

Good luck!

Posted 23-Feb-12 23:07pm

E.F. Nijboer

Comments

vikram_shinde 24-Feb-12 5:19am

it's giving error ---
Column 'payment_txn_status_ref.description ' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

E.F. Nijboer 24-Feb-12 9:17am

What if you simply try "select description ..." without the ps. prefix? also remove the " AS payment_txn_status" here. It should simply take the description from the joined table.