Suggestions intermixed with your questions below. However, because you
only described your table and neglected to post the actual CREATE TABLE
statement, I will need to make a few assumptions (the actual name of your
table and the fact that missing data is stored as nulls are two I can
think of right away).
"N. Kavithashree" <[EMAIL PROTECTED]> wrote on 12/02/2004
01:33:01 AM:
>
> hello,
>
> Example : i hv a table containg date, flower, perfume,codeno,regno etc
>
> There are some row which have only flower entries for a day. there are
> some rows which have only perfume entries for a day.
> some rows have both florwer and perfume entries for a day..ie same day
> contains both entries.
>
> same flower names may repeat but on diff dates or on diff regno.
> similarly for perfume.
>
> but table has no duplicate entries.
>
> Query : what i want is
>
> 1) To retrieve those records for which there is both entries for flower
> and perfume on same date
This is a simple WHERE condition check
SELECT *
FROM flowerdata
WHERE flower is not null
and perfume is not null
>
> 2) To retriev records for perfume entries which have flower entries for
> the same date , same regno and same codeno.
You can accomplish this with a "self-join"
SELECT p.*
FROM flowerdata p
INNER JOIN flowerdata f
ON f.flower is not null
AND f.regno = p.regno
AND f.date = p.date
AND f.codeno = p.codeno
WHERE p.perfume IS NOT null
>
> 3) To retrieve records which doesnt hv a flower entry for the same date
as
> that of perfume
This is also a self-join but this time we use a LEFT JOIN and not an INNER
JOIN
SELECT p.*
FROM flowerdata p
LEFT JOIN flowerdata f
ON f.flower is not null
AND f.date = p.date
WHERE p.perfume IS NOT null
AND f.date IS null
AND p.flower IS null
The reason this query works is that we first try to match records that
meet your test condition (same date with flower entry) then look for the
exceptions (the non-matching records) from the table on the LEFT side of
the join. The ON clause handles the test condition while the WHERE clause
identifies only those that don't match your test (the first WHERE
condition makes sure that these are perfume records while the first AND-ed
condition in the WHERE clause will eliminate any matches). Since you can
have both flower and perfume data on the same date in some records I also
had to also exclude those dual-status records from this result (the last
AND-ed condition in the WHERE clause) as they would have violated your
query requirements.
>
> ====================================================
> N. Kavithashree
> ===================================================
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
