is this possible for sql

[rommeltm00]

good day sir/madam

attached is an excel file with sample table is the result can be possible in sql and how can i command that on sql. thanks and more power.

 

[GriffMG]

I don't mean to be unhelpful, but I think this question would be better posted in the SQL forums?

B-(

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[rommeltm00]

griff

i used this in vfp

thanks.

 

[Mike Lewis]

I don't mean to be unhelpful either, but do you think you could explain your problem. It's not very useful to expect us to download a worksheet and try to figure out what you are trying to do.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[rommeltm00]

hi mike

i want to obtain the result of the two table with the result on the column h, thanks.

 

[GriffMG]

I have actually downloaded the information - which is in a spreadsheet, which is not ideal from a security perspective.

I have even created a couple of tables from it and preloaded the data from the spread sheet... but I confess that I can't come up with a solution in my range of SQL skills:

It looks like it ought to be deceptively simple but it isn't to me!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[rommeltm00]

griff

we use this on a duty detail order of a security personnel where

name of guards then the firearms

some client have more guards than firearms, so the excel file i sent is some example. i use join but the name repeats itself until all firearms for that unit is get by sql, then next guard, get all firearms for that unit again, and so on.

 

[GriffMG]

Rommel

I can see what you are trying to do, and it is childs play for me in native VFP - but I don't have the SQL skills to do this... which is why I (and Mike) suggested you post it in a SQL forum.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[rommeltm00]

ok guys, thanks for the time. ill try to post this on sql forums, more power and God Bless.

 

[cbasoz]

If doing all that in SQL is not a must and since I guess we are not talking about an army with millions of guards this sounds to be an effective solution to me:

Create Cursor table2 (unit c(1), serial i)

Insert Into table1 Values ('a	','name1','license1')
Insert Into table1 Values ('a	','name2','license2')
Insert Into table1 Values ('a	','name3','license3')
Insert Into table1 Values ('b	','name4','license4')
Insert Into table1 Values ('b	','name5','license5')
Insert Into table1 Values ('c	','name6','license6')
Insert Into table1 Values ('c	','name7','license7')
Insert Into table1 Values ('c	','name8','license8')
Insert Into table1 Values ('c	','name9','license9')
Insert Into table1 Values ('c	','name10','license10')
Insert Into table1 Values ('c	','name11','license11')
Insert Into table1 Values ('c	','name12','license12')
Insert Into table1 Values ('d	','name13','license13')

Insert Into table2 Values ('a	',1)
Insert Into table2 Values ('a	',2)
Insert Into table2 Values ('b	',3)
Insert Into table2 Values ('c	',4)
Insert Into table2 Values ('c	',5)
Insert Into table2 Values ('c	',6)
Insert Into table2 Values ('c	',7)
Insert Into table2 Values ('d	',8)

Select *, Cast(Null As Int) As serial ;
  FROM table1 ;
  ORDER By unit ;
  INTO Cursor crsResult ;
  readwrite
Index On unit Tag unit

Select table2
Scan
  Scatter Name o2
  Select crsResult
  Seek o2.unit
  Locate For Isnull(serial) While unit = o2.unit
  Replace serial With o2.serial
Endscan

Select crsResult
Set NullDisplay To ''
Browse

Cetin Basoz
MS Foxpro MVP, MCP

 

[rommeltm00]

Thanks Cetin, i'll try this approach, more power and God bless