CODEWITHSUNDEEP
phpmysqldouble
Muhammad Arslan Jamshaid
Muhammad Arslan Jamshaid

Reputation: 1197

MySQL query printing double records

All recorders in sales & purchase table are only entered Once. I have checked it carefully. However I wish to combine these two tables in such a way that both tables will be completely fetched. here is my query note that timestamp column has mysql DATE format

Select 
    sales.ID as sid,
    sales.saleHatsh,
    sales.timestamp as sdate,
    sales.gatePass as sGP,
    sales.pname as sPN,
    sales.description as sDES,
    sales.balance as sbal, 
    purchase.ID as pid,
    purchase.purchaseHatsh,
    purchase.timestamp as pdate,
    purchase.gatePass as pGP,
    purchase.pname as pPN,
    purchase.description as pDES,
    purchase.balance as pbal 
from sales,purchase 
where sales.timestamp='2013-11-11' OR purchase.timestamp='2013-11-11'

here is the result of my query & sales & purchase table Query Result Sales table only have 2 recorder enter image description here

Purchase table only has 4 recorder enter image description here

Upvotes: 1

Views: 341

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

What is happening there is that you are not joining those tables in any way. so you are getting all the possible matches from those tables. Looking at the columns i don't think you want to JOIN, but probably you want a UNION instead:

SELECT 
  sales.ID AS id,
  sales.saleHatsh AS hatsch,
  sales.TIMESTAMP AS date,
  sales.gatePass AS GatePass,
  sales.pname AS pname,
  sales.description AS Description,
  sales.balance AS balance,
  'SALE' AS transanctionType
FROM sales
WHERE sales.TIMESTAMP = '2013-11-11'
UNION 
SELECT 
  purchase.ID,
  purchase.purchaseHatsh,
  purchase.TIMESTAMP,
  purchase.gatePass,
  purchase.pname,
  purchase.description,
  purchase.balance,
  'PURCHASE'
FROM purchase
WHERE purchase.TIMESTAMP = '2013-11-11'

I added a column "transactionType" for you to identify which ones are sales or purchases.

Upvotes: 2

Related Questions