Newbie needs help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Newbie needs help

Author

Topic

Rens1976
Starting Member

2 Posts

Posted - 2010-01-24 : 23:32:41

Hi I'm new to SQL.
I want to generate a query.
I have 5 tables.
Table: People
Table: Eaten
Table: Breakfast
Table: Lunch
Table: Dinner
People has the following fields:
ID, Firstname, Last name
Eaten has the following fields:
Eaten ID, People ID, Breakfast ID, Lunch ID and Dinner ID
Breafast, Lunch and Dinner all have the same structure:
Breakfast/Lunch/Dinner ID, Date, yes/no (recorded as 0 for no and 1 for yes)
People has a one to many relationship with Eaten
Eaten has a one to many relationship with breakfast, lunch and dinner tables.
I need a query that will return the names of all the people that have not eaten anything for 3 or more days and the exact number of days they have not eaten.
Can someone help. I'm lost.
Thanks in advance for your assistance.
Regards
Rens

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 02:37:51

I would have stored Breakfast, Lunch and Dinner in a single table (with a column to indicate which meal it was

), but you can fake that with a UNION:


SELECT Meal, ID, MAX(MaxDate) AS MaxDate
FROM
(
    SELECT 'Breakfast' AS Meal, ID, MAX([Date]) as MaxDate
    FROM Breakfast
    GROUP BY ID
    HAVING MAX([Date]) < DATEADD(Day, -3, GetDate())
    UNION ALL
    SELECT 'Lunch' AS Meal, ID, MAX([Date]) as MaxDate
    FROM Lunch
    GROUP BY ID
    HAVING MAX([Date]) < DATEADD(Day, -3, GetDate())
    UNION ALL
    SELECT 'Dinner' AS Meal, ID, MAX([Date]) as MaxDate
    FROM Dinner
    GROUP BY ID
    HAVING MAX([Date]) < DATEADD(Day, -3, GetDate())
) AS X

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources