Link to home
Create AccountLog in
Avatar of rleyba828
rleyba828Flag for Australia

asked on

SQL query for indirect relationship

Hi Team,

   I have an  SQL relationship question where I need to relate two indirectly linked tables together.
I have a mySQL database which is composed of 3 main tables and they are linked many-to-many. See attached diagram.

The first one is an IT APPLICATIONS table where an IT Application can reside on one on more SERVERS.  These servers, in turn have multiple network cards each with its own IP addresses.  In our actual setup, an application should only be linked to ONE IP address per server.    In my example scenario, only IP addresses 10.1.1.1 and 10.1.1.4 should be linked to the APP-A application.   I am missing a relationship parameter here somewhere, but I am not sure how to setup the tables to link the APPS table and the IP_ADDDRESES table.

Initially, I created an APPS2IP table to do the many-to-many relationship, but it could lead to a situation where an IP address (say 10.1.1.7) below might link to APP-A but would result in a constraint violation, because 10.1.1.7 is connected to SERVER-C, which is NOT linked to APP-A.

How could I prevent this scenario from happening?

Thanks very much.

relationship.JPG
Avatar of Sheils
Sheils
Flag of Australia image

If you want a sql to link the two tables use the following syntax:

'SELECT TableA.Column1, TableB.Column1, TableC.Column1,TableD.Column1

FROM      ((TableA
INNER JOIN  TableB ON TableA.AID = TableB.AID)
INNER JOIN      TableC ON TableB.BID = TableC.BID)
INNER JOIN      TableD ON TableC.BID = TableD.BID

This is for a 4 table query. You have a five table query. Just add the next inner join line one extra bracket before table A and a braket at the end of the second last line of you sql.

Avatar of rleyba828

ASKER

Hi,

   Thanks for your reply above,  actually it is not a 5 table query...it is only a 3 table one.  The 3 tables involved are APPS_TABLE, SERVER_TABLE and IP_ADDRESS tables.   The two other tables, APPS2SERVER and SERVER2IP are merely there as I need a way to do a many-to-many relationship between APPS and SERVER (this is taken cared of by the APP2SERVER table)   and also between the SERVER and the IP_ADDRESS tables (this is taken cared of by the SERVER2IP table).

   One of the first things I need to do is to query all IP addresses linked to APP-A.   The answer, as show above is 10.1.1.1 and 10.1.1.4.  My problem is I don't know how to formulate the SQL query that would produce this result.

Thanks again.
ASKER CERTIFIED SOLUTION
Avatar of Sheils
Sheils
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Amazing!  After I substituted the field names with my actual field names, the whole query worked flawlessly.  It gave me the actual results I wanted to see.

Thanks very much.