getting records from multiple tables....
-
Hi Everybody
Please help me how to write a query for the below situation.
I have table like
----------------------------------------------------
FID FNAME NoOfButtons NoOfLables
----------------------------------------------------
1 Login 2 4
2 Main Form 5 3
----------------------------------------------------
For the Login form I have 2 buttons and 4 labels and for Main Form 5 buttons and 3 labels.
I have "Buttons" and "Labels" tables to store the records.
The "Buttons" table is like
------------------------------------
FID BID Button Desc
------------------------------------
1 1 Logon
1 2 OK
2 1 Search
2 2 OK
2 3 Find
2 4 GetResults
2 5 Exit
------------------------------------
The "Labels" table is like
------------------------------------
FID LID Label Desc
------------------------------------
1 1 Login ID
1 2 Password
1 3 Error
1 4 Remember Me
2 1 Search Key
2 2 Name
2 3 Address
------------------------------------
I want the results like this
-----------------------------------------------
Form Name Button Desc Label Desc
-----------------------------------------------
Login Logon
OK
Login ID
Password
Error
Remember Me
Main Form Search
OK
Find
GetResults
Exit
Search Key
Name
Address
-----------------------------------------------
Thanks in Advance
Live Life Like Lion !!! -
I don't quite understand the question, but if the main thing is the layout of the results, then it looks like that is the kind of formatting best suited for the client / presentation app.
/Kenneth
-
Hi Kenneth,
Here I will explain what I need exactly....
In the first table form, I have two columns NoOfButtons, NoOfLables. For each column in the form table i have a seperate table- so, I have table Buttons for NoOfButton column in the form table..like for NoOfLables also.
These colums represents the number of rows in the respective tables. For ex: NoOfButtons colums has the value 2, that means there are two rows in the Buttons table against that form (for each record in the form table you have a form id - FID and form name - FNAME).
I need to display all the rows for that form -
For Login form I have to display 6 rows (2 buttons + 4 labels)
Like this I need to display all the rows against the form table.
I hope you understood this Kenneth..
Live Life Like Lion !!! -
Hi,
is this what you want ?
drop table forms
drop table labels
drop table buttons
go
create table forms ( fid int , formName varchar(20))
create table buttons ( fid int , bid int , buttonName varchar(20))
create table labels ( fid int , lid int , labelName varchar(20))
go
insert into forms
select 1 , 'Login' union
select 1 , 'Form Name'
go
insert into buttons
select 1, 1, 'Logon' union
select 1,2,'OK' union
select 2,1,'Search' union
select 2,2,'OK' union
select 2,3,'Find' union
select 2,4,'GetResults' union
select 2,5,'Exit'
go
insert into labels
select 1,1 , 'Login ID' union
select 1,2 , 'Password' union
select 1,3 , 'Error' union
select 1,4 , 'Remember Me' union
select 2,1 , 'Search Key' union
select 2,2 , 'Name' union
select 2,3 , 'Address'
go
select
f.FormName
, b.buttonName
, '' as LabelName
from forms f
inner join buttons b on b.fid = f.fid
union
select
f.FormName
, '' as buttonName
, l.LabelName
from forms f
inner join labels l on l.fid = f.fid
order by formname , buttonName ,labelname
Gareth
-
oops
should be
insert into forms
select 1 , 'Login' union
select 1 , 'Main Form'
and
select
f.FormName
, b.buttonName
, '' as LabelName
from forms f
inner join buttons b on b.fid = f.fid
union
select
f.FormName
, '' as buttonName
, l.LabelName
from forms f
inner join labels l on l.fid = f.fid
order by formname , labelname , buttonName
this gives the correct button/label order
gareth
-
oops
insert into forms
select 1 , 'Login' union
select 2 , 'Main Form'
thats what happens when you rush
-
Almost there I think...
This first came to mind:
select f.fname, b.bdesc, l.ldesc
from #forms f
join #buttons b
on f.fid = b.fid
join #labels l
on f.fid = l.fid
fname bdesc ldesc
-------------------- -------------------- --------------------
Login Logon Login ID
Login Logon Password
Login Logon Error
Login Logon Remember Me
Login OK Login ID
Login OK Password
Login OK Error
Login OK Remember Me
Main Form Logon Login ID
Main Form Logon Password
Main Form Logon Error
Main Form Logon Remember Me
Main Form OK Login ID
Main Form OK Password
Main Form OK Error
Main Form OK Remember Me
(16 row(s) affected)
but...
You have a problem here. There is nothing that says which label goes to which button, so you'll end up with all lables on each form being matched to all buttons on that form...
/Kenneth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply