开发者

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')
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