What does `Type: IP (0x0800)` in the Ethernet II part of a ping packet mean?

Question

I have a two tables called 1.ReportTableDetails and 2.SecurityDetails

ReportTableDetails

ReportName   | ColumnNames | FilterNames
--------------------------------------
Total Sales  | data1,data2 | data1,data2
Branch Sales | data1,data2 | data1,data2

SecurityDetails

SecurityLevel | ReportName   | RColumn     | RFilter
--------------------------------------------------------
     1        | Total Sales  | data1,data2 | data1,data2
     4        | Total Sales  | data1,data2 | data1,data2

I will search the records by SecurityLevel from SecurityDetails. The condition is, if the ReportName is not found in SecurityDetails, it should pick the data from ReportTableDetails

Expecting output

SecurityLevel | ReportName   | RColumnNames| RFilterNames | ColumnNames | FilterNames
---------------------------------------------------------------------------------------
     1        | Total Sales  | data1,data2 | data1,data2  |             |
     1        | Branch Sales |             |              | data1,data2 | data1,data2

What I tried is inner join with SecurityDetails table. it retrieves when the report names are equal. But i want to retrieve which is not in SecurityDetails table also

Solution

The "Type" field in Ethernet II frames tells the OS what kind of data the frame carries – 0x0800 means that the frame has an IPv4 packet; there's a list of different EtherTypes.

This field is needed because there are many other protocols that go directly over Ethernet: for example, IPv6, IPX, ARP, AppleTalk...

This is explained in detail in the Wikipedia article about Ethernet frames, in particular the section about Ethernet frame types – Ethernet II ("DIX") frames have a "Type" field, but some networks (in particular the now-obsolete IPX) used to use IEEE 802.3 framing, which has "Packet size" instead, and uses separate 'LLC' (802.2) or even 'LLC'+'SNAP' headers between Ethernet header and protocol data.

Comments

[Dan Bracuk]

A combination of outer join and isNull() should work.

[mohamed faisal]

@DanBracuk can you show me some sample code?

[Yashveer Singh]

@mohamedfaisal I answered but I have some question with our output I mentione din my answer that as well

[Arjan]

@grawity, a bit late, but nice how you edited your answer in here! :-) (Will delete this comment soon.)

[mohamed faisal]

Actually when i execute your query, it's retrieving only one row, which is equal the report name from both table

[SqlZim]

@mohamedfaisal Then you probably changed the query to use where sd.SecurityLevel = @SecurityLevel instead of leaving it as a join condition. Check the rextester link, it proves the query works for the provided test data. rextester.com/QGZHLO98381

[mohamed faisal]

sorry. its working fine. But i don't want the columname and filternames values of first row record

[SqlZim]

@mohamedfaisal Updated to return empty strings when SecurityDetails has column and filter values.