getting records from multiple tables....

Question

getting records from multiple tables....

Venkateshwar Reddy

SSC Rookie

Points: 25
More actions
October 6, 2005 at 3:45 am

#90205

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 !!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

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

Venkateshwar Reddy SSC Rookie Points: 25 More actions · Answer 2

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 !!!

Glyndwr SSC Enthusiast Points: 174 More actions · Answer 3

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

Glyndwr SSC Enthusiast Points: 174 More actions · Answer 4

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

Glyndwr SSC Enthusiast Points: 174 More actions · Answer 5

oops

insert into forms

select 1 , 'Login' union

select 2 , 'Main Form'

thats what happens when you rush

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

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