Need a join, I think - 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

General SQL Server Forums

New to SQL Server Programming

Need a join, I think

Author

Topic

hamilcar1
Starting Member

3 Posts

Posted - 2011-10-06 : 09:46:12

Have two tables

people items
________ _______
id_number id_number
name receipt_number
address date
amount

Need a query for all items purchased for "John Doe" in the second quarter of 2010 and to list the date, amount, and receipt_number which is sorted by order date.

My inital try came up with this:
Select date, amount, and receipt_number
From Items
Where id_number.people = "John Doe"
and date BETWEEN 'Mar-31-2010' AND 'Jul-01-2011'
ORDER BY date;

any help is greatly appreciated.
Thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-06 : 09:48:04

formatted:

Have two tables

people       items
________     _______
id_number    id_number
name         receipt_number
address      date
             amount

Need a query for all items purchased for "John Doe" in the second quarter of 2010 and to list the date, amount, and receipt_number which is sorted by order date.

My inital try came up with this:
Select date, amount, and receipt_number
From Items
Where id_number.people = "John Doe"
and date BETWEEN 'Mar-31-2010' AND 'Jul-01-2011'
ORDER BY date;

any help is greatly appreciated.
Thank you

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-06 : 09:49:24

where is the connection between people and items?

No, you're never too old to Yak'n'Roll if you're too young to die.

hamilcar1
Starting Member

3 Posts

Posted - 2011-10-06 : 10:04:52

id_number is the connection. If I'm understanding what you are asking.

amnak
Starting Member

3 Posts

Posted - 2011-10-06 : 12:04:15

Select item.date, people.amount, and item.receipt_number
From Items
join People on item.id_number=people.id_number
Where people.name = "John Doe"
and item.date BETWEEN 'Mar-31-2010' AND 'Jul-01-2011'
ORDER BY date;

amnak
Starting Member

3 Posts

Posted - 2011-10-06 : 12:07:45

Try this one , i hope it will work . ignor my first replay i items table name is incorrect.

Select items.date, people.amount, and items.receipt_number
From Items
join People on items.id_number=people.id_number
Where people.name = "John Doe"
and items.date BETWEEN 'Mar-31-2010' AND 'Jul-01-2011'
ORDER BY date;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 12:17:15

the last suggestion depends on server language and date frmat setting. better to use below modified version


Select items.date, people.amount, and items.receipt_number
From Items
join People on items.id_number=people.id_number
Where people.name = 'John Doe'
and items.date >= DATEADD(qq,1,DATEADD(DATEADD(yy,110,0)) 
AND items.date < DATEADD(qq,2,DATEADD(DATEADD(yy,110,0))
ORDER BY date;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hamilcar1
Starting Member

3 Posts

Posted - 2011-10-06 : 12:44:56

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 12:47:06

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources