SQL intersect queries - 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

SQL intersect queries

Author

Topic

qwerty1234
Starting Member

13 Posts

Posted - 2011-12-02 : 18:45:23

im trying to create a query that shows all the customers that have rented less than 10 dvd within the last 6 months. So far i have got
select member_id from rental_table where add_months(data_out,6) >= sysdate
intersect
select member_id from rental_table group by member_id having count(rental_id) < 10;

Is there any way to get around this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-02 : 18:51:23

[code]
select member_id
from rental_table
where data_out >= dateadd(month, datediff(month, 0, getdate()) - 6, 0)
group by member_id
having count(rental_id) < 10;
[/code]

KH
[spoiler]Time is always against us[/spoiler]

qwerty1234
Starting Member

13 Posts

Posted - 2011-12-02 : 19:03:57

Thank You! we changed it to

select member_id
from rental_table
where sysdate <= add_months(date_out, 6)
group by member_id
having count(rental_id) < 10;

It was having a problem with the method you used for the date.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-02 : 19:56:03

quote:
It was having a problem with the method you used for the date.

what is the problem ?

KH
[spoiler]Time is always against us[/spoiler]

qwerty1234
Starting Member

13 Posts

Posted - 2011-12-02 : 19:58:05

no that worked prefectly once we made the changes that we showed above.

However could you please help me again I need to join together rental table and member table.

select r.member_id, m.first_name, m.surname
from rental_table r INNER JOIN member_table m
ON r.member_id = m.member_id
where sysdate <= add_months(date_out, 6)
group by r.member_id
having count(rental_id) < 10;

however this come up with an error on the group by expresion, im i doing this wrong?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-02 : 20:01:22

are you using MS SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

qwerty1234
Starting Member

13 Posts

Posted - 2011-12-02 : 20:02:44

no we are using oracle SQL server

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-02 : 20:03:52

SQLTeam.com is for MS SQL Server. Not many here is familiar with Oracle

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-02 : 20:04:58

all column not in aggregate should appear in the GROUP BY


select r.member_id, m.first_name, m.surname
from rental_table r INNER JOIN member_table m
ON r.member_id = m.member_id
where sysdate <= add_months(date_out, 6)
group by r.member_id, m.first_name, m.surname
having count(rental_id) < 10;

KH
[spoiler]Time is always against us[/spoiler]

qwerty1234
Starting Member

13 Posts

Posted - 2011-12-02 : 20:08:26

Thank you so much, that worked perfectly :D thank you for all your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources