MySQL Query Help Needed * Please Help

[JRMS]

Please help. I have two tables.

Table 1

IP_Addr Tool#1 Tool#2
xx.xx.11 P

Table 2

IP_Addr Tool#1 Tool#2
xx.xx.11 N

I would like to query the two tables, and if:

1. The IP addresses match, join the Tool#1 and Tool#2 into one table such as below:


IP_Addr Tool#1 Tool#2
xx.xx.11 N P

Thank you in advance for your help.

Jerrell

 

[ethorn10]

Will it always be EITHER Tool#1 or Tool#2? Meaning, if Tool#2 has a value, then Tool#1 in that same table won't have a value? Or could both tables have both Tool#1 and Tool#2 values? So if your data was:

Table 1

IP_Addr Tool#1 Tool#2
xx.xx.11 XY P

Table 2

IP_Addr Tool#1 Tool#2
xx.xx.11 N YZ

Would you then want to see:

IP_Addr Tool#1 Tool#2
xx.xx.11 NXY PYZ

?

 

[JRMS]

Thanks for the response. Table 1 nor 2 will ever have both tools present. Table 1 has only Tool 1 and Table 2 only has Tool 2. So, I should have illustrated by a table with 2 columns: IP and Tool.

If the IP's are the same in both table, merge the row with IP + Tool 1 and Tool2.

Hopefully this will clarify.

Thanks.
Jerrell

 

[ethorn10]

select a.IP_Addr
     , concat_ws(' ', a.Tool, b.Tool) as Tool
from table1 as a inner join 
     table2 as b on a.IP_Addr = b.IP_Addr

Or if you want 3 columns

select a.IP_Addr
     , a.Tool as Tool1
     , b.Tool as Tool2
from table1 as a inner join 
     table2 as b on a.IP_Addr = b.IP_Addr

 

[JRMS]

Thanks for the response. What does the 'a' in front of 'a.IP_Addr' represent.

 

[ethorn10]

'a' in that case is an alias for table1. You'll notice in the from clause I said 'table1 as a' and 'table2 as b'. Feel free to make it whatever you like, or remove the alias altogether. Did either of those work for you? Which one were you preferring?

 

[JRMS]

ethorn10,

This statement works perfectly. What if I wanted to include in the list the IP that was scanned by both tools and list in the same list the IPs that were not.

Is this possible:

select a.IP_Addr
, a.Tool as Tool1
, b.Tool as Tool2
from table1 as a inner join
table2 as b on a.IP_Addr = b.IP_Addr UNION
select a.IP_Addr
, a.Tool as Tool1
, b.Tool as Tool2
from table1 as a inner join
table2 as b on a.IP_Addr != b.IP_Addr

Thanks!

 

[ethorn10]

The only way I can think to accomplish this is with a FULL OUTER JOIN. Somebody can probably make this better/faster/more_efficient:

select 
  case when a.ip_addr = b.ip_addr then concat_ws('-', cast(a.ip_addr as char), 'BOTH') else '' end as BOTH,
  case when a.ip_addr is null then concat_ws('-', cast(b.ip_addr as char), 'ToolB Only') else '' end as ToolB,
  case when b.ip_addr is null then concat_ws('-', cast(a.ip_addr as char), 'ToolA Only') else '' end as ToolA
from table1 as a full outer join
   table2 as b on a.ip_addr = b.ip_addr

Again, somebody else can probably touch that up.