Avatar of harveygs
harveygs
 asked on

SqL lookup table basics

I have 400 computers that can have between 1 to 4 printers allocated. From a list of 20 printers. And also another column showing which one of those 4 is the default printer. This is queried in a VBS logon script.
I just need the table layout and SQL query not any vbs sript.
Being new to sql I dont know the best way to make the tables. I assume I would need 2 with a join.
first table 'Printer_List'
pr_id  name
1     epson99
2     hpthingy22
3     konicaxyz

2nd table PC_List
pc_name    pr1    pr2   pr3  pr4    default_printer                  where default would be = 1 to 4
room1       1          0     0     0        1
class77     3         2     0       0      2
pc99          4        0     0     0         4

a zero means no printer allocated
so for instance the pc called class77  has two printers allocated, konicaxyz and hpthingy22
and the hpthingy22 is to be the default printer.
eventually 6 variables
pc_name="class77"
pr1="konicaxyz"
pr2="hpthingy22"
pr3=0 or null
pr4=0 or null
default_printer="hpthingy22"
I hope that makes sense .
Thanks.


DatabasesMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
harveygs

8/22/2022 - Mon
TextReport

You should not include Pr1, Pr2 etc as this will limit the number of printers to 4 which is OK until you get a room that needs another printer for some reason. When you normalize your tables structure one of the tasks is to remove any repeating like this, this is achieved by using a 3rd table that join the PC's to the Printers and this is known as a Many to Many relationship. Also PC_List should have a ID column the same way as the Printer_List does.

tblPCPrinters
PC_ID
PR_ID
DefaultPrinter

The table would have a Primary Key of PC_ID and PR_ID and DefaultPrinter would be a BIT field (Yes/No)

Cheers, Andrew
harveygs

ASKER
This makes it worse (for me to understand).
Ok to needing the ID column and see point if need to expamd to 5 printers etc.
so table pc_list  would be...
pc_id pc_name
1        pc99

table printer_list    would be
pr_id     name
1          epson

3rd table  ...
Nope havnt got a clue!
TextReport

It may make it worse now but this is a standard technique for relating 2 tables to be a many to many relationship. In your case many printers connected to many pc's.
Cheers, Andrew
tblPCPrinters
PC_ID  PR_ID  DefaultPrinter
1      1      1
1      2      0
1      3      0
2      3      0
2      1      1

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
harveygs

ASKER
Thanks Andy.
I am a complete beginner (i've never made a join before)

in the table tblPCPrinters ...
the pc of PC_id = 1 which is say room1 which gets 3 printers of PR_id 1 2 and 3 the default printer being PR_id =1.

what does the sql query look like and how do i make it so i get this....
(so i can get the variables or array out)

while not eof
wscript.addprinter  1st printer..... 2nd printer.....3rd printer
wend
wscript.setdefaultprinter =....  defaultprinter

dont worry about the script syntax.

thanks
TextReport

The query will be similar to below, how you populate your front end will depend on what the front end is.
Cheers, Andrew
SELECT PC.PC_Name
     , PR.PR_Name
     , PCP.DefaultPrinter
FROM dbo.PCPrinters PCP
    INNER JOIN dbo.PC_List PC
    ON PCP.PC_ID = PC.PC_ID
    INNER JOIN dbo.Printer_List PR
    ON PCP.PR_ID = PR.PR_ID

Open in new window

harveygs

ASKER
so if the pcname is pc99 where i want to get the list of printers,
would this be :-
SELECT PC.PC_Name
     , PR.PR_Name
     , PCP.DefaultPrinter
FROM dbo.PCPrinters PCP
    INNER JOIN dbo.PC_List PC
    ON PCP.PC_ID = PC.PC_ID
    INNER JOIN dbo.Printer_List PR
    ON PCP.PR_ID = PR.PR_ID
where pc_list.name="pc99"

thanks
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
TextReport

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
harveygs

ASKER
That works ok thanks