By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

How to make correct join

P: n/a
Hi
I have to tables one called GROUPS and one called ACCOUNT

In table Group I have the follwing fields
Groupid, AccountFrom, AccountTo

In table Account I have
AccountNo, Name etc.

Records in Groups:
P1, 1001, 1002
P1, 1005, 1007
P1, 1010, 1010
P1, 1007, 1012

Now I want to have the corresponding AccountNo from ACCOUNT (from range
AccountFrom..AccountTo), that is the following result:
1001
1002
1005
1006
1007
1008
1009
1010
1011
1012

If f.x. 1008 doesn't exist in ACCOUNT this should not be listed. The
result will be used in another view.
My problem is that I also get every other record from table ACCOUNT.

Do anyone out there have a solution on my problem ?
BR/Jan

Apr 4 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Post your current SELECT statement.
Usually , you would use an INNER JOIN for your requiremnts , i.e An inner
join returns all rows that result in a match .

Are you currently using a LEFT JOIN?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<ja**********@hotmail.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...
Hi
I have to tables one called GROUPS and one called ACCOUNT

In table Group I have the follwing fields
Groupid, AccountFrom, AccountTo

In table Account I have
AccountNo, Name etc.

Records in Groups:
P1, 1001, 1002
P1, 1005, 1007
P1, 1010, 1010
P1, 1007, 1012

Now I want to have the corresponding AccountNo from ACCOUNT (from range
AccountFrom..AccountTo), that is the following result:
1001
1002
1005
1006
1007
1008
1009
1010
1011
1012

If f.x. 1008 doesn't exist in ACCOUNT this should not be listed. The
result will be used in another view.
My problem is that I also get every other record from table ACCOUNT.

Do anyone out there have a solution on my problem ?
BR/Jan

Apr 4 '06 #2

P: n/a
Hi there

As I recall the statement it's something like this

Select distinct dbo.Account.AccountNo as AccountNo
From dbo.Account inner join dbo.Groups

On dbo.Groups.AccountFrom <= dbo.Account.AccountNo AND
dbo.Groups.AccountTo >= dbo.Account.AccountNo

Hope you have a solution for me. I have tried different ways but don't
seme to get it right.
BR /Jan

Apr 4 '06 #3

P: n/a
(ja**********@hotmail.com) writes:
As I recall the statement it's something like this

Select distinct dbo.Account.AccountNo as AccountNo
From dbo.Account inner join dbo.Groups

On dbo.Groups.AccountFrom <= dbo.Account.AccountNo AND
dbo.Groups.AccountTo >= dbo.Account.AccountNo

Hope you have a solution for me. I have tried different ways but don't
seme to get it right.


This is certainly better, since it relieves you from the DISTINCT:

SELECT a.AccountNp
FROM dbo.Account a
WHERE EXISTS (SELECT *
FROM dbo.Groups g
WHERE a.AccountNo BETWEEN g.AccountFrom AND g.AccountTo)

But whether that really addresses your issue, I cannot tell, because
your posting was very clear.

If this query does not work out, I suggest that you post the following:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result from the sample.

This permits anyone who wants to answer to copy and paste into
a query tool and develop a tested query.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 4 '06 #4

P: n/a
Hi
Thanks very much. This worked out just as I wanted it to. Next time I
will have in mind what you suggested.
BR/Jan

Apr 5 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.