问题描述:

I have two tables that I want to join together.

Table1

Year, ID, Theme,

Table2

First, Last, WeekID, Date, Affiliation

I want to use this command

SELECT *

FROM Table1

CROSS JOIN Table2

WHERE Table1.ID = 5

AND WHERE Table2.Date >= 1/1/2011

AND Table2.Date <= 12/30/2011

ORDER BY Asc

What I wanted to happen was all the rows and Columns from Table 1 are selected where the ID column contains an int value of 5. In Table2 all of the columns and rows should be selected that are within the given date range.

I would like to know if the WHERE clause should be coming after the CROSS JOIN clause as I have above. Also Should I remove the second WHERE keyword and instead have the following command.

SELECT *

FROM Table1

CROSS JOIN Table2

WHERE Table1.ID = 5

AND Table2.Date >= 1/1/2011

AND Table2.Date <= 12/30/2011

ORDER BY Asc

My third question is the tricky one. Can 2 different WHERE clauses be used in a single command like this but be applied to separate tables? Meaning can I have WHERE Table1 (*Condition*) AND WHERE Table2 (*Condition*) when I am joining the tables?

I think I could easily resolve the entire problem by creating 2 separate SQL commands 1 for each table and just avoid the JOIN and 2 WHERE clauses. Would this be something you would recommend?

The final result would look something like this

Table3

ID, Year, Theme, WeekID, Date, First, Last, Affiliation

Then the cells of would be order in ascending order based on date.

a sample table is below

Table3

ID Year Theme WeekID Date First Last Affiliation

5 2011 Stuff1 1 01/09/2011 Foo Bar Baz Inc

5 2011 Stuff2 2 01/14/2011 Flum Baz Bar Inc

5 2011 Stuff3 3 04/15/2011 Bar Flum Bub Inc

5 2011 Stuff4 4 05/01/2011 Bar Foo FlumBub Inc

5 2011 Stuff5 5 08/16/2011 Bub Baz Foo Inc

网友答案:

Q: I would like to know if the WHERE clause should be coming after the CROSS JOIN clause as I have above.

A: Yes, that's the correct placement of the WHERE clause.

Q: Also Should I remove the second WHERE keyword and instead have the following command.

A: Yes, the WHERE clause can appear only once in a simple SELECT statement. Each subquery can have its own WHERE clause, but that's really still one WHERE clause per SELECT.

Q: My third question is the tricky one. Can 2 different WHERE clauses be used in a single command like this but be applied to separate tables? Meaning can I have WHERE Table1 (Condition) AND WHERE Table2 (Condition) when I am joining the tables?

A:The WHERE keyword can appear only once per SELECT. You are free to include predicates on any table.


Also, to answer some additional questions you didn't ask...

You need to provide an expression, or list of expressions, in the ORDER BY clause. The default order is ASC, so this keyword is most frequently omitted.

The predicate on the Date column of Table2 appears to represent date literals. (As they are in your statement, they appear to represent an integer value, derived by a sequence of division operations.

The literals should be explicitly converted to DATETIME (to match the datatype of the Date column). An explicit CONVERT isn't required by SQL Server, but absent the conversion, you really want those to be represented as strings in a canonical (unambiguous) format. (Does '3/5/2012' represent March 5th, or May 3rd?)

SQL Server DATTIME datatype stores both date and time components. Typically, when users ask for an end date, they are meaning any time on that day as well. To take into account that a DATETIME value of '2011-12-30 09:30:00' is NOT <= '2011-12-30', we would normally code a test of LESS THAN midnight of the following day.

It's very good practice to qualify references to columns. This is frequently done with table aliases. Table aliases are not required, but they are a familiar pattern, and can make reading a statement much easier. That's especially true when the table names are fully qualified mydatabase.schema.MyLongAndUnWeILDyTblName, and fully qualified column names used in more complex expressions can make deciphering the expression very tedious. (Not really an issue in your case, but it's a pattern we follow even on simple statements.)

Also, best practice is to avoid using the * in the SELECT list (unless you are selecting from an inline view or CTE within the statement). Instead list the specific expressions you want returned. For testing and development, using * is fine. Aside from those minor issues, your statement looks fine.

(Avoiding the * and qualifying column names avoids PROBLEMS in the future which can occur, for example, when a new column is added to a table, giving rise to an "ambiguous column" exception which wasn't there before. (We like to be able to add columns without running a full regression test of every SQL statement in the application.)

Given all that information you didn't ask for... in our shop, the statement to return your specified resultset would be formatted like this:

SELECT t1.ID
     , t1.Year
     , t1.Theme
     , t2.WeekID
     , t2.Date
     , t2.First
     , t2.Last
     , t2.Affiliation
  FROM dbo.Table1 t1
 CROSS
  JOIN dbo.Table2 t2
 WHERE t1.ID = 5 
   AND t2.Date >= CONVERT(DATETIME,'2011-01-01',20)
   AND t2.Date <  CONVERT(DATETIME,'2011-12-31',20)
 ORDER
    BY t1.ID
     , t1.Year
     , t1.Theme
     , t2.WeekID
     , t2.Date
     , t2.First
     , t2.Last
     , t2.Affiliation

In a later comment, Derek noted that the Date column is VARCHAR. In that unfortunate case, we need to know the format the dates are represented in.

If the string representation are not in a canonical format, the VARCHAR comparison will yield undesirable results.

(Observe that the character string '3/5/2011' is NOT BETWEEN '1/1/2011' AND '12/30/2011'.)

There are significant advantages to using the DATETIME datatype to store date values. If that is not possible (for whatever insidious reason someone comes up with), and the strings are not in a canonical format, then the predicate should really be something more like:

AND CONVERT(DATETIME,t2.Date,101) >= CONVERT(DATETIME,'01/01/2011',101)
AND CONVERT(DATETIME,t2.Date,101) <  CONVERT(DATETIME,'12/31/2011',101)
网友答案:

I think you'd be best off using a CTE like this:

WITH Table1CTE (COL1, COL2)
AS
(
SELECT COL1, COL2
FROM Table1 
WHERE Table1.ID = 5
),
Table2CTE (COL3, COL4)
AS
(
SELECT COL3, COL4
FROM TABLE2
WHERE Table2.Date >= 1/1/2011 
    AND Table2.Date <= 12/30/2011
)
SELECT *
FROM Table1CTE CROSS JOIN Table2CTE
ORDER BY COL1 ASC
网友答案:

The first SELECT command

SELECT *  
FROM Table1  
CROSS JOIN Table2  
WHERE Table1.ID = 5  
    AND WHERE Table2.Date >= 1/1/2011  
    AND Table2.Date <= 12/30/2011  
ORDER BY Asc 

will produce an

incorrect syntax error near keyword WHERE

Only one WHERE statement needs to be used any other conditions can be specified by AND.

Also the dates specified above need to be formatted as such

Table2.Date >= '01/01/2011'  AND Table2.Date <= '12/30/2011'

Otherwise the SQL command will perform integer arithmetic and convert the type to int rather than matching a date string to the dates in the table which are of type varchar.

相关阅读:
Top