CODEWITHSUNDEEP
mysqljoin
itcplpl
itcplpl

Reputation: 780

using SQL JOIN for union of data

I am trying to run a query using JOIN. so, I wrote the below:

SELECT * FROM (SELECT trade_date, trade_time, price, contract_name 
               FROM test 
               WHERE contract_name="Z10" 
               AND trade_date="2010-12-01" 
               AND trade_time="0900") 
AS A JOIN (SELECT trade_date, trade_time, price, contract_name 
           FROM test 
           WHERE contract_name="H11" 
           AND trade_date="2010-12-01" 
           AND trade_time="0900") 
AS B ON (A.trade_date=B.trade_date 
AND A.trade_time=B.trade_time);

it runs fine, except it returns a large number of rows:

+------------+------------+-------------+---------------+------------+------------+-------------+---------------+
172025 rows in set (0.31 sec)

so I ran the below 2 queries individually to check how many max rows I should get, and from the below it should not exceed 1000.

mysql> select count(*) 
       from test 
       where contract_name="Z10" 
       and trade_date="2010-12-01" 
       and trade_time="0900";
+----------+
| count(*) |
+----------+
| 983 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) 
       from test 
       where contract_name="H11" 
       and trade_date="2010-12-01" 
       and trade_time="0900";
+----------+
| count(*) |
+----------+
| 175 |
+----------+

Can anyone suggest how I edit my JOIN to get the union of the 2 contracts on the selected date and time.

Upvotes: 0

Views: 39

Answers (2)

WordsWorth
WordsWorth

Reputation: 872

You in fact don't need to do the join if you are getting result from same table. You can do this:

select count(*)
from test
where contract_name in ("H11","z10")
  and trade_date="2010-12-01"
  and trade_time="0900";

Upvotes: 2

gview
gview

Reputation: 15361

Yes that is what happens when you join two tables together. Because the date and time (your join criteria) match for every single row you get the product of the rows -- 983 x 175 = 172025. It does not matter that the two tables being joined are actually subsets of the same table.

Upvotes: 0

Related Questions