6 Replies - 596 Views - Last Post: 03 December 2013 - 10:27 AM Rate Topic: -----

#1 devarapalli  Icon User is offline

  • New D.I.C Head

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

Mysql subquery for getting stock-sold

Posted 03 December 2013 - 03:39 AM

I need to get the stock sold based on date and productName and product category.
here is my tables to which i have to join to get result.

create table products (
P_ID INT AUTO_INCREMENT,
PRODUCT_NAME varchar(40) NOT NULL,
PRODUCT_TYPE varchar(40),
Deleted BIT DEFAULT 1,
PRIMARY KEY(P_ID)
); 

Create Table suppliers (
supid INT AUTO_INCREMENT,
SupplierName varchar(50),
SupplierMobile varchar(20),
SupplierPhone varchar(20),
SupplierPlace varchar(20),
SupplierAddress varchar(20),
sDate varchar(20),
Deleted BIT DEFAULT 1,
PRIMARY KEY(supid)
);

create table customers(
C_ID INT AUTO_INCREMENT,
Name varchar(50),
Mobile varchar(12),
ContactNo varchar(12),
Place varchar(50),
Address varchar(50),
Date varchar(20),
Deleted BIT DEFAULT 1,
PRIMARY KEY(C_ID,Name)
);






create table transactions(
T_ID INT AUTO_INCREMENT,
C_ID INT,
TOTAL varchar(20),
PAID varchar(20),
BALANCE varchar(20),
PRE_BALANCE varchar(20),
TOTAL_BALANCE varchar(20),
DATE varchar(10),
PRIMARY KEY(T_ID),
FOREIGN KEY (C_ID) REFERENCES customers(C_ID)
);





create table ttl_transaction(
detailed_transaction_id INT AUTO_INCREMENT,
T_ID INT,
P_ID INT,
QUANTITY INT,
UNITCOST INT,
TOTAL INT,
PRIMARY KEY(detailed_transaction_id),
FOREIGN KEY (T_ID) REFERENCES transactions(T_ID),
FOREIGN KEY (P_ID) REFERENCES products(P_ID)
);





create table stock_details(
S_ID INT AUTO_INCREMENT,
P_ID INT,
supid INT,
QUANTITY INT,
DATE varchar(20),
PRIMARY KEY(S_ID),
FOREIGN KEY (P_ID) REFERENCES products(P_ID) ,
FOREIGN KEY (supid) REFERENCES suppliers(supid) ,
updated TIMESTAMP default CURRENT_TIMESTAMP not null
);



here is my query but getting result by three time.

select DIstinct c.`Name`,p.`PRODUCT_NAME`,p.`PRODUCT_TYPE`,tt.`QUANTITY` ,tt.`UNITCOST` from 
customers c INNER JOIN transactions t on t.`C_ID`=c.`C_ID` 
INNER JOIN ttl_transaction tt on t.`T_ID`=tt.`T_ID`
INNER JOIN products p ON tt.`P_ID`=p.`P_ID`
INNER JOIN stock_details st ON st.`P_ID`=p.`P_ID` and tt.`P_ID`=p.`P_ID` 
INNER JOIN suppliers sp On sp.supid=st.supid WHERE t.`C_ID`=c.`C_ID` and 
tt.`T_ID`=t.`T_ID` and tt.`P_ID`=p.`P_ID` and t.`DATE` = '2013-12-03' 
and sp.`SupplierName` like  'John' and p.`PRODUCT_NAME` like 'apple' and p.`PRODUCT_TYPE` like 'box'



Is This A Good Question/Topic? 0
  • +

Replies To: Mysql subquery for getting stock-sold

#2 x68zeppelin80x  Icon User is offline

  • D.I.C Addict

Reputation: 130
  • View blog
  • Posts: 576
  • Joined: 07-March 09

Re: Mysql subquery for getting stock-sold

Posted 03 December 2013 - 04:00 AM

Just formatting your query for others...

Posted Image

SELECT DISTINCT c.`Name`, p.`PRODUCT_NAME`, p.`PRODUCT_TYPE`,
	tt.`QUANTITY`, tt.`UNITCOST`
FROM customers c
	INNER JOIN transactions t ON t.`C_ID` = c.`C_ID` 
	INNER JOIN ttl_transactiON tt ON t.`T_ID` = tt.`T_ID`
	INNER JOIN products p ON tt.`P_ID` = p.`P_ID`
	INNER JOIN stock_details st ON st.`P_ID` = p.`P_ID`
		AND tt.`P_ID` = p.`P_ID` 
	INNER JOIN suppliers sp ON sp.supid = st.supid
