13 Replies - 866 Views - Last Post: 03 November 2013 - 02:11 AM Rate Topic: -----

#1 devarapalli  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 28-October 13

db for store management

Posted 28 October 2013 - 10:34 PM

I am struck getting last transaction of customers.
I want get in nested mysql query.So please anybody help me?
Is This A Good Question/Topic? 0
  • +

Replies To: db for store management

#2 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 54
  • View blog
  • Posts: 335
  • Joined: 14-November 12

Re: db for store management

Posted 28 October 2013 - 10:37 PM

what have you done so far?
could you please post some codes of what you have already tried?
Was This Post Helpful? 0
  • +
  • -

#3 devarapalli  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 28-October 13

Re: db for store management

Posted 28 October 2013 - 10:58 PM

This is db tables
customers table:
1.c_id(PK)
2.customer name
3.customer phone
4.customer place

transactions table :
1.t_id(PK)
2.c_id(FK)
3.Total
4.paid
5.balance
6.previous balance
7.total_balance
8.date

products table;
1.p_id(PK)
2.product name
3.product quantity type


detailed transaction table :
1.id(PK)
2.t_id(FK)
3.p_id(FK)
4.qnty
5.unit cost
6.total


Here i need to get reports for balance sheet of customers
for that i need to get last transaction id of customer.here i struck
how can i get last t_id of all customers in single query
i tried 

SELECT t.date,c.NAME,t.TOTAL_BALANCE from transactions t, customers c where c.c_id=t.c_id;
[code]
Was This Post Helpful? 0
  • +
  • -

#4 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 54
  • View blog
  • Posts: 335
  • Joined: 14-November 12

Re: db for store management

Posted 28 October 2013 - 11:46 PM

are you familiar with using Max() in your query?
this way you can get the last transaction just by getting the largest transaction_ID created for a single customer.

here is something you can try to read

Hope it helped.
Good luck
Was This Post Helpful? 0
  • +
  • -

#5 devarapalli  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 28-October 13

Re: db for store management

Posted 29 October 2013 - 12:10 AM

I tried that also like

SELECT t.date,c.NAME,t.TOTAL_BALANCE from transactions t, customers c where c.c_id=t.c_id AND t.t_id = (SELECT MAX(t_id) FROM transaction t);

it shows error 'Multiple rows are getting ' like this.

This post has been edited by andrewsw: 03 November 2013 - 03:52 AM

Was This Post Helpful? 0
  • +
  • -

#6 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 54
  • View blog
  • Posts: 335
  • Joined: 14-November 12

Re: db for store management

Posted 29 October 2013 - 12:24 AM

joining the tables in one query could do it.
you can try something like this
here is the link for a more detailed explaination regarding table joins
SELECT Max(t.t_id),t.date,c.NAME,t.TOTAL_BALANCE from transactions t Inner Join customers c on c.c_id=t.c_id


Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9574
  • View blog
  • Posts: 36,262
  • Joined: 12-June 08

Re: db for store management

Posted 29 October 2013 - 07:12 AM

Why are you not recording the date/time of the data being entered?
Was This Post Helpful? 0
  • +
  • -

#8 devarapalli  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 28-October 13

Re: db for store management

Posted 29 October 2013 - 10:50 PM

It's not working jimzcoder.
Was This Post Helpful? 0
  • +
  • -

#9 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 54
  • View blog
  • Posts: 335
  • Joined: 14-November 12

Re: db for store management

Posted 29 October 2013 - 10:54 PM

Please provide more details about the error.
it can help us understand more about the problem.
Was This Post Helpful? 0
  • +
  • -

#10 devarapalli  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 28-October 13

Re: db for store management

Posted 30 October 2013 - 12:10 AM

[/code]
SELECT MAX(Orders.OrderID), Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

And the error is:

You tried to execute a query that does not include the specified expression 'CustomerName' as part of an aggregate function.
Was This Post Helpful? 0
  • +
  • -

#11 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 54
  • View blog
  • Posts: 335
  • Joined: 14-November 12

Re: db for store management

Posted 30 October 2013 - 02:03 AM

try using an alias.

SELECT MAX(or.OrderID), cust.CustomerName, or.OrderDate
FROM Orders as or
INNER JOIN Customers as cust
ON or.CustomerID=cust.CustomerID;



you will also have to make sure that the tables' column names were spelled correctly.

This post has been edited by jimzcoder: 30 October 2013 - 02:06 AM

Was This Post Helpful? 0
  • +
  • -

#12 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5932
  • View blog
  • Posts: 12,855
  • Joined: 16-October 07

Re: db for store management

Posted 30 October 2013 - 03:35 AM

View Postjimzcoder, on 30 October 2013 - 05:03 AM, said:

try using an alias.


No. While aliases do make life easier, that have nothing to do with the problem here.

Try using a GROUP BY. If you want an "aggregate" function like MAX to work, you either only use those type of functions or declare the columns that form the group.

e.g.
SELECT MAX(Orders.OrderID), Customers.CustomerName, Orders.OrderDate
  FROM Orders
    INNER JOIN Customers
      ON Orders.CustomerID=Customers.CustomerID
  GROUP BY Customers.CustomerName, Orders.OrderDate;



There. You have the two non aggregate fields in the GROUP BY and it will work.
Was This Post Helpful? 1
  • +
  • -

#13 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 54
  • View blog
  • Posts: 335
  • Joined: 14-November 12

Re: db for store management

Posted 30 October 2013 - 04:09 AM

thnx baav. you're really an expert in this.
Was This Post Helpful? 0
  • +
  • -

#14 devarapalli  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 26
  • Joined: 28-October 13

Re: db for store management

Posted 03 November 2013 - 02:11 AM

View Postbaavgai, on 30 October 2013 - 04:05 PM, said:

Try using a GROUP BY. If you want an "aggregate" function like MAX to work, you either only use those type of functions or declare the columns that form the group.

e.g.
SELECT MAX(Orders.OrderID), Customers.CustomerName, Orders.OrderDate
  FROM Orders
    INNER JOIN Customers
      ON Orders.CustomerID=Customers.CustomerID
  GROUP BY Customers.CustomerName, Orders.OrderDate;


There. You have the two non aggregate fields in the GROUP BY and it will work.


I am able to get only Max Id. But not the entire record of MAX id.

This post has been edited by andrewsw: 03 November 2013 - 03:51 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1