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

Last Comment

harveygs

8/22/2022 - Mon

TextReport

3/15/2009

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

3/15/2009

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

3/15/2009

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

3/15/2009

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

3/15/2009

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

3/15/2009

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

3/15/2009

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