Query / base case

[darrenhubbard]

Hi All,

I have the following SQL query:

SELECT aux_total.[Last Occurence], Applications.Application, aux_total.[Server IP], aux_total.[Server Port], aux_total.Protocol, aux_total.[Client IP], aux_total.[Server Total], aux_total.[Client Total], aux_total.[Total Number]
FROM aux_total LEFT JOIN Applications ON ((aux_total.Protocol=Applications.Protocol) And (aux_total.[Server Port]=Applications.[Server Port]) And (aux_total.[Server IP]=Applications.[Server IP])) Or ((aux_total.Protocol=Applications.Protocol) And (aux_total.[Server Port]=Applications.[Server Port]))
ORDER BY [Application];

The idea is I have a table with a server port, protocol and server IP address and the left join "maps" that to an application. However, in certain circumstances, the server ip address is left blank (the "OR" part of the left join) and therefore ANY server IP address with right port and protocol matches to that application. So, the applications table might look like this:

IP Prot Port Application
10.0.0.1 TCP 1100 App1
10.0.0.4 UDP 7 App2
TCP 80 HTTP

And so on. The problem is, if I add an entry like this:
100.100.10.1 TCP 80 WebApp3
then the query doesn't work (ie I want it to match the above to "WebApp3" and any other TCP 80 to HTTP). To be honest, I wasn't expecting the query to work in this situation but I can't figure anyway to fix it!

Thanks for all your help!

TIA,

Darren

 

[DanChard]

Hello.

To be quite honest, that query looks pretty weird because you're joining on more than one column which isn't common practise. If I were in your position I'd choose one column to join on (ie, "aux_total.Protocol=Applications.Protocol&quot and then put any other conditions as where clauses. This would look something like the following:

SELECT aux_total.[Last Occurence], Applications.Application, aux_total.[Server IP], aux_total.[Server Port], aux_total.Protocol, aux_total.[Client IP], aux_total.[Server Total], aux_total.[Client Total], aux_total.[Total Number]
FROM aux_total LEFT JOIN Applications ON aux_total.Protocol=Applications.Protocol
where aux_total.[Server Port]=Applications.[Server Port]
ORDER BY [Application];

NOTE - the previous join criteria ("aux_total.[Server IP]=Applications.[Server IP]&quot has been completely removed since you weren't bothered whether it was included or not by the looks of your query.

Give it a go and see what happens. Reply again if it doesn't!

 

[darrenhubbard]

I think I muddled it up a bit with my first post. Basically, if you consider the applications table as I defined it above (with four entries for http, app1, app2 and webapp3) and the aux_total contains the following (relevant fields only shown here):

10.0.0.1 TCP 1100
10.0.0.4 UDP 7
1.1.1.1 TCP 80
100.100.10.1 TCP 80
20.20.20.20 UDP 76

So, the result of my query should be the first row is "app1", the second row is "app2", the third row is "http", the fourth row is "webapp3" and the fifth row should be included in the result of the query but with nothing in the "application" field.

The query you posted seems to lose the records of the fifth row type (ie no match in the Applications table) and loses the match to server ip when there is one (ie considering the applications table: there could be another entry that is 50.50.50.50 UDP 7 NewApp -- the ip address being the only thing that differentiates App2 and NewApp).

Every time I look at it I keep on thinking there must be a simple solution but everything I try fails in someway :-(

Cheers for your help!

Darren