HP Procurve 2920 VLANs with DHCP cannot ping

Question

The question is "Write a query to show all the employees:

last_name, first_name, phone_number, department_name, city, country, and region. Sort by last_name and then by first_name.

The problem is fields are in 5 different tables of database

I tried but don't know how to wire it further

use hr;

SELECT first_name
     , last_name
     , PHONE_NUMBER
     , ( SELECT department_name
          FROM departments d 
         where e.department_id = d.department_id) department 
  FROM employees e 
 ORDER 
    BY last_name;

Solution

So it looks from your setup that you're using the HP 2920 as a router. In order to do this you will need to add static routes on the 2920 to allow for communication between the vlans. If you add the following to your config it should fix it. Put the route to the outside world as your default route- I put it as 1.1.1.1 for the sake of illustration, replace with whatever you need it to be (most likely the ip of your firewall, if you have one)

ip route 0.0.0.0 0.0.0.0 1.1.1.1
ip route 192.168.0.0 255.255.255.0 192.168.0.3
ip route 192.168.1.0 255.255.255.0 192.168.1.3
ip route 192.168.2.0 255.255.255.0 192.168.2.3

The first IP represents the network, the second is the next-hop address after the gateway. Traditionally when doing it this way, you would have the vlan interfaces on the switch set to the first IP and use that as the default gateway and the next hop address would b a .2. I've never seen the interface set as a .2, so I'm presuming next hop would be set as .3 but someone here may correct me. Eitherway, you need the routes- give it a shot.

EDIT: To further clarify, whenever you want traffic to go from one vlan (or subnet) to another it has to be routed. You can ping the different vlans from the switch because the traffic never has to leave that vlan. Its failing from 192.168.0.1 to 192.168.1.1 because they are in two different subnets and there is no route telling the traffic how to get to the other network.

Comments

[David Schwartz]

Can the machines ping the switch's other addresses? Can 192.168.1.1 ping 192.168.0.2? Can 192.168.0.1 ping 192.168.1.2?

[Koto]

Hmm.. weird things going on. Okey.... I was testing today only on the XP machine.. other pings.. connected 2nd laptop to switch. The results are, that XP machines can ping each other at VLAN101 and VLAN102. Winserver2008 cannot ping any of the XP machines and cannot be pinged by them. But no config changed. Gonna try out some other things as route 0.0.0.0/0 again etc.

[Koto]

Okey, so I got to it again, using wireshark and trying things out. I've found out that adding static routes to win server solved the problem. I was sure it is enough to have default gateway set to switch IP and all be running. So for each VLAN i had to add route manualy ROUTE ADD 192.198.x.0 MASK 255.255.255.0 192.168.x.2 Clients are getting IPs from DHCP now on other VLANs, but I'm still not sure why you have to manualy do the routes when everything pointing to switch and switch has ip routing enabled ?

[David Schwartz]

That doesn't make sense. If the server already has an IP address inside 192.168.x.0, it shouldn't need a route. And if it doesn't have an IP address inside 192.168.x.0, it shouldn't know how to reach 192.168.x.2 without a route that would also work for the rest of 192.168.x.0/24.

[Paul T.]

Ok, so our questions is: ... where is the query that shows your attempt to solve the problem? ... This is not a write-4-U coding site.

[Joe Shark]

HINT: you don’t need a subquery, you need to JOIN the tables eg start with employees table then JOIN jobs table based on job_id, then JOIN another table and so on until all the fields you need are available. Look up how to do JOINs - and the different types.

[Strawberry]

Why would you want a subquery here?