question

jordanw44 avatar image
jordanw44 asked

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][1] [1]: /storage/temp/388-\capture.png
queryviewdatacolumn
\capture.png (14.4 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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?
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@jordanw44 As @Grant pointed out, it would be much better to have an interim table to marry the PCs and printers because with current design you will have to alter the table in order to add PrinterID3 (maybe PrinterID4 etc) to it and then join PC with Printer table once for each such column. This would be somnewhat cumbersome to maintain, while alternative solution would allow consistent query to get the data you need without the need to modify it.
0 Likes 0 ·
Is an interim table something that I can look up? I have never heard of such a thing. I do in fact have more PrinterID's coming.
0 Likes 0 ·
An interim table is also called a many-to-many join table. The idea is you have a table for printers and a table for PCs. Then, you have a table between these PCPrinters or PrintersPCs or whatever you want to call it. It then has a primary key that consists of the PK of the printers table and the PC table. You may also want to through in a relationship type in a lookup table (another related table) to identify the specific relationships. Don't make that part of the PK though. Now you can get a listing of multiple PCs for a given printer, or multiple printers for a given PC. Here's a more in-depth explanation: http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php
0 Likes 0 ·
@jordan44 It is actually quite simple. Remove the columns referencing the printers from the PC table. Add a table which has 2 columns, namely: Asset and PrinterID. This table will maintain many-to-many relationship between PC and Printer tables. For example, from the snapshot in your question, there will be 2 records in this table for Asset = 156234, 2 records for 158742 etc: Asset PrinterID 156234 1001 156234 1002 158742 1001 158742 1003 etc This way, your join is always going to be between PC, the interim and Printer tables. Former is married to the interim by Asset while the latter - by PrinterID. If you have, say 3 records for a specific Asset then there will be 3 records in the final query for this asset and if you need to make the printer paths appear as columns (rather than records) then you can crosstab your results with pivot.
0 Likes 0 ·
Thank you for the help @Oleg and @Grant! It was very helpful. I believe that I have enough to get this done, at least enough to go back to school for a few hours and learn a few new SQL tricks.
0 Likes 0 ·
Oleg avatar image
Oleg answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

16 seconds. I tried. Nicely done.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.