|
|
Posted by John Bell on 10/29/34 11:29
Hi
You could use a number table http://www.aspfaq.com/show.asp?id=2516 such as
CREATE TABLE tblLogin (
MemberNo int,
RegNo int,
Country char(10) )
CREATE TABLE tblTrades (
MemberNo int,
RegNo int,
Country char(10))
INSERT INTO tblLogin ( MemberNo, RegNo, Country )
SELECT 1, 1, 'Wales'
UNION ALL SELECT 2, 1, 'england'
UNION ALL SELECT 3, 1, 'scotland'
UNION ALL SELECT 4, 2, 'wales'
UNION ALL SELECT 5, 2, 'england'
UNION ALL SELECT 6, 2, 'scotland'
UNION ALL SELECT 7, 3, 'Wales'
UNION ALL SELECT 8, 3, 'england'
UNION ALL SELECT 9, 3, 'scotland'
UNION ALL SELECT 10, 4, 'wales'
UNION ALL SELECT 11, 4, 'england'
UNION ALL SELECT 12, 4, 'scotland'
INSERT INTO tblTrades ( MemberNo , RegNo , Country )
SELECT 0, 1, 'Wales'
UNION ALL SELECT 0, 1, 'england'
UNION ALL SELECT 0, 1, 'scotland'
UNION ALL SELECT 0, 2, 'wales'
UNION ALL SELECT 0, 3, 'scotland'
UNION ALL SELECT 0, 4, 'wales'
UNION ALL SELECT 0, 4, 'england'
UNION ALL SELECT 0, 4, 'scotland'
UNION ALL SELECT 0, 2, 'england'
UNION ALL SELECT 0, 2, 'scotland'
UNION ALL SELECT 0, 3, 'Wales'
UNION ALL SELECT 0, 3, 'england'
UPDATE t
SET MemberNo = c.MemberNo
FROM ( SELECT 1 AS MemberNo
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12 ) C
CROSS JOIN tblTrades t
JOIN tblLogin l on l.RegNo = t.RegNo AND l.Country = t.Country AND
c.MemberNo = l.MemberNo
SELECT * FROM tblTrades order by memberno
John
"joshnosh" <me@u.com> wrote in message
news:28a2l1lrvh5pn8cul6nlvjc3acm3ivt9jl@4ax.com...
>I have two tables tblLogin and tblTrades
>
> I have 3 fields in each table, for thr purpose of this question
>
> tblLogin Fields are
> MemberNo
> RegNo
> Country
>
> tblTrades fields are
> MemberNo
> RegNo
> Country
>
> There are other fields in both table but the fields shown are fields i
> need to work on
>
> the Tables look like this
>
> tblLogin
> ----------------------------
> MemberNo | RegNo | Country |
> ----------------------------
> 1 1 Wales
> 2 1 england
> 3 1 scotland
> 4 2 wales
> 5 2 england
> 6 2 scotland
> 7 3 Wales
> 8 3 england
> 9 3 scotland
> 10 4 wales
> 11 4 england
> 12 4 scotland
>
> tblTrades
> ----------------------------
> MemberNo | RegNo | Country |
> ----------------------------
> 0 1 Wales
> 0 1 england
> 0 1 scotland
> 0 2 wales
> 0 2 england
> 0 2 scotland
> 0 3 Wales
> 0 3 england
> 0 3 scotland
> 0 4 wales
> 0 4 england
> 0 4 scotland
>
>
> I need the field MemberNo in tblTrades to match the MemberNo in
> tblLogin
> Any help appriciated as cant to get any SQL code i use to work,
> because it must be wrong.
> and its a lot of quesswork.
> I am using phpMyAdmin on a MYSql database
>
Navigation:
[Reply to this message]
|