sql from multiple tables

SELECT computers.employee, printers_scanners.brandname, IpAddress.ipaddress
FROM (computers INNER JOIN IpAddress ON computers.[IP Address] = IpAddress.ipaddress) INNER JOIN printers_scanners ON IpAddress.ipaddress = printers_scanners.ip_address;

I have one table with every possible ip address that is allowed in my system
one table with all pc's
one table with all printers
one table with all rack mounted equipment.
the common field is the ip address
I need a list of every piece of equipment in my system.
The query above was an experiment to try to bring out all ip in the computer table and all ip in the printer table; but it come up blank.

thanks

gair
javagairAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
First make sure that each IP in the child tables (Printers, PC's, ...ect) has a corresponding IP in the parent (IP) table
0
mbizupCommented:
Try using LEFT JOINS:


SELECT computers.employee, printers_scanners.brandname, IpAddress.ipaddress
FROM (computers LEFT JOIN IpAddress ON computers.[IP Address] = IpAddress.ipaddress) LEFT JOIN printers_scanners ON IpAddress.ipaddress = printers_scanners.ip_address;

Open in new window

0
mbizupCommented:
Just explaining:

A LEFT JOIN will select all records from the table on the left hand side of the join, and matching records from the other side of the join; inner joins only select records that have matches in both tables.

For example, in your case INNER JOINS will display only those computers that have matches in the IP Addresses and ptinters tables.

LEFT JOINS will display ALL Computers, but show nulls for fields where matching records were not found in the other tables.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Dale FyeCommented:
My guess is that none of those are going to work, because the IP addresses between computers and scanners are not common, therefore neither inner or Left joins are going to achieve the desired results.

If all you need is a list of all of the equipment and the associated IP, then write a UNION query first contains Computers, 2nd contains printers.

For some reason my network is balking at accepting the actual "Union" syntax.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
Unless you start with the IP table, and then LEFT JOIN the other two tables to that, but even then, you will probably never get IP, Computer, and Printer to all show up on the same line.
0
PortletPaulfreelancerCommented:
if "every possible IP" is in a table (epi), then

select
*
from (((epi
left join computers on epi.ip = computers.ip)
left join rackmounted on epi.ip = rackmounted.ip)
left join printers_scanners on epi.i = printers_scanners .ip)


if epi doesn't actually exist, then one option is along these lines :

select
*
from ((((
   select IP from computers
   union
   select IP from rackmounted
   union
   select IP from printers_scanners) epi
left join computers on epi.ip = computers.ip)
left join rackmounted on epi.ip = rackmounted.ip)
left join printers_scanners on epi.i = printers_scanners .ip)

you could just directly union computers, rackmounted , printers_scanners but you have to deal with the fields of each so they align appropriately.


?? what about network gear such as routers etc.

nb: my Access SQL syntax knowledge is old/limited and I don't use it nowadays
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.