Select command help
i have two tables
t1
movieid showdate
1001 2011-05-23
1002 2011-05-23
1001 2011-05-23
1003 2011-05-23
1001 2011-05-22
1003 2011-05-22
1001 开发者_开发知识库 2011-05-22
t2
movieid moviename
1001 saanu
1002 ambika
1003 sarah
1004 hello
I want result as
moviename
saanu
when date is 2011-05-22
moviename
saanu
ambika
sarah
when showdate is between 2011-05-22 and 2011-05-23
You can JOIN
the two tables
By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how Microsoft SQL Server should use data from one table to select the rows in another table.
A join condition defines the way two tables are related in a query by:
Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table.
Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.
Statement
SELECT DISTINCT moviename
FROM t2
INNER JOIN t1 ON t1.movieid = t2.movieid
WHERE t1.showdate = '2011-05-22'
and
SELECT DISTINCT moviename
FROM t2
INNER JOIN t1 ON t1.movieid = t2.movieid
WHERE t1.showdate BETWEEN '2011-05-22' AND '2011-05-23'
would suffice
OK so you might want to look in to JOINS and the WHERE statement using AND/OR.
For example
SELECT * FROM t2
INNER JOIN t1 ON t2.movieid = t1.moveid
WHERE (showdate = '2011-May-22 and moviename = 'saanu') OR (showdate between '2011-May-22' AND '2011-May-23')
1.
select moviename from t2 join t1 on t1.movieid=t2.movieid and showdate='2011-05-22'
where moviename='saanu'
2.
select moviename from t2 join t1 on t1.movieid=t2.movieid and showdate between '2011-05-22' and '2011-05-23'
SELECT t1.movie_name
FROM t1, t2
WHERE t1.movieid = t2.movieid
AND t1.showdate = to_date('2011-05-22','YYYY-MM-DD')
OR t1.showdate BETWEEN to_date('2011-05-22','YYYY-MM-DD') AND to_date('2011-05-23','YYYY-MM-DD')
精彩评论