How to show another tables data in a specific column based on a query
I have two tables: one contains PC ID's and the other printer ID's. Each PC has several different classes to print that all reference a specific printer ID. I want to show the path for each class that is represented by each printer ID. I have attached a screen shot of kind of what I am looking for. I thought that I could accomplish this by a view but I am not having any success. It is giving problems because I am trying to reference multiple columns in the same table to one single column in another table.
![alt text] : /storage/temp/388-\capture.png
Can't you just JOIN to the table twice? SELECT pct.Asset, pt1.Path AS PrinterID1, pt2.Path AS PrinterID2 FROM PCTable AS pct JOIN PrinterTable AS pt1 ON pct.Printerid1 = pt1.PrinterID JOIN PrinterTable AS pt2 ON pct.PrinterID2 = pt2.PrinterID BTW, points out that a better design would be to list the PC to Printer associations through an interim table so that you can do many to many joins. What happens when a PC has another printer?
Because your PC Table has 2 columns storing the printer IDs, you just need to join this table with your printer table twice, once for each ID. Here is the script: select pc.Asset, printer.[Path] PrinterID1, printer2.[Path] PrinterID2 from PC_Table pc inner join Printer_Table printer on pc.PrinterID1 = printer.PrinterID inner join Printer_Table printer2 on pc.PrinterID2 = printer2.PrinterID; go The above produces the data you need: Asset PrintID1 PrintID2 ----------- ---------- ---------- 78956 pathXYZ path465 73532 pathABC path234 94324 path234 path465 Hope this helps, Oleg