query help

  • Thread starter Thread starter dede
  • Start date Start date
D

dede

i want to join 2 table in sql query
table example is like this :

table "members"

name phone

deny 07334
ben 76865
all 2435
ren 736456
bony 54546
..... .....

table "number"

id group
1 A
2 B

i want to make query become

name phone group

deny 07334 A
ben 76865 B
all 2435 A
ren 736456 B
bony 54546 A
..... ..... ...

anyone can help me..?
 
What is the relationship between the two tables? How do you know that
bony is group A, not group B?
 
Simple question but difficult problem. The easy way would be to use a
cursor and a temporary table inside a stored procedure, the harder way would
be to use a subquery in order to eliminate the pair and impair values.
However, this newsgroup is about ADP and SQL-Server and I'm not sure that
you are really working against a SQL-Server and not again a JET/Access
database.

The solution that I could give to you using a stored procedure won't be of
any use at all if you are not working with a SQL-Server installation. If
you are working with Access and JET, then please repost your question in the
newsgroup m.p.access.queries (or m.p.access.modulesdaovba.ado if you want a
solution using a VBA procedure).
 
hi.. thaxs for the advice.
i'm use adp access project, i try to make the member group is balance
for the example if i have 50 member,
query automaticaly make group A is 25 member and B is 25 member, if
new member added, become group A, next member become group B,next
member become group A.......
 
The easy way would be to use a cursor: create a temporary table, open a
cursor on your table Members and then insert each line in table Members into
the temporary table, alternating the value of ID. Of course, you could
also add this ID field directly to the table Members and update it using the
cursor. On the Internet, you will find numerous examples of using a cursor
in T-SQL. Check also the BOL (OnLine Help for SQL-Server) as well as the
m.p.sqlserver.programming newsgroup.

The second way would be to use a subquery to compute the position of each
member and use the Modulo 2 of this value to divide your members; for
example:

Set NoCount ON
create table #Members (IdMember int Identity (1,1) primary key, firstname
varchar(50), phone varchar (10))
create table #Number (IdNumber int primary key, groupe char (1))

insert into #Members (firstname, phone) values ('deny', '07334')
insert into #Members (firstname, phone) values ('ben', '76865')
insert into #Members (firstname, phone) values ('all', '2435')
insert into #Members (firstname, phone) values ('ren', '736456')
insert into #Members (firstname, phone) values ('bony', '54546')

insert into #Number (IdNumber, groupe) values ('1', 'A')
insert into #Number (IdNumber, groupe) values ('2', 'B')

-- These three queries show first the ranking of each member using a
subquery (starting from 0),
-- then the result of Modulo 2
-- and finally the Join to give you the searched result:

select *, (Select Count (*) from #Members M2 where M2.IdMember < M.IdMember)
from #Members as M

select *, (Select Count (*) from #Members M2 where M2.IdMember < M.IdMember)
% 2 from #Members as M

select * from #Members M Inner Join #Number N on ((1 + ((Select Count (*)
from #Members M2 where M2.IdMember < M.IdMember) % 2)) = N.IdNumber)
Order by M.IdMember

drop table #Members
drop table #Number
 
Back
Top