SQL Query for a particular problem??

Question

Lets frame the question again---

  table1{date, bID, sName, fID}
    {11/05,B1,A1,P1}
    {12/05,B2,A2,P2}
    {13/05,B1,A3,P1}
    {15/05,B3,A4,P1}
    {16/05,B1,A5,P2}
    {19/05,B1,A6,P2}

This is the table and the data stored in the table is also specified...

Now the query that i want is that:-

Depending of fId (lets say, P1 is selected) it should display the data from table say from 11/05-17/05 (no date should be missed). The data retrieved is as follows:-

11/05,B1,A1
12/05,--,--
13/05,B1,A3
14/05,--,--
15/05,B3,A4
16/05,--,--
17/05,--,--

The data retrieved for a particular fID(say P1) is displayed.. Explaning the result...

1) it displayed all data from 11/05-17/05 where fId is P1, if there is no date in the database, then also it should display null value for that date (i.e.14/05 date was not there in database, but still it displayed with a null value)..

2) if fId for that particular date is not P1, then also it store a null value in result set..

Atlast the data is retrieved in result set,, and processed further..

So i want to write the query for this problemm,, is it possible..

Plz guide frenz...

Thanx..

Answer 1

No code here, just my thoughts.

You need to create a temporary table with dates ranging from your begin date to an end date, inclusive. And then left join table1 with that temporary table on date column plus add where fID = ?.

Answer 2

As the other answer here mentions, a table with all the dates in it, and a LEFT JOIN is what you need.

Say you have this table:

CREATE TABLE table1
{
    date    DATETIME
    bID     VARCHAR(10),
    sName   VARCHAR(10),
    fID     VARCHAR(10)
}

and then this date-table:

CREATE TABLE dates
(
    dt      DATETIME
)

and in this table you need to have all the dates for the range you want to display. Usually you populate it with a couple of years in both directions, but that's up to you.

Note: For simplicity, I did not bother with primary keys in either table. You should of course make sure you have a primary key, and in the case of the dates table, it could be the dt column.

Then to display the results you want:

SELECT
    dt,
    bID,
    sName
FROM
    dates
    LEFT JOIN table1 ON dt = date AND fld = 'P1'
ORDER BY
    dt

Note that the selection of only P1 rows is done in the JOIN criteria. If you add a WHERE clause to do the same, you'll loose all dates that have no data.