WHERE t.`C_ID` = c.`C_ID`
	AND tt.`T_ID` = t.`T_ID`
	AND tt.`P_ID` = p.`P_ID`
	AND t.`DATE` = '2013-12-03' 
	AND sp.`SupplierName` LIKE 'John'
	AND p.`PRODUCT_NAME` LIKE 'apple'
	AND p.`PRODUCT_TYPE` LIKE 'box';

This post has been edited by x68zeppelin80x: 03 December 2013 - 08:24 AM

Was This Post Helpful? 0
  • +
  • -

#3 g00se  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2720
  • View blog
  • Posts: 11,433
  • Joined: 20-September 08

Re: Mysql subquery for getting stock-sold

Posted 03 December 2013 - 05:17 AM

You must have used every possible naming convention for your column names - all at once

And

Quote

Deleted BIT DEFAULT 1,

Why? Are you mostly going to fill that table with deleted products?

And

Quote

DATE varchar(20),

Not a good idea to call your column 'DATE'. Not only is it meaningless but it's also a reserved word. Also using a char type for dates not only increases storage space in the tables but means you can't use them properly as dates. You're also opening up a possibility of format nightmares.

This is not a Java question btw

This post has been edited by g00se: 03 December 2013 - 05:30 AM
Reason for edit:: Clarification

Was This Post Helpful? 0
  • +
  • -

#4 devarapalli  Icon User is offline

  • New D.I.C Head

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

Re: Mysql subquery for getting stock-sold

Posted 03 December 2013 - 09:59 AM

View Postx68zeppelin80x, on 03 December 2013 - 04:30 PM, said:

Just formatting your query for others...

Posted Image

SELECT DISTINCT c.`Name`, p.`PRODUCT_NAME`, p.`PRODUCT_TYPE`,
	tt.`QUANTITY`, tt.`UNITCOST`
FROM customers c
	INNER JOIN transactions t ON t.`C_ID` = c.`C_ID` 
	INNER JOIN ttl_transactiON tt ON t.`T_ID` = tt.`T_ID`
	INNER JOIN products p ON tt.`P_ID` = p.`P_ID`
	INNER JOIN stock_details st ON st.`P_ID` = p.`P_ID`
		AND tt.`P_ID` = p.`P_ID` 
	INNER JOIN suppliers sp ON sp.supid = st.supid
WHERE t.`C_ID` = c.`C_ID`
	AND tt.`T_ID` = t.`T_ID`
	AND tt.`P_ID` = p.`P_ID`
	AND t.`DATE` = '2013-12-03' 
	AND sp.`SupplierName` LIKE 'John'
	AND p.`PRODUCT_NAME` LIKE 'apple'
	AND p.`PRODUCT_TYPE` LIKE 'box';




This is not working will the customer buy the same product twice a day
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: Mysql subquery for getting stock-sold

Posted 03 December 2013 - 10:07 AM

View Postg00se, on 03 December 2013 - 05:47 PM, said:

You must have used every possible naming convention for your column names - all at once

And

Quote

Deleted BIT DEFAULT 1,

Why? Are you mostly going to fill that table with deleted products?

And

Quote

DATE varchar(20),

Not a good idea to call your column 'DATE'. Not only is it meaningless but it's also a reserved word. Also using a char type for dates not only increases storage space in the tables but means you can't use them properly as dates. You're also opening up a possibility of format nightmares.

This is not a Java question btw




Please give me good idea regarding query.
Was This Post Helpful? 0
  • +
  • -

#6 devarapalli  Icon User is offline

  • New D.I.C Head

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

Re: Mysql subquery for getting stock-sold

Posted 03 December 2013 - 10:24 AM

View Postg00se, on 03 December 2013 - 05:47 PM, said:

You must have used every possible naming convention for your column names - all at once

And

Quote

Deleted BIT DEFAULT 1,

Why? Are you mostly going to fill that table with deleted products?

And

Quote

DATE varchar(20),

Not a good idea to call your column 'DATE'. Not only is it meaningless but it's also a reserved word. Also using a char type for dates not only increases storage space in the tables but means you can't use them properly as dates. You're also opening up a possibility of format nightmares.

This is not a Java question btw




Please give me good idea regarding query.
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9198
  • View blog
  • Posts: 34,552
  • Joined: 12-June 08

Re: Mysql subquery for getting stock-sold

Posted 03 December 2013 - 10:27 AM

Please stop duplicating your posts..
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1