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.

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

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

ASKER CERTIFIED SOLUTION

TextReport

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

harveygs

ASKER

That works ok thanks