Hello I have three tables that I have joined but it returns empty result even though there suppose to some result. Here is my sql
SELECT c.code,c.name, a.ltp as begning, b.ltp as enddate, d.interim_cash,d.interim_rec_date,
CAST(((b.ltp - a.ltp) / a.ltp * 100) AS DECIMAL(10, 2)) as chng
FROM eod_stock a
JOIN eod_stock b ON a.company_id = b.company_id
LEFT OUTER JOIN company AS c
ON c.ID = a.company_id
RIGHT JOIN divident_info AS d
ON c.ID = d.company_id
WHERE a.entry_date = "2012-09-24"
AND b.entry_date = "2012-09-25"
AND d.interim_rec_date BETWEEN "2012-09-24" AND "2012-09-25"
AND a.company_id IN (13, 2)
AND d.company_id IN (13,2);
The result i am expecting is like this:
+--------+-----------------+---------+--------+--------+------------------+------------+
| code | name | begning | end | chng | interim_rec_date |interim_cash|
+--------+-----------------+---------+--------+--------+------------------+------------+
| ABBANK | AB BANK LIMITED | 518.00 | 459.00 | -11.39 |2012-09-24 |10 |
| 1STICB | 1ST ICB M.F. | 227.00 | 253.00 | 11.45 | | |
+--------+-----------------+---------+--------+--------+------------------+------------+
But I am getting empty set in my result is this because the second one interim info is 0? how can I get all info like above if row is empty then it could be blank but i need other info related to it.
Here are my table schema:
eod_stock:
+-----------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| company_id | varchar(30) | NO | PRI | NULL | |
| entry_date | date | NO | PRI | NULL | |
| entry_timestamp | int(10) unsigned | NO | | NULL | |
| open | decimal(16,2) | NO | | NULL | |
| high | decimal(16,2) | NO | | NULL | |
| low | decimal(16,2) | NO | | NULL | |
| ltp | decimal(16,2) | NO | | NULL | |
| ycp | decimal(16,2) | NO | | NULL | |
| cse_price | decimal(9,2) | NO | | NULL | |
| cse_volume | decimal(18,2) | NO | | NULL | |
| total_trade | int(30) | NO | | NULL | |
| total_volume | int(30) | NO | | NULL | |
| total_value | decimal(18,4) | NO | | NULL | |
+-----------------+------------------+------+-----+---------+-------+
divident Info:
+------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+-------------------+-----------------------------+
| divident_ID | int(11) | NO | PRI | NULL | auto_increment |
| company_id | int(11) | NO | | NULL | |
| year | year(4) | NO | | NULL | |
| right_base | int(11) | NO | | NULL | |
| right_new | int(11) | NO | | NULL | |
| right_dec_date | date | NO | | NULL | |
| right_rec_date | date | NO | | NULL | |
| interim_cash | decimal(6,2) | NO | | NULL | |
| interim_stock | decimal(8,2) | NO | | NULL | |
| interim_dec_date | date | NO | | NULL | |
| interim_rec_date | date | NO | | NULL | |
| annual_cash | decimal(6,2) | NO | | NULL | |
| annual_stock | decimal(8,2) | NO | | NULL | |
| annual_dec_date | date | NO | | NULL | |
| annual_rec_date | date | NO | | NULL | |
| update_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------------+--------------+------+-----+-------------------+-----------------------------+
Please can you help me to find out the result?
So finally I got what i needed here is the query:
SELECT c.code,c.name, a.ltp as begning, b.ltp as enddate, d.interim_cash,d.interim_rec_date, d.annual_rec_date,d.annual_cash,
CAST(((b.ltp - a.ltp) / a.ltp * 100) AS DECIMAL(10, 2)) as chng
FROM eod_stock AS a
LEFT OUTER JOIN eod_stock AS b
ON a.company_id = b.company_id
LEFT OUTER JOIN company AS c
ON c.ID = a.company_id
LEFT OUTER JOIN dividend_info AS d
ON c.ID = d.company_id AND d.interim_rec_date BETWEEN "2012-09-24" AND "2012-09-25" AND d.annual_rec_date BETWEEN "2012-09-24" AND "2012-09-25"
WHERE a.entry_date = "2013-09-24"
AND b.entry_date = "2013-09-25"
AND a.company_id IN (13, 2,4,5);
Thanks Gordon Linoff for bring me on the track i was messing up with the right join