Extract data without Joining Tables

[sqltimmy]

This one may be a little hard to explain, but here goes -

I have a table APPS and table MONTH. MONTH is a subset of APPS that contains records with a date in the month of, say, January and in 2002 (the month of choice will vary). There's a part in the query I'm trying to build where I want to extract records from APPS that are from the month indicated by the date of records in MONTH, but from 2001.

How can I extract that Month from the MONTH table without using it in the FROM (having to join the tables)? I only want to select records from APPS.

Thanks again! You guys always answer my questions quickly and I appreciate the help!

 

[gcole]

You don't need to select records from both tables. Use the tables in the from and where statement, like this:

Select (list of APPS fields)
from APPS a, month m
where a.commonfield = m.commonfield

 

[sqltimmy]

That method creates too many record for though because I'm not looking for exact common fields. I need to select from APPS the records matching the month of the records in MONTH, but from the year before (2001). That method you suggests gives me essentially the same thing as MONTH because MONTH is a selection of APPS to begin with.

I just want to do:

SELECT *
FROM APPS
WHERE MONTH(APPS.DATE) = MONTH(MONTH.DATE) AND YEAR(APPS.DATE) = YEAR(MONTH.DATE) - 1

The problem is that I can't use the table MONTH unless I have it in the FROM clause.

 

[gcole]

Can you select just the fields you are interested in from the APPS table?

 

[tlbroadbent]

You have to add the MONTH table to the FROM clause. You don't need to select any columns from the MONTH table.

SELECT APPS.*
FROM APPS, [MONTH]
WHERE MONTH(APPS.[DATE]) = MONTH([MONTH].[DATE])
AND YEAR(APPS.[DATE]) = YEAR([MONTH].[DATE]) - 1

Alternate query:

SELECT APPS.*
FROM APPS
WHERE EXISTS
(SELECT * From [MONTH]
WHERE MONTH([DATE]) = MONTH(APPS.[DATE])
AND YEAR([DATE]) - 1 = YEAR(APPS.[DATE]))

I'd advise you not to use reserved words such as MONTH and DATE for object and column names. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.