How to make correct join

[jannoergaard]

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

[Jack Vamvas]

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

[jannoergaard]

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

[Erland Sommarskog]

(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

[jazpar]

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