6 Replies - 1036 Views - Last Post: 24 February 2012 - 09:41 AM Rate Topic: -----

#1 vikramsinh_shinde  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 22-February 12

SQL multiple join in same query

Posted 24 February 2012 - 02:56 AM

I have a query as follow,

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 ,
+-------------------+------+--------------+
|payment_txn_status |count | date_created |
+-------------------+------+--------------+
|  210              |   68 |     2011     |
|  201              |   34 |     2012     |
|  205              |   57 |     2011     |

(8 rows affected)



I have one more table named payment_txn_status_ref which contains description for each payment staus code as follow,
 
+---------------+-----------+
|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 ,
+-------------------+------+--------------+
|payment_txn_status |count | date_created |
+-------------------+------+--------------+
|  Created          |   68 |     2011     |
|  Progress         |   34 |     2012     |
|  Paid             |   57 |     2011     |


and so on....

Is This A Good Question/Topic? 0
  • +

Replies To: SQL multiple join in same query

#2 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 204
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: SQL multiple join in same query

Posted 24 February 2012 - 03:08 AM

Again you can do that using JOINS. I've shown you how to join tables in your previous question. Now show some effort by trying to do it yourself first.
Was This Post Helpful? 1
  • +
  • -

#3 vikramsinh_shinde  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 22-February 12

Re: SQL multiple join in same query

Posted 24 February 2012 - 03:21 AM

View PostnK0de, on 24 February 2012 - 03:08 AM, said:

Again you can do that using JOINS. I've shown you how to join tables in your previous question. Now show some effort by trying to do it yourself first.



i have tried this ---

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)



but it's giving me 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.

Am i missing something?
Was This Post Helpful? 0
  • +
  • -

#4 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 204
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: SQL multiple join in same query

Posted 24 February 2012 - 03:41 AM

change the GROUP BY clause to this and see

GROUP BY payment_txn_status, date_created

Was This Post Helpful? 1
  • +
  • -

#5 vikramsinh_shinde  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 22-February 12

Re: SQL multiple join in same query

Posted 24 February 2012 - 03:46 AM

View PostnK0de, on 24 February 2012 - 03:41 AM, said:

change the GROUP BY clause to this and see

GROUP BY payment_txn_status, date_created


still it's giving same 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.
Was This Post Helpful? 0
  • +
  • -

#6 nK0de  Icon User is offline

  • Catch me As Exception
  • member icon

Reputation: 204
  • View blog
  • Posts: 823
  • Joined: 21-December 11

Re: SQL multiple join in same query

Posted 24 February 2012 - 03:58 AM

check your second JOIN statement.

JOIN payment_txn_status_ref ps ON payment_txn_status = payment_status


And give this article a read. It'll help you out.

This post has been edited by nK0de: 24 February 2012 - 03:59 AM

Was This Post Helpful? 1
  • +
  • -

#7 vikramsinh_shinde  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 22-February 12

Re: SQL multiple join in same query

Posted 24 February 2012 - 09:41 AM

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)


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1